[jira] [Created] (CALCITE-2071) Query with IN operators in WHERE clause returns wrong result

["Volodymyr Vysotskyi (JIRA)" <ji...@apache.org>]

Volodymyr Vysotskyi created CALCITE-2071:
--------------------------------------------

             Summary: Query with IN operators in WHERE clause returns wrong result
                 Key: CALCITE-2071
                 URL: https://issues.apache.org/jira/browse/CALCITE-2071
             Project: Calcite
          Issue Type: Bug
            Reporter: Volodymyr Vysotskyi
            Assignee: Julian Hyde


Query it this test returns the wrong result:
{code:java}
  @Test
  @Ignore
  public void testWhereInOr() throws Exception {
    CalciteAssert.hr()
        .query("select \"empid\"\n"
            + "from \"hr\".\"emps\" t\n"
            + "    where (\"empid\" in (select \"empid\" from \"hr\".\"emps\") \n"
            + "        or \"empid\" in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, "
            + "                     12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25) )\n"
            + "      and \"empid\" in (100, 200, 150)")
        .returns("empid=100\n"
            + "empid=200\n"
            + "empid=150\n");
  }
{code}
Without condition in or
{code:sql}
\"empid\" in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25) 
{code}
it returns right result.

The problem appears during converting the query into the logical plan and may be observed more clearly using this test:
{code:java}
  @Test
  @Ignore
  public void testWhereInOrPlan() throws Exception {
    final String sql =
        "select \n" 
            + "from emp t\n" 
            + "    where (t.EMPNO in (select EMPNO from emp) \n" 
            + "        or t.EMPNO in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, " 
            + "12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25) )\n" 
            + "      and t.EMPNO in (1, 2, 3)";
    sql(sql).ok();
  }
{code}
This query returns plan 
{noformat}
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
  LogicalFilter(condition=[AND(OR(true, true), OR(=($0, 1), =($0, 2), =($0, 3)))])
    LogicalJoin(condition=[=($0, $10)], joinType=[inner])
      LogicalJoin(condition=[=($0, $9)], joinType=[inner])
        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
        LogicalAggregate(group=[{0}])
          LogicalProject(EMPNO=[$0])
            LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      LogicalAggregate(group=[{0}])
        LogicalValues(tuples=[[{ 1 }, { 2 }, { 3 }, { 4 }, { 5 }, { 6 }, { 7 }, { 8 }, { 9 }, { 10 }, { 11 }, { 12 }, { 13 }, { 14 }, { 15 }, { 16 }, { 17 }, { 18 }, { 19 }, { 20 }, { 21 }, { 22 }, { 23 }, { 24 }, { 25 }]])
{noformat}
where filter has incorrect condition: {{OR(true, true)}}.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)