Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
1.8.0
-
None
-
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
- is related to
-
CALCITE-1506 Push OVER Clause to underlying SQL via JDBC adapter
- Closed