Supporting subqueries in ON clauses

[James Starr <ja...@gmail.com>]

I have been working on supporting subqueries in ON clauses for some time.

My current PR supports non-correlated subqueries in ON clauses of left and
inner joins where subquery rewrite does not attempt to use the root to do
the rewrite.  For correlated subqueries and  subqueries for joins other
than inner or left an exception will be thrown.

https://github.com/apache/calcite/pull/2364

The subqueries cannot just be applied on top of the join as a filter
because ON clauses for non-inner joins are not just filters.  I am
currently supporting left joins by rewriting the subqueries on the right
side of the join, allowing multiplicity to be maintained.  To support outer
joins, I plan on rewriting the query to a left join + union of all values
from the right that did not join to the left.  In order to support
correlated queries in On clauses, calcite's correlate type is insufficient
because the join being created could be the correlate join but would also
have a filter that needs to be applied.  I could either add a new node or
enhance correlate/join.

Concerning Blackboard.register() vs mutating Blackboard.root to capture the
subqueries after they have been rewritten, unless there is an easy way to
do the necessary rewrite for reworking the query, then subquery rewrite
needs to move away from directly mutating the root node.

James

Re: Supporting subqueries in ON clauses

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

Thanks for taking this on.

There are two ways of handling sub-queries: one is to have
SqlToRelConverter expand them during translation, and the other is to
wrap them in RexSubQuery and treat them as scalar expressions until
they are handled by SubQueryRemoveRule. I couldn't tell which approach
you are taking, but I recommend the latter because it is less
disruptive to the intricate machinery of SqlToRelConverter.

I see you have some SqlToRelConverter tests checking that particular
exceptions are thrown. I know that the exceptions reflect current
limitations of SqlToRelConverter, but still, we shouldn't expose such
implementation details to the user. I think those exceptions should be
thrown by the validator, and tested in SqlValidatorTest. Feel free to
have SqlToRelConverter throw AssertionError if it hits queries that it
can't handle; SqlValidator should have caught them first.

I've added the same comments to the JIRA case [1]; let's continue the
conversation there.

Julian

[1] https://issues.apache.org/jira/browse/CALCITE-4340

On Tue, Mar 30, 2021 at 9:27 PM James Starr <ja...@gmail.com> wrote:
>
> I have been working on supporting subqueries in ON clauses for some time.
>
> My current PR supports non-correlated subqueries in ON clauses of left and
> inner joins where subquery rewrite does not attempt to use the root to do
> the rewrite.  For correlated subqueries and  subqueries for joins other
> than inner or left an exception will be thrown.
>
> https://github.com/apache/calcite/pull/2364
>
> The subqueries cannot just be applied on top of the join as a filter
> because ON clauses for non-inner joins are not just filters.  I am
> currently supporting left joins by rewriting the subqueries on the right
> side of the join, allowing multiplicity to be maintained.  To support outer
> joins, I plan on rewriting the query to a left join + union of all values
> from the right that did not join to the left.  In order to support
> correlated queries in On clauses, calcite's correlate type is insufficient
> because the join being created could be the correlate join but would also
> have a filter that needs to be applied.  I could either add a new node or
> enhance correlate/join.
>
> Concerning Blackboard.register() vs mutating Blackboard.root to capture the
> subqueries after they have been rewritten, unless there is an easy way to
> do the necessary rewrite for reworking the query, then subquery rewrite
> needs to move away from directly mutating the root node.
>
> James