[CALCITE-3815] Add missing SQL standard aggregate functions: EVERY, SOME, INTERSECTION

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.23.0
• Labels: pull-request-available
• Assignee: Hanumath Rao Maduri
• Reporter: Julian Hyde
• Created: 23/Feb/20 19:20
• Updated: 01/Jun/24 01:25
• Resolved: 21/Mar/20 15:24

Description

Add missing SQL standard aggregate functions: EVERY, SOME, INTERSECTION.

Examples:

SELECT deptno,
  EVERY(sal < 3000) AS es, SOME(sal < 1000) AS ss,
  EVERY(comm < 500) AS ec, SOME(comm < 500) AS sc
FROM emp
GROUP BY deptno;

+--------+-------+-------+-------+------+
| DEPTNO |  ES   |  SS   |  EC   |  SC  |
+--------+-------+-------+-------+------+
| 10     | FALSE | FALSE |       |      |
| 20     | FALSE | TRUE  |       |      |
| 30     | TRUE  | TRUE  | FALSE | TRUE |
+--------+-------+-------+-------+------+

EVERY and SOME can be implemented by translating to MIN, MAX:

• EVERY(condition) is equivalent to MIN(condition);
• SOME(condition) is equivalent to MAX(condition)

where condition is a BOOLEAN expression, possibly allowing NULL values).

INTERSECTION computes the intersection of collections (arrays and multisets). (Compare to FUSION, which computes the union of collections.)

FUSION is in the operator table but there is no code-generation for it. This task should implement FUSION and INTERSECTION so that we can run queries in Enumerable mode.

Issue Links

• is depended upon by: CALCITE-2935 Add BOOL_OR, BOOL_AND, LOGICAL_OR, LOGICAL_AND aggregate functions
• relates to: CALCITE-6418 Expression with ALL aggregate return unexpected result.
• links to: GitHub Pull Request #1849