JDBC limit in sub query - use row_number and filter

[Andrew O <ao...@gmail.com>]

I am trying out Calcite against a DBMS that does not support the limit
syntax in nested / sub queries.   (The JOOQ blog captures more details /
examples at:
https://www.jooq.org/doc/3.1/manual/sql-building/sql-statements/select-statement/limit-clause/#N467A6
)

I'm interested in how I could have similar behaviour in Calcite.  My
starting attempt / thinking is that it needs a JDBC rule that re-writes /
adjusts Sort operations based on the relevant SqlDialect. However I'm not
sure if / how I can match only sorts within nested queries and avoid
changing the root / non-nested limit operations.


Is this the correct thinking? Is there any relevant examples to look at for
this?

Thanks

Andrew

Re: JDBC limit in sub query - use row_number and filter

[Andrew O <ao...@gmail.com>]

I made some progress getting a local version of this working (thanks for
the pointers).

I'm still working on it, but to share a couple of items encountered.

1) I have not yet identified an obvious entry point to configure / register
a custom version on the JdbcImplementor class

2) it seems that a simple select on a single column with a limit is always
converted to project on a sub query that contains the limit (which is then
triggering my query rewriting unnecessarily).   I think this is caused by
the ProjectSortTransposeRule as part of logical planning. (At this point
I'm thinking I may need a custom JdbcRule to reverse this or to make my
JdbcImplementor smarter,  but I've not tried / validated that)

Thanks

Andrew

On Tue, 16 Apr 2019, 07:40 Andrew O, <ao...@gmail.com> wrote:

> Thanks for the pointers,  I'll take a further look.
>
> Andrew
>
> On Mon, 15 Apr 2019, 20:08 Julian Hyde, <jh...@apache.org> wrote:
>
>> If you decide to write a RelOptRule, your rule could perhaps match
>> RelNode(Sort) rather than Sort, and therefore the rule will not fire at the
>> top-level.
>>
>> However, you could also do the transformation on RelToSqlConverter, which
>> is a more conventional visitor; you can look at the stack to see whether
>> you are at the top.
>>
>> You should add code to the dialect, similar to how
>> SqlNode.emulateNullDirection works, and add dialect tests.
>>
>> Julian
>>
>>
>> > On Apr 14, 2019, at 4:45 AM, Andrew O <ao...@gmail.com> wrote:
>> >
>> > I am trying out Calcite against a DBMS that does not support the limit
>> > syntax in nested / sub queries.   (The JOOQ blog captures more details /
>> > examples at:
>> >
>> https://www.jooq.org/doc/3.1/manual/sql-building/sql-statements/select-statement/limit-clause/#N467A6
>> > )
>> >
>> > I'm interested in how I could have similar behaviour in Calcite.  My
>> > starting attempt / thinking is that it needs a JDBC rule that re-writes
>> /
>> > adjusts Sort operations based on the relevant SqlDialect. However I'm
>> not
>> > sure if / how I can match only sorts within nested queries and avoid
>> > changing the root / non-nested limit operations.
>> >
>> >
>> > Is this the correct thinking? Is there any relevant examples to look at
>> for
>> > this?
>> >
>> > Thanks
>> >
>> > Andrew
>>
>>

Re: JDBC limit in sub query - use row_number and filter

[Andrew O <ao...@gmail.com>]

Thanks for the pointers,  I'll take a further look.

Andrew

On Mon, 15 Apr 2019, 20:08 Julian Hyde, <jh...@apache.org> wrote:

> If you decide to write a RelOptRule, your rule could perhaps match
> RelNode(Sort) rather than Sort, and therefore the rule will not fire at the
> top-level.
>
> However, you could also do the transformation on RelToSqlConverter, which
> is a more conventional visitor; you can look at the stack to see whether
> you are at the top.
>
> You should add code to the dialect, similar to how
> SqlNode.emulateNullDirection works, and add dialect tests.
>
> Julian
>
>
> > On Apr 14, 2019, at 4:45 AM, Andrew O <ao...@gmail.com> wrote:
> >
> > I am trying out Calcite against a DBMS that does not support the limit
> > syntax in nested / sub queries.   (The JOOQ blog captures more details /
> > examples at:
> >
> https://www.jooq.org/doc/3.1/manual/sql-building/sql-statements/select-statement/limit-clause/#N467A6
> > )
> >
> > I'm interested in how I could have similar behaviour in Calcite.  My
> > starting attempt / thinking is that it needs a JDBC rule that re-writes /
> > adjusts Sort operations based on the relevant SqlDialect. However I'm not
> > sure if / how I can match only sorts within nested queries and avoid
> > changing the root / non-nested limit operations.
> >
> >
> > Is this the correct thinking? Is there any relevant examples to look at
> for
> > this?
> >
> > Thanks
> >
> > Andrew
>
>

Re: JDBC limit in sub query - use row_number and filter

[Julian Hyde <jh...@apache.org>]

If you decide to write a RelOptRule, your rule could perhaps match RelNode(Sort) rather than Sort, and therefore the rule will not fire at the top-level.

However, you could also do the transformation on RelToSqlConverter, which is a more conventional visitor; you can look at the stack to see whether you are at the top.

You should add code to the dialect, similar to how SqlNode.emulateNullDirection works, and add dialect tests.

Julian
 

> On Apr 14, 2019, at 4:45 AM, Andrew O <ao...@gmail.com> wrote:
> 
> I am trying out Calcite against a DBMS that does not support the limit
> syntax in nested / sub queries.   (The JOOQ blog captures more details /
> examples at:
> https://www.jooq.org/doc/3.1/manual/sql-building/sql-statements/select-statement/limit-clause/#N467A6
> )
> 
> I'm interested in how I could have similar behaviour in Calcite.  My
> starting attempt / thinking is that it needs a JDBC rule that re-writes /
> adjusts Sort operations based on the relevant SqlDialect. However I'm not
> sure if / how I can match only sorts within nested queries and avoid
> changing the root / non-nested limit operations.
> 
> 
> Is this the correct thinking? Is there any relevant examples to look at for
> this?
> 
> Thanks
> 
> Andrew