Here is the jira ticket: https://issues.apache.org/jira/browse/CALCITE-1506 and the PR: https://github.com/apache/calcite/pull/329
The PR should cover most cases but i'm not sure how to convert some range bounds from Rel to SQL. Would appreciate if you can review it. Cheers, Christian On 23 November 2016 at 04:59, Julian Hyde <[email protected]> wrote: > Yes, you won’t need to add “visit(Window)”. Just make SqlImplementor.toSql > handle RexOver within its expressions. > > > On Nov 22, 2016, at 12:02 PM, Christian Tzolov <[email protected]> > wrote: > > > > Thanks Julian, > > > > I will open JIRA ticket and will add test for postgres dialect. Regarding > > your question: > > > >> 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. > > > > I think it is a Project with RexOver expressions rather than Window. Here > > is the explain plan: > > 'PLAN' > > 'JdbcToEnumerableConverter > > JdbcProject(id=[$0], device_id=[$1], transaction_value=[$2], > > account_id=[$3], ts_millis=[$4], last_version_number=[MAX($4) OVER > > (PARTITION BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED > > FOLLOWING)]) > > JdbcFilter(condition=[=($1, 1445)]) > > JdbcTableScan(table=[[HAWQ, transaction]]) > > ' > > So i guess the visit(Window e) wouldn't work in this case? > > > > Cheers, > > Christian > > > > > > > > > > > > > > On 22 November 2016 at 20:18, Julian Hyde <[email protected]> wrote: > > > >> 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 > >> > > -- Christian Tzolov <http://www.linkedin.com/in/tzolov> | Solution Architect, EMEA Practice Team | Pivotal <http://pivotal.io/> [email protected]|+31610285517
