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)