Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-4805

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

Rank to TopRank to BottomBulk Copy AttachmentsBulk Move AttachmentsVotersWatch issueWatchersConvert to sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    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]])

      Attachments

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            nobigo xiong duan
            nobigo xiong duan
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0h
                0h
                Logged:
                Time Spent - 0.5h
                0.5h

                Slack

                  Issue deployment