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
>
