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

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.27.0
• Fix Version/s: 1.29.0
• Component/s: core
• Labels: pull-request-available
• Assignee: xiong duan
• Reporter: xiong duan
• Created: 09/Oct/21 22:48
• Updated: 02/Mar/22 02:02
• Resolved: 04/Jan/22 08:27

Description

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 

 'x in (a, b, c)' 

by expands to

'x = a or x = b or x = c'

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:

@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");
}

Issue Links

• is duplicated by: CALCITE-5026 NPE for IN-expression
• links to: GitHub Pull Request #2594