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

When generating SQL, translate SUM0(x) to COALESCE(SUM(x), 0)

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.8.0
    • Fix Version/s: 1.18.0
    • Component/s: jdbc-adapter
    • Labels:
      None
    • Environment:

      PostgreSQL

      Description

      JDBC adapter wrongly pushes SUM0 down to PostgreSQL.

      select "rnum", "c1", avg("c1") over (partition by "rnum") from "public"."stdpop";

      This query in calcite throws exception:

      0: jdbc:calcite:model=postgres.json> select "rnum", "c1", avg("c1") over (partition by "rnum") from "public"."stdpop";
      Error: Error while executing SQL "select "rnum", "c1", avg("c1") over (partition by "rnum") from "public"."stdpop"": while executing SQL [SELECT "rnum", "c1", CAST(CASE WHEN COUNT("c1") > 0 THEN CAST($SUM0("c1") AS INTEGER) ELSE NULL END / COUNT("c1") AS INTEGER)
      FROM "stdpop"] (state=,code=0)
      java.sql.SQLException: Error while executing SQL "select "rnum", "c1", avg("c1") over (partition by "rnum") from "public"."stdpop"": while executing SQL [SELECT "rnum", "c1", CAST(CASE WHEN COUNT("c1") > 0 THEN CAST($SUM0("c1") AS INTEGER) ELSE NULL END / COUNT("c1") AS INTEGER)
      FROM "stdpop"]
      	at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
      	at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
      	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:143)
      	at org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:177)
      	at sqlline.Commands.execute(Commands.java:822)
      	at sqlline.Commands.sql(Commands.java:732)
      	at sqlline.SqlLine.dispatch(SqlLine.java:807)
      	at sqlline.SqlLine.begin(SqlLine.java:681)
      	at sqlline.SqlLine.start(SqlLine.java:398)
      	at sqlline.SqlLine.main(SqlLine.java:292)
      Caused by: java.lang.RuntimeException: while executing SQL [SELECT "rnum", "c1", CAST(CASE WHEN COUNT("c1") > 0 THEN CAST($SUM0("c1") AS INTEGER) ELSE NULL END / COUNT("c1") AS INTEGER)
      FROM "stdpop"]
      	at org.apache.calcite.runtime.ResultSetEnumerable.enumerator(ResultSetEnumerable.java:148)
      	at org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
      	at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:85)
      	at org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:190)
      	at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:65)
      	at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:44)
      	at org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:576)
      	at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:578)
      	at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:581)
      	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:135)
      	... 7 more
      Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$"
        Position: 63
      	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
      	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
      	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:405)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:285)
      	at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
      	at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
      	at org.apache.calcite.runtime.ResultSetEnumerable.enumerator(ResultSetEnumerable.java:143)
      	... 16 more
      

      And what the same query returns in PostgreSQL:

       rnum | c1 |        avg         
      ------+----+--------------------
          1 |  1 | 2.0000000000000000
          1 |  2 | 2.0000000000000000
          1 |  3 | 2.0000000000000000
          2 |  4 | 5.0000000000000000
          2 |  5 | 5.0000000000000000
          2 |  6 | 5.0000000000000000
      

      Preconditions:

      create table stdpop( rnum int, c1 int);
      insert into stdpop values(1,1);
      insert into stdpop values(1,2);
      insert into stdpop values(1,3);
      insert into stdpop values(2,4);
      insert into stdpop values(2,5);
      insert into stdpop values(2,6);
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                dr-wolf Taras Supyk
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: