[CALCITE-4805] Calcite should convert a small IN-list as if the user had written OR, even if the IN-list contains NULL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.28.0
• Component/s: core
• Labels: pull-request-available
• Assignee: xiong duan
• Reporter: xiong duan
• Created: 26/Sep/21 05:45
• Updated: 20/Oct/21 05:24
• Resolved: 30/Sep/21 19:25

Description

Since CALCITE-373 Calcite has converted "x IN (1, 2)" to "x = 1 OR x = 2" but it still converts "x IN (1, NULL)" to "x IN (VALUES 1, NULL)" and that, since some bugs have been fixed, there's no good reason for the difference.

For example:

select * from emp where deptno in (null,2)

Now the RelNode is:

LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
 LogicalJoin(condition=[=($7, $9)], joinType=[inner])
 LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 LogicalAggregate(group=[{0}])
 LogicalValues(tuples=[[{ null }, { 2 }]])

The RelNode should be:

LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
 LogicalFilter(condition=[OR(=($7, null), =($7, 2))])
 LogicalTableScan(table=[[CATALOG, SALES, EMP]])

Issue Links

• links to: GitHub Pull Request #2545