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

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

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.13.0
    • None

    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

              rajeshbabu Rajeshbabu Chintaguntla
              julianhyde Julian Hyde
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: