[jira] [Closed] (CALCITE-4844) IN-list that references columns is wrongly converted to Values, and gives incorrect results

["Rui Wang (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-4844?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rui Wang closed CALCITE-4844.
-----------------------------

> IN-list that references columns is wrongly converted to Values, and gives incorrect results
> -------------------------------------------------------------------------------------------
>
>                 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
>             Fix For: 1.29.0
>
>          Time Spent: 2h 10m
>  Remaining Estimate: 0h
>
> 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 be converted to OR, no matter how large they are.
> 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.20.1#820001)