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

HAVING clauses fail validation when type coercion is applied to GROUP BY clause

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Critical
    • Resolution: Fixed
    • None
    • 1.39.0
    • None

    Description

      Given a sql statement like:

      select if(EMP.empno <= CAST(18 AS DOUBLE), 'youth', 'adult') as adult_or_child

      from EMP

      GROUP BY if(EMP.empno <= CAST(18 AS DOUBLE), 'youth', 'adult')

      HAVING if(EMP.empno <= CAST(18 AS DOUBLE), 'youth', 'adult')  = 'adult'

       

      Sql Validation fails with the error:

      org.apache.calcite.sql.validate.SqlValidatorException: Expression 'EMP.EMPNO' is not being grouped

       

      Root cause: A cast is applied to the group by clause, to make it GROUP BY if( CAST (EMP.empno AS DOUBLE) <= CAST(18 AS DOUBLE), 'youth', 'adult') 

      But no such cast is applied to the Having Clause. Consequently, an equality comparison on the two fails

       

       

      Attachments

        Issue Links

          Activity

            People

              mbudiu Mihai Budiu
              etracy Tracy Sassaman
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: