It’s a missing feature (or bug), so definitely needs a JIRA case. We have other JIRA cases for deficiencies in the JDBC adapter, e.g. https://issues.apache.org/jira/browse/CALCITE-981 <https://issues.apache.org/jira/browse/CALCITE-981>, https://issues.apache.org/jira/browse/CALCITE-734 <https://issues.apache.org/jira/browse/CALCITE-734>.
I don’t know whether you’re seeing a Project where some of the expressions are RexOver, or whether you’re seeing a Window. If the latter, you’ll need to add a ‘public result visit(Window e)’ method in RelToSqlConverter. Different JDBC providers support different features, and have different syntax. For those difference, add methods to SqlDialect. You’ll need to add tests to JdbcAdapterTest and unfortunately the default database, hsqldb, doesn’t support window functions, so you’ll need to add some tests that use postgres. Julian > On Nov 22, 2016, at 10:01 AM, Christian Tzolov <[email protected]> wrote: > > Hi there, > > Using jdbc adapter in the latest calcite (1.11.0) build i can not perform > windows aggregations. While converting the Rel into SQL the > RelImplementation drops the 'OVER(...)' blocks. > > For example if you try the following sql query against Postgres db: > > SELECT "id", "device_id", "transaction_value", "account_id", "ts_millis", > MAX("ts_millis") OVER(partition by "device_id") as > "last_version_number" > FROM "HAWQ"."transaction" > WHERE "device_id" = 1445 > > The result sql would look like this: > > [SELECT "id", "device_id", "transaction_value", "account_id", "ts_millis", > MAX("ts_millis") AS "last_version_number" > FROM "transaction" > WHERE "device_id" = 1445] > > e.g. the entire OVER block is ignored. > > I've managed to track the problem down the SqlImplementeror#571-7594 ( > https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L571-L594 > ) > > There the RexOver is casted to (and handled as) RexCall instance and the > RexOver#window is completely ignored. > > This looks like a problem to me. Shall i open a Jira ticket for it? > > Also what should be the right way to include the RexWindow attribute in the > SQL generation? > > Cheers, > Christian > > -- > Christian Tzolov <http://www.linkedin.com/in/tzolov> | Solution Architect, > EMEA Practice Team | Pivotal <http://pivotal.io/> > [email protected]|+31610285517
