You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Christian Tzolov <ct...@pivotal.io> on 2016/11/22 18:01:43 UTC
SqlImplementer doesn't handle RexOver when converting Rel into SQL
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/>
ctzolov@pivotal.io|+31610285517
Re: SqlImplementer doesn't handle RexOver when converting Rel into SQL
Posted by Christian Tzolov <ct...@pivotal.io>.
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 <jh...@apache.org> 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 <ct...@pivotal.io>
> 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 <jh...@apache.org> 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 <ct...@pivotal.io>
> >> 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/>
> >>> ctzolov@pivotal.io|+31610285517
> >>
>
>
--
Christian Tzolov <http://www.linkedin.com/in/tzolov> | Solution Architect,
EMEA Practice Team | Pivotal <http://pivotal.io/>
ctzolov@pivotal.io|+31610285517
Re: SqlImplementer doesn't handle RexOver when converting Rel into
SQL
Posted by Julian Hyde <jh...@apache.org>.
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 <ct...@pivotal.io> 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 <jh...@apache.org> 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 <ct...@pivotal.io>
>> 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/>
>>> ctzolov@pivotal.io|+31610285517
>>
Re: SqlImplementer doesn't handle RexOver when converting Rel into SQL
Posted by Christian Tzolov <ct...@pivotal.io>.
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 <jh...@apache.org> 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 <ct...@pivotal.io>
> 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/>
> > ctzolov@pivotal.io|+31610285517
>
Re: SqlImplementer doesn't handle RexOver when converting Rel into
SQL
Posted by Julian Hyde <jh...@apache.org>.
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 <ct...@pivotal.io> 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/>
> ctzolov@pivotal.io|+31610285517