You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Volodymyr Vysotskyi (JIRA)" <ji...@apache.org> on 2017/11/29 21:03:01 UTC
[jira] [Updated] (CALCITE-2071) Query with IN operators in WHERE
clause returns wrong result
[ https://issues.apache.org/jira/browse/CALCITE-2071?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Volodymyr Vysotskyi updated CALCITE-2071:
-----------------------------------------
Description:
Query in 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}} operator
{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 the right result.
was:
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)}}.
> 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 in 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}} operator
> {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 the right result.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)