Calcite IN operator handling

[Mykola Zerniuk <my...@oath.com.INVALID>]

Hello calcite devs!
My name is Mykola, a am software engineer. We actively use calcite
framework in our project, and recently faced with next issue:
Some of SQL has huge IN list (more than 1500).  Right now Calcite's SQL
parser replaces INs with ORs or sub-queries depending on the
inSubQueryThreshold.
In case of OR transformation sql become really huge and complex, some of
databases (in our case - Vertica and Amazon Athena) cannot execute such
queries. Moreover this transformation
can be the reason of StackOverFlow error.
In other case IN is converted into full table scans that is not appropriate
for our requirements.
So we want to have an option do not make any conversions to IN.
I found kinda similar question here:
https://www.mail-archive.com/dev@calcite.apache.org/msg06929.html
As i understand that this could be the reason of some expression
simplification problem.
This change is very important to us, so we want to create some fix.
On quick look we see that there are conversions in SqlToRelConverter where
is check should we do OR conversion or translate values list into inline
table. We want to add some additional flow there to assign to subQuery.expr
some RexNode that will left IN "as is".
It would be nice to get from you some help or suggestion to make it
possible. Maybe there are some other places we have to pay attention.
If you see this fix necessary for calcite in future, we can make it
available in next calcite releases.

Thank you.
Mykola Zerniuk

Re: Calcite IN operator handling

[Stamatis Zampetakis <za...@gmail.com>]

Hi Mykola,

In the previous discussion that you cited above, people lean towards the
option of adding a new rule and not really changing the converter. From a
quick look, it seems that Zoltan also shared some code for transforming ORs
back to IN which you may be able to reuse/extend as you like.

Apart from that, it is worth noting that if you create a query using the
RelBuilder then you can avoid these transformations (see
https://issues.apache.org/jira/browse/CALCITE-2444).

Finally, whatever direction you choose to follow have in mind that IN
operator is not implemented by the runtime of Calcite (see
https://issues.apache.org/jira/browse/CALCITE-2618).

Best,
Stamatis


Στις Τετ, 10 Οκτ 2018 στις 7:04 μ.μ., ο/η Michael Mior <mm...@apache.org>
έγραψε:

> You should be able to work around this by setting inSubQueryThreshold to
> Integer.MAX_VALUE..
>
> --
> Michael Mior
> mmior@apache.org
>
>
> Le mer. 10 oct. 2018 à 10:04, Mykola Zerniuk
> <my...@oath.com.invalid> a écrit :
>
> > Hello calcite devs!
> > My name is Mykola, a am software engineer. We actively use calcite
> > framework in our project, and recently faced with next issue:
> > Some of SQL has huge IN list (more than 1500).  Right now Calcite's SQL
> > parser replaces INs with ORs or sub-queries depending on the
> > inSubQueryThreshold.
> > In case of OR transformation sql become really huge and complex, some of
> > databases (in our case - Vertica and Amazon Athena) cannot execute such
> > queries. Moreover this transformation
> > can be the reason of StackOverFlow error.
> > In other case IN is converted into full table scans that is not
> appropriate
> > for our requirements.
> > So we want to have an option do not make any conversions to IN.
> > I found kinda similar question here:
> > https://www.mail-archive.com/dev@calcite.apache.org/msg06929.html
> > As i understand that this could be the reason of some expression
> > simplification problem.
> > This change is very important to us, so we want to create some fix.
> > On quick look we see that there are conversions in SqlToRelConverter
> where
> > is check should we do OR conversion or translate values list into inline
> > table. We want to add some additional flow there to assign to
> subQuery.expr
> > some RexNode that will left IN "as is".
> > It would be nice to get from you some help or suggestion to make it
> > possible. Maybe there are some other places we have to pay attention.
> > If you see this fix necessary for calcite in future, we can make it
> > available in next calcite releases.
> >
> > Thank you.
> > Mykola Zerniuk
> >
>

Re: Calcite IN operator handling

[Michael Mior <mm...@apache.org>]

You should be able to work around this by setting inSubQueryThreshold to
Integer.MAX_VALUE..

--
Michael Mior
mmior@apache.org


Le mer. 10 oct. 2018 à 10:04, Mykola Zerniuk
<my...@oath.com.invalid> a écrit :

> Hello calcite devs!
> My name is Mykola, a am software engineer. We actively use calcite
> framework in our project, and recently faced with next issue:
> Some of SQL has huge IN list (more than 1500).  Right now Calcite's SQL
> parser replaces INs with ORs or sub-queries depending on the
> inSubQueryThreshold.
> In case of OR transformation sql become really huge and complex, some of
> databases (in our case - Vertica and Amazon Athena) cannot execute such
> queries. Moreover this transformation
> can be the reason of StackOverFlow error.
> In other case IN is converted into full table scans that is not appropriate
> for our requirements.
> So we want to have an option do not make any conversions to IN.
> I found kinda similar question here:
> https://www.mail-archive.com/dev@calcite.apache.org/msg06929.html
> As i understand that this could be the reason of some expression
> simplification problem.
> This change is very important to us, so we want to create some fix.
> On quick look we see that there are conversions in SqlToRelConverter where
> is check should we do OR conversion or translate values list into inline
> table. We want to add some additional flow there to assign to subQuery.expr
> some RexNode that will left IN "as is".
> It would be nice to get from you some help or suggestion to make it
> possible. Maybe there are some other places we have to pay attention.
> If you see this fix necessary for calcite in future, we can make it
> available in next calcite releases.
>
> Thank you.
> Mykola Zerniuk
>