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

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

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

    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

              Dates

              • Created:
                Updated:
                Resolved:

                Issue deployment