[
https://issues.apache.org/jira/browse/CALCITE-4844?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17430818#comment-17430818
]
Julian Hyde commented on CALCITE-4844:
--------------------------------------
[~nobigo], The only thing wrong with this bug is the confusing title. If you
fix the title I think we can merge it.
I think "IN-list that references columns is wrongly converted to Values, and
gives incorrect results" is a better title. The first line of the description
can say "This problem only occurs in large IN-lists, because small IN-lists -
those smaller than SqlToRelConverter.Config.InSubQueryThreshold - are always
converted to OR, and converting to OR always gives correct results. For correct
results, IN-lists that reference column values must never be converted to OR,
no matter how large they are."
> when the value of sub-query threshold smaller than IN list size the In list
> include columns return wrong result.
> ----------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-4844
> URL: https://issues.apache.org/jira/browse/CALCITE-4844
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.27.0
> Reporter: duan xiong
> Assignee: duan xiong
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> According CALCITE-1155[Support columns for IN list]. We support
> {code:java}
> 'x in (a, b, c)' {code}
> by expands to
> {code:java}
> 'x = a or x = b or x = c'{code}
> In Calcite. Only the list size smaller than the value of the sub-query
> threshold can convert. We should ignore the the value of the sub-query
> threshold to convert this.
>
> This issue can reproduce by add a unit test in SqlToRelConverterTest:
> {code:java}
> @Test void testInListIncludeColumns() {
> final String sql = "SELECT empno\n"
> + "FROM emp AS e\n"
> + "WHERE 130 in (empno, deptno)";
> sql(sql).withConfig(b -> b.withInSubQueryThreshold(3))
> .convertsTo(
> "\n" +
> "LogicalProject(EMPNO=[$0])\n" +
> " LogicalFilter(condition=[OR(=(130, $0), =(130, $7))])\n" +
> " LogicalTableScan(table=[[CATALOG, SALES, EMP]])\n");
> sql(sql).withConfig(b -> b.withInSubQueryThreshold(1))
> .throws_("java.lang.NullPointerException: scope");
> }{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
