[CALCITE-1845] Quantified comparison predicates (SOME, ANY, ALL)

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.14.0
• Labels: phoenix
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 16/Jun/17 03:02
• Updated: 20/Oct/18 00:05
• Resolved: 04/Jul/17 23:20

Description

Support quantified comparison predicates (SOME, ANY, ALL), per the SQL standard.

<comp op> ::=
<equals operator>
| <not equals operator>
| <less than operator>
| <greater than operator>
| <less than or equals operator>
| <greater than or equals operator>

<quantifier> ::=
<all>
| <some>

<all> ::= ALL

<some> ::=
SOME
| ANY

The result of “R <comp op> <quantifier> T” is derived by the application of the implied <comparison predicate> “R <comp op> RT” to every row RT in T.

Case:

• a) If T is empty or if the implied <comparison predicate> is True for every row RT in T, then “R <comp op> <all> T” is True.
• b) If the implied <comparison predicate> is False for at least one row RT in T, then “R <comp op> <all> T” is False.
• c) If the implied <comparison predicate> is True for at least one row RT in T, then “R <comp op> <some> T” is True.
• d) If T is empty or if the implied <comparison predicate> is False for every row RT in T, then “R <comp op> <some> T” is False.
• e) If “R <comp op> <quantifier> T” is neither True nor False, then it is Unknown.

Issue Links

• is depended upon by: CALCITE-530 Phoenix support
• relates to: CALCITE-2634 Query with WITH and SOME parses but fails to convert to rel