[CALCITE-2029] Query with IS DISTINCT FROM condition in WHERE or JOIN clause fails with AssertionError, "Cast for just nullability not allowed" (Volodymyr Vysotskyi)
Move fix from Filter constructor to IS DISTINCT FROM convertlet (Julian Hyde) Close apache/calcite#554 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/61f1258c Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/61f1258c Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/61f1258c Branch: refs/heads/master Commit: 61f1258c678d40d1041d89bdfec58b665a36fb6b Parents: 67bd544 Author: Volodymyr Vysotskyi <[email protected]> Authored: Thu Nov 2 14:08:39 2017 +0000 Committer: Julian Hyde <[email protected]> Committed: Thu Nov 2 15:01:51 2017 -0700 ---------------------------------------------------------------------- .../org/apache/calcite/plan/RelOptUtil.java | 12 +++++---- .../java/org/apache/calcite/rex/RexBuilder.java | 14 ++++++++++ .../java/org/apache/calcite/test/JdbcTest.java | 28 ++++++++++++++++++++ .../calcite/test/SqlToRelConverterTest.java | 8 ++++-- .../org/apache/calcite/test/RelOptRulesTest.xml | 2 +- .../calcite/test/SqlToRelConverterTest.xml | 24 ++++++++++++----- 6 files changed, 74 insertions(+), 14 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1258c/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 238cf3c..625fc73 100644 --- a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java +++ b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java @@ -1920,10 +1920,8 @@ public abstract class RelOptUtil { // The result of IS DISTINCT FROM is NOT NULL because it can // only return TRUE or FALSE. - ret = - rexBuilder.makeCast( - rexBuilder.getTypeFactory().createSqlType(SqlTypeName.BOOLEAN), - ret); + assert ret != null; + assert !ret.getType().isNullable(); return ret; } @@ -1942,6 +1940,8 @@ public abstract class RelOptUtil { nullOp = SqlStdOperatorTable.IS_NOT_NULL; eqOp = SqlStdOperatorTable.NOT_EQUALS; } + // By the time the ELSE is reached, x and y are known to be not null; + // therefore the whole CASE is not null. RexNode[] whenThenElse = { // when x is null rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, x), @@ -1956,7 +1956,9 @@ public abstract class RelOptUtil { rexBuilder.makeCall(nullOp, x), // else return x compared to y - rexBuilder.makeCall(eqOp, x, y) + rexBuilder.makeCall(eqOp, + rexBuilder.makeNotNull(x), + rexBuilder.makeNotNull(y)) }; return rexBuilder.makeCall( SqlStdOperatorTable.CASE, http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1258c/core/src/main/java/org/apache/calcite/rex/RexBuilder.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java index bd6579d..a04cbf0 100644 --- a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java +++ b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java @@ -748,6 +748,20 @@ public class RexBuilder { } /** + * Makes a cast of a value to NOT NULL; + * no-op if the type already has NOT NULL. + */ + public RexNode makeNotNull(RexNode exp) { + final RelDataType type = exp.getType(); + if (!type.isNullable()) { + return exp; + } + final RelDataType notNullType = + typeFactory.createTypeWithNullability(type, false); + return makeAbstractCast(notNullType, exp); + } + + /** * Creates a reference to all the fields in the row. That is, the whole row * as a single record object. * http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1258c/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 af69242..8760d82 100644 --- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java +++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java @@ -1589,6 +1589,34 @@ public class JdbcTest { + "full_name=Terry Anderson\n"); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2029";>[CALCITE-2029] + * Query with "is distinct from" condition in where or join clause fails + * with AssertionError: Cast for just nullability not allowed</a>. */ + @Test public void testIsNotDistinctInFilter() { + CalciteAssert.that() + .with(CalciteAssert.Config.JDBC_FOODMART) + .query("select *\n" + + " from \"foodmart\".\"employee\" as e1\n" + + " where e1.\"last_name\" is distinct from e1.\"last_name\"") + .runs(); + } + + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2029";>[CALCITE-2029] + * Query with "is distinct from" condition in where or join clause fails + * with AssertionError: Cast for just nullability not allowed</a>. */ + @Test public void testMixedEqualAndIsNotDistinctJoin() { + CalciteAssert.that() + .with(CalciteAssert.Config.JDBC_FOODMART) + .query("select *\n" + + " from \"foodmart\".\"employee\" as e1\n" + + " join \"foodmart\".\"employee\" as e2 on\n" + + " e1.\"first_name\" = e1.\"first_name\"\n" + + " and e1.\"last_name\" is distinct from e2.\"last_name\"") + .runs(); + } + /** A join that has both equi and non-equi conditions. * * <p>Test case for http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1258c/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java index f0276f4..aebcece 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -1442,12 +1442,16 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { } @Test public void testIsDistinctFrom() { - final String sql = "select 1 is distinct from 2 from (values(true))"; + final String sql = "select empno is distinct from deptno\n" + + "from (values (cast(null as int), 1),\n" + + " (2, cast(null as int))) as emp(empno, deptno)"; sql(sql).ok(); } @Test public void testIsNotDistinctFrom() { - final String sql = "select 1 is not distinct from 2 from (values(true))"; + final String sql = "select empno is not distinct from deptno\n" + + "from (values (cast(null as int), 1),\n" + + " (2, cast(null as int))) as emp(empno, deptno)"; sql(sql).ok(); } http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1258c/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml ---------------------------------------------------------------------- diff --git a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml index 596f730..cccedb0 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -2036,7 +2036,7 @@ LogicalCalc(expr#0=[{inputs}], expr#1=['TABLE '], expr#2=['t'], U=[$t1], </Resource> <Resource name="planBefore"> <![CDATA[ -LogicalProject(EXPR$0=[CAST(CASE(IS NULL($1), IS NULL($0), IS NULL($0), IS NULL($1), =($1, $0))):BOOLEAN NOT NULL]) +LogicalProject(EXPR$0=[CASE(IS NULL($1), IS NULL($0), =(CAST($1):INTEGER NOT NULL, $0))]) LogicalProject(EXPR$0=[2], EXPR$1=[null]) LogicalValues(tuples=[[{ 0 }]]) ]]> http://git-wip-us.apache.org/repos/asf/calcite/blob/61f1258c/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml ---------------------------------------------------------------------- diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml index dd944d1..6818e63 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -626,23 +626,35 @@ LogicalProject(DEPTNO=[$7]) <TestCase name="testIsDistinctFrom"> <Resource name="plan"> <![CDATA[ -LogicalProject(EXPR$0=[CAST(CASE(IS NULL(1), IS NOT NULL(2), IS NULL(2), IS NOT NULL(1), <>(1, 2))):BOOLEAN NOT NULL]) - LogicalValues(tuples=[[{ true }]]) +LogicalProject(EXPR$0=[CASE(IS NULL($0), IS NOT NULL($1), IS NULL($1), IS NOT NULL($0), <>(CAST($0):INTEGER NOT NULL, CAST($1):INTEGER NOT NULL))]) + LogicalUnion(all=[true]) + LogicalProject(EXPR$0=[null], EXPR$1=[1]) + LogicalValues(tuples=[[{ 0 }]]) + LogicalProject(EXPR$0=[2], EXPR$1=[null]) + LogicalValues(tuples=[[{ 0 }]]) ]]> </Resource> <Resource name="sql"> - <![CDATA[select 1 is distinct from 2 from (values(true))]]> + <![CDATA[select empno is distinct from deptno +from (values (cast(null as int), 1), + (2, cast(null as int))) as emp(empno, deptno)]]> </Resource> </TestCase> <TestCase name="testIsNotDistinctFrom"> <Resource name="plan"> <![CDATA[ -LogicalProject(EXPR$0=[CAST(CASE(IS NULL(1), IS NULL(2), IS NULL(2), IS NULL(1), =(1, 2))):BOOLEAN NOT NULL]) - LogicalValues(tuples=[[{ true }]]) +LogicalProject(EXPR$0=[CASE(IS NULL($0), IS NULL($1), IS NULL($1), IS NULL($0), =(CAST($0):INTEGER NOT NULL, CAST($1):INTEGER NOT NULL))]) + LogicalUnion(all=[true]) + LogicalProject(EXPR$0=[null], EXPR$1=[1]) + LogicalValues(tuples=[[{ 0 }]]) + LogicalProject(EXPR$0=[2], EXPR$1=[null]) + LogicalValues(tuples=[[{ 0 }]]) ]]> </Resource> <Resource name="sql"> - <![CDATA[select 1 is not distinct from 2 from (values(true))]]> + <![CDATA[select empno is not distinct from deptno +from (values (cast(null as int), 1), + (2, cast(null as int))) as emp(empno, deptno)]]> </Resource> </TestCase> <TestCase name="testNotLike">
