Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-3815

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

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.23.0
    • Component/s: None

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                hanu.ncr Hanumath Rao Maduri
                Reporter:
                julianhyde Julian Hyde
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 2h 50m
                  2h 50m