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

Scan HAVING clause for sub-queries and IN-lists

    Details

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

      Description

      This is another variant of CALCITE-516 and CALCITE-614.

      select count(*) from emp 
        group by emp.deptno
        having sum(case when emp.empno in (1, 2, 3) then emp.sal else 0 end) 
           between 10000.0 and 20000.0
      
      java.lang.AssertionError: Internal error: while converting CASE WHEN `EMP`.`EMPNO` IN (1, 2, 3) THEN `EMP`.`SAL` ELSE 0 END
      	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4058)
      	at org.apache.calcite.sql2rel.StandardConvertletTable.convertCase(StandardConvertletTable.java:301)
      	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      	at java.lang.reflect.Method.invoke(Method.java:606)
      	at org.apache.calcite.sql2rel.ReflectiveConvertletTable$1.convertCall(ReflectiveConvertletTable.java:87)
      	at org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:60)
      	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4212)
      	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:3668)
      	at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:130)
      	at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:4105)
      	at org.apache.calcite.sql2rel.SqlToRelConverter$AggConverter.visit(SqlToRelConverter.java:4483)
      	at org.apache.calcite.sql2rel.SqlToRelConverter$AggConverter.visit(SqlToRelConverter.java:4329)
      	at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:130)
      	at org.apache.calcite.sql2rel.SqlToRelConverter$AggConverter.visit(SqlToRelConverter.java:4528)
      	at org.apache.calcite.sql2rel.SqlToRelConverter$AggConverter.visit(SqlToRelConverter.java:4329)
      	at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:130)
      	at org.apache.calcite.sql2rel.SqlToRelConverter.createAggImpl(SqlToRelConverter.java:2573)
      	at org.apache.calcite.sql2rel.SqlToRelConverter.convertAgg(SqlToRelConverter.java:2510)
      

        Activity

        Hide
        seanhychu Sean Hsuan-Yi Chu added a comment -

        I have some idea of this issue. I would like to work on this.

        Show
        seanhychu Sean Hsuan-Yi Chu added a comment - I have some idea of this issue. I would like to work on this.
        Hide
        seanhychu Sean Hsuan-Yi Chu added a comment -

        For issue is when we have aggregate functions in having clause, we should add them to aggList to replace subqueries.
        I have a fix for this issue. I will send a pull request shortly.

        Show
        seanhychu Sean Hsuan-Yi Chu added a comment - For issue is when we have aggregate functions in having clause, we should add them to aggList to replace subqueries. I have a fix for this issue. I will send a pull request shortly.
        Hide
        seanhychu Sean Hsuan-Yi Chu added a comment -
        Show
        seanhychu Sean Hsuan-Yi Chu added a comment - A pull request was sent out: https://github.com/apache/incubator-calcite/pull/85
        Hide
        julianhyde Julian Hyde added a comment - - edited

        Didn't need the visitor – if the HAVING clause contains an aggegrate it is sufficient to add the whole HAVING clause to aggList. I removed it, to simplify.

        Show
        julianhyde Julian Hyde added a comment - - edited Didn't need the visitor – if the HAVING clause contains an aggegrate it is sufficient to add the whole HAVING clause to aggList. I removed it, to simplify.
        Show
        julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/61ba314f .
        Hide
        julianhyde Julian Hyde added a comment -

        Resolved in release 1.3.0-incubating (2015-05-30).

        Show
        julianhyde Julian Hyde added a comment - Resolved in release 1.3.0-incubating (2015-05-30).

          People

          • Assignee:
            julianhyde Julian Hyde
            Reporter:
            amansinha100 Aman Sinha
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development