Quantifier Functions (OLAP)

[Table of Contents](https://github.com/dell-oss/Doradus/wiki/OLAP Databases: Table-of-Contents) | Previous | Next
Doradus Query Language: Quantifier Functions

---

A quantifier clause tests a set of values that are related to a particular perspective object. For the clause to be true, the values in the set must satisfy the clause's condition _quantitatively_, that is, in the right quantity.

Overview: ANY, ALL, and NONE

When a comparison field is multi-valued with respect to a query's perspective table, it is possible that all, some, or none of its values related to a given perspective object will match the target value or range. By default, sets are implicitly compared using "any" quantification. However, the explicit quantifiers ANY, ALL, and NONE can be used. Here is how these quantifiers work:

• Every clause can be thought of as having the general form = {target}, which means the values produced by the must match the values in the {target} set. The {target} set is defined by the comparison operator and values specified in the clause (e.g., Size > 10, Size = [1000 TO 10000]). How the link path matches the {target} set depends on how the is quantified.

• When a field path has no explicit quantification, such as A.B.C, then the path is implicitly quantified with "any". This means that at least one value in the set {A.B.C} must match the target value set. Logically, the set is constructed by gathering all C's for all B's for all A's into a single set; if the intersection between this set and the {target} set is not empty, the quantified expression is true.

• The explicitly quantified link path ANY(A.B.C) is identical to the implicitly quantified link path A.B.C.

• If a set implicitly or explicitly quantified with ANY is empty, it does not match the {target} set. Hence, if there are no A values, or no A's have a B value, or no B's have a C value, the set is empty and the clause cannot match the {target} set.

• The explicit quantifier ALL requires that the is not an empty set and that every member is contained in the {target} set. For ALL(A.B.C), some A's might not have a B value and some B's might not have a C value, but that's OK – as long as the set {A.B.C} is not be empty and every C value in the set matches the {target} set, the clause is true.

• The explicit quantifier NONE requires that no member of the is contained in the {target} set. Unlike ANY and ALL, this means NONE matches the {target} set if the set is empty. Otherwise, no member of the set {A.B.C} must match a {target} set value for the clause to be true. Semantically, NONE is the same as NOT ANY.

• A can use multiple quantifiers, up to one per field. For example ALL(A).ANY(B).NONE(C) can be interpreted as "No C's for any B for every A can match a target value". Put another way, for the quantified to be true for a perspective object P, all of P's A values must have at least one B value for which none of its C values match the {target} set. The same existence criteria applies as described above: if a given B has no C values, NONE(C) is true for that B; if a given A has no B values, ANY(B) is false for that A; if a given object P has no A values, ALL(A) is false for that P.

• Implicit "any" quantification is used for any link "sub-path" this is not explicitly quantified. For example, ALL(A).B.C is the same as ALL(A).ANY(B.C). Similarly, A.B.NONE(C) is the same as ANY(A.B).NONE(C).

• Note that ALL(A.B) is not the same as ALL(A).ALL(B). This is because in the first case, we collect the set of all B's for all A's and test the set – if a given A has no B's, that's OK as long as the set {A.B} is not empty and every value matches the {target} set. However, in ALL(A).ALL(B), if a given A has no B values, ALL(A) fails for that A, therefore the clause is false for the corresponding perspective object.

• However, ANY(A.B) is effectively the same as ANY(A).ANY(B) because in both cases we only need one A to have one B that matches the {target} set. Similarly, NONE(A.B) is effectively the same as NONE(A).NONE(B) because NONE(X) is the same as NOT ANY(X).

• Nested quantifiers are not allowed (e.g., ANY(A.ALL(B))).

The following sections look more closely at explicit quantifiers in certain instances.

Quantifiers on MV Scalar Fields

Explicit quantifiers can be used with a single MV scalar as shown in these examples:

ANY(Tags) = Confidential
ALL(Tags) : (Priority, Internal)
NONE(Tags) = "Do Not Forward"  

In the first example, the ANY quantifier acts the same as if no explicit quantifier was given. That is, an object is selected if at least one Tags value is Confidential. In the second example, all of an object's Tags values must have one of the terms in the set {Priority, Internal} in order to be selected. In the last example, none of the object'sTags can equal the value "Do Not Forward" (case-insensitive). The NONE quantifier is true if the quantified field is null.

Quantifiers on SV Scalar Fields

Explicit quantification is allowed on SV scalars. An SV scalar is treated as a set of zero or one value, otherwise it is treated the same as an MV scalar. Strictly speaking, quantifiers are not needed on SV scalars because simple comparisons produce the same results. For example:

ANY(Name) = Fred     // same as Name = Fred
ALL(Name) = Fred     // same as Name = Fred
NONE(Name) = Fred    // same as NOT Name = Fred  

Quantifiers on Link Fields

When a clause's comparison field is a single link field, explicit quantifiers have similar semantics as with MV scalars. Examples:

ANY(Manager) = ABC
ALL(DirectReports) = (DEF, GHI) // same as ALL(DirectReports) IN (DEF, GHI)
NONE(MessageAddresses) = XYZ  

The first case is the same as implicit quantification: an object is selected if it has at least one Manager whose object ID is ABC. In the second case, the object is selected all DirectReports values point to either of the objects with IDs DEF or GHI. In the third example, the object must not have any MessageAddresses values equal to XYZ.

Quantifiers with IS NULL

Special semantics are applied when a link path is used with the IS NULL clause. When quantified with ANY, a link path is null if the resulting value set is empty. In the following example, each link is implicitly qualified with ANY:

InternalRecipients.Person.LastName IS NULL

This clause is true for a perspective object if:

• InternalRecipients is null, or
• Person is null for every InternalRecipients object, or
• LastName is null for ever Person object.

In other words, if at least one InternalRecipients.Person.LastName exists, IS NULL is false. Since ANY is associative, any portion of the link path can be explicitly quantified and the result is the same.

If a link path used with IS NULL is quantified with ALL, the clause is true only if (1) the quantified portion of the path is not empty but (2) the remainder of the path produces an empty set. For example:

ALL(InternalRecipients).Person.LastName IS NULL

This clause is true if InternalRecipients is not null but Person.LastName is null for every InternalRecipients value. Either Person can be null or a Person's LastName can be null to satisfy the second condition. Alternatively, consider this ALL quantification:

ALL(InternalRecipients.Person).LastName IS NULL

In this case, InternalRecipients cannot be null, at least one InternalRecipients must have a Person, but no Person objects have a LastName. Essentially, ALL adds an existential requirement to the quantified portion of the link path.

If the quantifier NONE is used, the quantified portion of the link path can be empty. For example:

NONE(InternalRecipients).Person.LastName IS NULL

This clause is true for an object if either InternalRecipients is null or if LastName is not null for every InternalRecipients.Person.

When a quantified IS NULL clause is negated, it selects the opposite objects than without negation. This means that all objects selected by the following clause:

Q(X).Y IS NULL

are selected by the clause:

NOT Q(X).Y IS NULL

where Q is a quantifier and X and Y are field paths.