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