Hi Julian,
I don't think this has anything to do with the InSubQueryThreshold and
hence, setting InSubQueryThreshold property will not change anything.
The problem described in my ticket (CALCITE-4048) is due to having a NULL
in the IN clause. For example, if the IN clause has 3 items in it such as
IN ('a', 'b', null) and the InSubQueryThreshold is set to > 3 (Max Int for
example), then this IN clause will still be converted to a JOIN (an inline
table). In fact, whatever InSubQueryThreshold you set does not matter and
the mentioned IN clause will always be converted to a JOIN (an inline
table).
- Maytas
On Mon, Jun 8, 2020 at 1:12 PM Julian Hyde <[email protected]> wrote:
> Did you try setting InSubQueryThreshold [1]?
>
> It's not particularly easy to set[2], but AFAIK it works if you set it.
>
> Julian
>
> [1]
> http://calcite.hydromatic.net/apidocs/org/apache/calcite/sql2rel/SqlToRelConverter.Config.html#getInSubQueryThreshold()
>
> [2] https://issues.apache.org/jira/browse/CALCITE-2696
>
> On Sat, Jun 6, 2020 at 5:46 PM Maytas Monsereenusorn <[email protected]>
> wrote:
> >
> > Thanks for the inputs. I have filed
> > https://issues.apache.org/jira/browse/CALCITE-4048 with the summary from
> > this email thread.
> >
> > Thanks,
> > Maytas
> >
> > Thanks Maytas ~
> > >
> > > You requests are reasonable, i think line [1] can be promoted, we can
> move the null literals
> > > comparison to the last of composition predicates (OR/AND) instead of
> forbidden all the IN
> > > to OR(AND) conversion if the IN value list contains nulls.
> > > Can you log an issue there so the one that are interested in it would
> contribute ~
> > >
> > > Thanks again ~
> > >
> > > [1]
> https://github.com/apache/calcite/blob/feae6fbc328e3a7c87693951d1623f8b47ccea59/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L1102
> > >
> > > Best,
> > > Danny Chan
> > > 在 2020年6月6日 +0800 AM5:38，Rui Wang <[email protected]>，写道：
> > > > I see. I was confused on when dim is null, how will null be equal to
> null.
> > > > Did a quick check and Calcite seems to return Unknown for both "null
> =
> > > > null" and "null = false". I am not sure it means for OR, Unknown is
> > > > treated as false thus your example can be simplified.
> > > >
> > > >
> > > > -Rui
> > > >
> > > > On Fri, Jun 5, 2020 at 2:27 PM Rui Wang <[email protected]>
> wrote:
> > > >
> > > > > Per the commit history I think you can find something in
> > > > > https://issues.apache.org/jira/browse/CALCITE-373.
> > > > >
> > > > > Meanwhile, "dim='a' OR dim=null" can be simplified to dim='a'?
> > > > >
> > > > >
> > > > >
> > > > > -Rui
> > > > >
> > > > > On Fri, Jun 5, 2020 at 2:13 PM Maytas Monsereenusorn <
> [email protected]>
> > > > > wrote:
> > > > >
> > > > > > Hi Calcite,
> > > > > >
> > > > > > I am wondering why we convert a value list with NULL into an
> inline table.
> > > > > > The code is in SqlToRelConverter, where we call
> > > > > > !containsNullLiteral(valueList). For example, if I have a SQL
> select *
> > > > > > from
> > > > > > druid.foo where dim in ('a', null) then why do we not convert
> this to
> > > > > > dim='a' OR dim=null.
> > > > > > Furthermore, this then can be simply to just dim='a' since
> dim=null is
> > > > > > always null.
> > > > > >
> > > > > > The plan I got after converting SqlNode to RelNode:
> > > > > > LogicalProject(__time=[$0], cnt=[$1], dim1=[$2], dim2=[$3],
> dim3=[$4],
> > > > > > m1=[$5], m2=[$6], unique_dim1=[$7])
> > > > > > LogicalJoin(condition=[=($3, $8)], joinType=[inner])
> > > > > > LogicalTableScan(table=[[druid, foo]])
> > > > > > LogicalAggregate(group=[{0}])
> > > > > > LogicalValues(tuples=[[{ 'a' }, { null }]])
> > > > > > however, i expect:
> > > > > > LogicalProject(__time=[$0], cnt=[$1], dim1=[$2], dim2=[$3],
> dim3=[$4],
> > > > > > m1=[$5], m2=[$6], unique_dim1=[$7])
> > > > > > LogicalFilter(condition=[OR(=($3, 'a'), =($3, null))])
> > > > > > LogicalTableScan(table=[[druid, foo]])
> > > > > >
> > > > > > or something like:
> > > > > > LogicalProject(__time=[$0], cnt=[$1], dim1=[$2], dim2=[$3],
> dim3=[$4],
> > > > > > m1=[$5], m2=[$6], unique_dim1=[$7])
> > > > > > LogicalFilter(condition=[=($3, 'a')])
> > > > > > LogicalTableScan(table=[[druid, foo]])
> > > > > >
> > > > > > Thanks!
> > > > > > -Maytas
> > > > > >
> > > > >
> > >
> > >
>
