[jira] [Created] (CALCITE-1174) OVER clause isn't processed correctly

["Taras Supyk (JIRA)" <ji...@apache.org>]

Taras Supyk created CALCITE-1174:
------------------------------------

             Summary: OVER clause isn't processed correctly
                 Key: CALCITE-1174
                 URL: https://issues.apache.org/jira/browse/CALCITE-1174
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.8.0
         Environment: PostgreSQL 
            Reporter: Taras Supyk
            Assignee: Julian Hyde


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);



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)