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

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

    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

            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