[
https://issues.apache.org/jira/browse/CALCITE-1174?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-1174.
----------------------------------
Resolution: Fixed
Fixed in
[332ffb44|http://git-wip-us.apache.org/repos/asf/calcite/commit/332ffb44]. If
we see {{SUM0(x)}} we now translate that to {{COALESCE(SUM(x), 0)}}, which I
think most DBs can handle.
The second issue is a duplicate of CALCITE-1506 so was fixed long ago.
> When generating SQL, translate SUM0(x) to COALESCE(SUM(x), 0)
> -------------------------------------------------------------
>
> Key: CALCITE-1174
> URL: https://issues.apache.org/jira/browse/CALCITE-1174
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
> Affects Versions: 1.8.0
> Environment: PostgreSQL
> Reporter: Taras Supyk
> Assignee: Julian Hyde
> Priority: Major
> Fix For: 1.18.0
>
>
> JDBC adapter wrongly pushes SUM0 down to PostgreSQL.
> {code:java}
> select "rnum", "c1", avg("c1") over (partition by "rnum") from
> "public"."stdpop";{code}
> This query in calcite throws exception:
> {noformat}
> 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
> {noformat}
> And what the same query returns in PostgreSQL:
> {noformat}
> 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
> {noformat}
> Preconditions:
> {code:java}
> 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);
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
