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

Allow GROUP BY and HAVING to reference SELECT expressions by ordinal and alias

    Details

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

      Description

      Allow GROUP BY and HAVING to reference SELECT expressions by ordinal and alias. It is not standard SQL, but MySQL and PostgreSQL allow it.

      See Stack Overflow: SQL - using alias in Group By.

      It would be enabled only by new methods isGroupByOrdinal and isGroupByAlias in SqlConformance.

      We might allow alias in the HAVING clause (as described in HIVE-10557) but ordinal does not make sense.

      Expressions that are not available before grouping would be illegal; for instance:

      select count(*) as c
      from t
      group by c
      

      We'd also need rules to resolve ambiguous expressions. For instance, in

      select e.empno as deptno
      from emp as e join dept as d
      where e.deptno = d.deptno
      group by deptno
      

      does deptno refer to e.deptno, d.deptno, or e.empno?

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                rajeshbabu Rajeshbabu Chintaguntla
                Reporter:
                julianhyde Julian Hyde
              • Votes:
                1 Vote for this issue
                Watchers:
                10 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: