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)
