Quantifiers

EQL provides existential and universal quantifiers for use with Boolean expressions against sets.

Both types of expressions can appear in any context that accepts a Boolean expression, such as SELECT clauses, WHERE clauses, HAVING clauses, ORDER BY clauses, join conditions, and so on.

Existential quantifier

An existential quantifier uses the SOME keyword. In an existential quantifier, if any item in the set has a match based on the comparison operator that is used, the returned value is TRUE.

The syntax of the existential quantifier is: 
SOME id IN set SATISFIES (booleanExpr)
where:
  • id is an arbitrary identifier for the item to be compared. The identifier must use the NCName format.
  • set is a set of any set data type.
  • booleanExpr is any expression that produces a Boolean (or NULL).

The expression binds the identifier id within booleanExpr. This binding shadows any other attributes with the same name inside the predicate. Note that this shadowing applies only to references to identifiers/attributes that do not have a statement qualifier.

To evaluate an existential quantifier expression, EQL evaluates the predicate expression for every member of the indicated set. Then, EQL computes the results of the quantifier based on these predicate values as follows:
  1. If set is empty, the quantifier is FALSE.
  2. Otherwise, if booleanExpr is true for least one element of set, the quantifier is TRUE.
  3. Otherwise, if booleanExpr is false for every id element of set, the quantifier is FALSE.
  4. Otherwise (the values of booleanExpr are either false or NULL, with at least one NULL), the quantifier is NULL.
Some results of this evaluation are:
  • SOME x IN { } SATISFIES (x > 0) is FALSE.
  • SOME x IN { -3, -2, 1 } SATISFIES (x > 0) is TRUE, because the predicate expression is true for x = 1.
  • SOME x IN { 5, 7, 10 } SATISFIES (x > 0) is TRUE, because the predicate is true for x = 5.
  • SOME x IN { 'foo', '3', '4' } SATISFIES (TO_INTEGER(x) > 0) is TRUE, because the predicate is true for x = '3'.
  • SOME x IN { 'foo', '-1', '-2' } SATISFIES (TO_INTEGER(x) > 0) is NULL. The predicate is false for x = '-1' and x = '-2', but NULL for x = 'foo'.
In this existential quantifier example, Body is a multi-assign string attribute (one of whose assignments on several records is 'Robust'): 
RETURN results AS
SELECT 
   WineID AS idRec,
   WineType AS wines,
   Body AS bodyAttr
FROM WineState
WHERE SOME x IN Body SATISFIES (x = 'Robust')
ORDER BY idRec
The result of this statement would be: 
bodyAttr          idRec   wines
---------------------------------------------
| { Robust, Tannins } |  4 | Red            |
| { Robust }          |  6 | Red            |
| { Oak, Robust       |  8 | Red            |
| { Robust, Tannins } | 11 | Zinfandel      |
| { Fresh, Robust }   | 19 | White          |
| { Firm, Robust }    | 22 | Blanc de Noirs |
| { Robust }          | 23 | Brut           |
| { Robust }          | 24 | Brut           |
| { Firm, Robust }    | 25 | White          |
---------------------------------------------

Only the nine records that have the Body='Robust' assignment are returned.

Universal quantifier

A universal quantifier uses the EVERY keyword. In a universal quantifier, if every item in the set has a match based on the comparison operator that is used, the returned value is TRUE.

The syntax of the universal quantifier is: 
EVERY id IN set SATISFIES (booleanExpr)
where id, set, and booleanExpr have the same meanings as in the existential quantifier.

The expression binds the identifier id within booleanExpr. This binding shadows any other attributes with the same name inside the predicate. Note that this shadowing applies only to references to identifiers/attributes that do not have a statement qualifier.

Similar to an existential quantifier expression, for a universal quantifier expression EQL evaluates the predicate expression for every member of the indicated set. Then, EQL computes the results of the quantifier based on these predicate values as follows:
  1. If set is empty, the quantifier is TRUE.
  2. Otherwise, if booleanExpr is false for at least one element of set, the quantifier is FALSE.
  3. Otherwise, if booleanExpr is true for every element of set, the quantifier is TRUE.
  4. Otherwise (the values of booleanExpr are either true or NULL, with at least one NULL), the quantifier is NULL.
Some results of this evaluation are:
  • EVERY x IN { } SATISFIES (x > 0) is TRUE.
  • EVERY x IN { -3, -2, 1 } SATISFIES (x > 0) is FALSE, because the predicate is false for x = -3.
  • EVERY x IN { 5, 7, 10 } SATISFIES (x > 0) is TRUE, because the predicate is true for every value in the set.
  • EVERY x IN { 'foo', '3', '4' } SATISFIES (TO_INTEGER(x) > 0) is NULL. The predicate is true for x = '3' and x = '4', but NULL for x = 'foo'.
  • EVERY x IN { 'foo', '-1', '-2' } SATISFIES (TO_INTEGER(x) > 0) is FALSE, because the predicate is false for x = '-1'.
This universal quantifier example is very similar to the existential quantifier example above: 
RETURN results AS
SELECT 
   WineID AS idRec,
   WineType AS wines,
   Body AS bodyAttr
FROM WineState
WHERE (EVERY x IN Body SATISFIES (x = 'Robust')) AND (WineID IS NOT NULL)
ORDER BY idRec
The result of this statement would be: 
bodyAttr     idRec wines
--------------------------------
|            |  1 | Chardonnay |
|            |  2 | Chardonnay |
| { Robust } |  6 | Red        |
|            | 17 | Merlot     |
|            | 20 | White      |
|            | 21 | White      |
| { Robust } | 23 | Brut       |
| { Robust } | 24 | Brut       |
--------------------------------

The only records that are returned are those that have only one Body='Robust' assignment (Records 6, 23, and 24) and those that have no Body assignments (Records 1, 2, 17, 20, and 21).

In the query, note the use of the "WineID IS NOT NULL" expression in the WHERE clause. This prevents the return of other records in the system for which the universal expression would normally be evaluated as TRUE but which would return empty sets.