[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Calcite IN operator handling

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

Finally, whatever direction you choose to follow have in mind that IN
operator is not implemented by the runtime of Calcite (see


Στις Τετ, 10 Οκτ 2018 στις 7:04 μ.μ., ο/η Michael Mior <mmior@xxxxxxxxxx>

> You should be able to work around this by setting inSubQueryThreshold to
> Integer.MAX_VALUE..
> --
> Michael Mior
> mmior@xxxxxxxxxx
> Le mer. 10 oct. 2018 à 10:04, Mykola Zerniuk
> <mykola.zerniuk@xxxxxxxx.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@xxxxxxxxxxxxxxxxxx/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
> >