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

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

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

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

      Attachments

        Issue Links

        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:
            6 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 - 2h 10m
                2h 10m

                Slack

                  Issue deployment