Repository: calcite Updated Branches: refs/heads/master 3a335bec7 -> 01be1ce69
[CALCITE-2071] Query with IN and OR in WHERE clause returns wrong result (Vineet Garg) Initial test case. (Volodymyr Vysotskyi) Add quidem tests; add a method to derive collations for EnumerableCalc, without which one of the added tests gets a failed assert when project is pushed through an EnumerableMergeJoin. (Julian Hyde) Close apache/calcite#575 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/01be1ce6 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/01be1ce6 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/01be1ce6 Branch: refs/heads/master Commit: 01be1ce69a5cafd8c60d528c28f1220554ee48c7 Parents: 3a335be Author: Vineet Garg <[email protected]> Authored: Mon Dec 11 16:09:00 2017 -0800 Committer: Julian Hyde <[email protected]> Committed: Wed Dec 13 17:13:44 2017 -0800 ---------------------------------------------------------------------- .../org/apache/calcite/plan/RelOptUtil.java | 2 +- .../calcite/rel/metadata/RelMdCollation.java | 6 ++ .../calcite/sql2rel/SqlToRelConverter.java | 12 ++++ .../java/org/apache/calcite/test/JdbcTest.java | 18 +++++ core/src/test/resources/sql/sub-query.iq | 72 ++++++++++++++++++++ 5 files changed, 109 insertions(+), 1 deletion(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/01be1ce6/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java index 5021569..e7e3b7a 100644 --- a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java +++ b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java @@ -546,7 +546,7 @@ public abstract class RelOptUtil { switch (logic) { case TRUE_FALSE_UNKNOWN: case UNKNOWN_AS_TRUE: - if (!containsNullableFields(seekRel)) { + if (notIn && !containsNullableFields(seekRel)) { logic = Logic.TRUE_FALSE; } } http://git-wip-us.apache.org/repos/asf/calcite/blob/01be1ce6/core/src/main/java/org/apache/calcite/rel/metadata/RelMdCollation.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdCollation.java b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdCollation.java index 32596ed..b48e763 100644 --- a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdCollation.java +++ b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdCollation.java @@ -26,6 +26,7 @@ import org.apache.calcite.rel.RelCollationTraitDef; import org.apache.calcite.rel.RelCollations; import org.apache.calcite.rel.RelFieldCollation; import org.apache.calcite.rel.RelNode; +import org.apache.calcite.rel.core.Calc; import org.apache.calcite.rel.core.Filter; import org.apache.calcite.rel.core.Join; import org.apache.calcite.rel.core.Project; @@ -149,6 +150,11 @@ public class RelMdCollation project(mq, project.getInput(), project.getProjects())); } + public ImmutableList<RelCollation> collations(Calc calc, + RelMetadataQuery mq) { + return ImmutableList.copyOf(calc(mq, calc.getInput(), calc.getProgram())); + } + public ImmutableList<RelCollation> collations(Values values, RelMetadataQuery mq) { return ImmutableList.copyOf( http://git-wip-us.apache.org/repos/asf/calcite/blob/01be1ce6/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java index e1b0d17..8326b02 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -1731,6 +1731,18 @@ public class SqlToRelConverter { break; } if (node instanceof SqlCall) { + switch (kind) { + // Do no change logic for AND, IN and NOT IN expressions; + // but do change logic for OR, NOT and others; + // EXISTS was handled already. + case AND: + case IN: + case NOT_IN: + break; + default: + logic = RelOptUtil.Logic.TRUE_FALSE_UNKNOWN; + break; + } for (SqlNode operand : ((SqlCall) node).getOperandList()) { if (operand != null) { // In the case of an IN expression, locate scalar http://git-wip-us.apache.org/repos/asf/calcite/blob/01be1ce6/core/src/test/java/org/apache/calcite/test/JdbcTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java index 2fd5d0a..e2effb8 100644 --- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java +++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java @@ -725,6 +725,24 @@ public class JdbcTest { assertTrue(connection.isClosed()); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2071";>[CALCITE-2071] + * Query with IN and OR in WHERE clause returns wrong result</a>. + * More cases in sub-query.iq. */ + @Test public void testWhereInOr() { + final String sql = "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,\n" + + " 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25))\n" + + "and \"empid\" in (100, 200, 150)"; + CalciteAssert.hr() + .query(sql) + .returnsUnordered("empid=100", + "empid=200", + "empid=150"); + } + /** Tests that a driver can be extended with its own parser and can execute * its own flavor of DDL. */ @Test public void testMockDdl() throws Exception { http://git-wip-us.apache.org/repos/asf/calcite/blob/01be1ce6/core/src/test/resources/sql/sub-query.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/sub-query.iq b/core/src/test/resources/sql/sub-query.iq index f7b5a58..2cc03dd 100644 --- a/core/src/test/resources/sql/sub-query.iq +++ b/core/src/test/resources/sql/sub-query.iq @@ -685,4 +685,76 @@ OR EXISTS (select * from "scott".emp e where emp.deptno = e.deptno + 20); !ok +# [CALCITE-2071] Query with IN and OR in WHERE clause returns wrong result +select empno +from "scott".emp +where (empno in (select empno from "scott".emp) + or 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)) +and empno in (7876, 7698, 7900); ++-------+ +| EMPNO | ++-------+ +| 7698 | +| 7876 | +| 7900 | ++-------+ +(3 rows) + +!ok + +# Equivalent to above (by de Morgan's law) +select empno +from "scott".emp +where not (empno not in (select empno from "scott".emp) + and empno not 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)) +and empno in (7876, 7698, 7900); ++-------+ +| EMPNO | ++-------+ +| 7698 | +| 7876 | +| 7900 | ++-------+ +(3 rows) + +!ok + +# Not equivalent to above, but happens to have same result +select empno +from "scott".emp +where (empno = 12345 + or empno in (select empno from "scott".emp) + or not 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)) +and empno in (7876, 7698, 7900); ++-------+ +| EMPNO | ++-------+ +| 7698 | +| 7876 | +| 7900 | ++-------+ +(3 rows) + +!ok + +# Similar to above, but never suffered from [CALCITE-2071] because AND +select empno +from "scott".emp +where (empno in (select empno from "scott".emp) + and empno in (7876, 7698, 7900)) +and empno in (7876, 7698, 7900); ++-------+ +| EMPNO | ++-------+ +| 7698 | +| 7876 | +| 7900 | ++-------+ +(3 rows) + +!ok + # End sub-query.iq
