Repository: calcite Updated Branches: refs/heads/master 314b5b78b -> b60b67eb8
[CALCITE-2080] Query with NOT IN operator and literal throws AssertionError: 'Cast for just nullability not allowed' (Volodymyr Vysotskyi) Alternative fix (in SqlToRelConverter rather than RelOptUtil); fix query formatting in SqlToRelConverterTest. (Julian Hyde) Close apache/calcite#579 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/04cbdb2c Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/04cbdb2c Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/04cbdb2c Branch: refs/heads/master Commit: 04cbdb2cead5985322db737e1b0b8ce2259a7306 Parents: 314b5b7 Author: Volodymyr Vysotskyi <[email protected]> Authored: Thu Dec 7 18:26:38 2017 +0200 Committer: Julian Hyde <[email protected]> Committed: Mon Dec 11 02:11:11 2017 -0800 ---------------------------------------------------------------------- .../rel/rules/FilterProjectTransposeRule.java | 10 +- .../org/apache/calcite/rex/RexSimplify.java | 5 +- .../java/org/apache/calcite/rex/RexUtil.java | 11 ++ .../calcite/sql2rel/SqlToRelConverter.java | 6 +- .../calcite/test/SqlToRelConverterTest.java | 96 +++++++++--------- .../calcite/test/SqlToRelConverterTest.xml | 101 ++++++++++++------- 6 files changed, 134 insertions(+), 95 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/04cbdb2c/core/src/main/java/org/apache/calcite/rel/rules/FilterProjectTransposeRule.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rules/FilterProjectTransposeRule.java b/core/src/main/java/org/apache/calcite/rel/rules/FilterProjectTransposeRule.java index aaec8a8..3bdb5f5 100644 --- a/core/src/main/java/org/apache/calcite/rel/rules/FilterProjectTransposeRule.java +++ b/core/src/main/java/org/apache/calcite/rel/rules/FilterProjectTransposeRule.java @@ -16,7 +16,6 @@ */ package org.apache.calcite.rel.rules; -import org.apache.calcite.plan.RelOptPredicateList; import org.apache.calcite.plan.RelOptRule; import org.apache.calcite.plan.RelOptRuleCall; import org.apache.calcite.plan.RelOptRuleOperand; @@ -27,7 +26,6 @@ import org.apache.calcite.rel.core.Project; import org.apache.calcite.rel.core.RelFactories; import org.apache.calcite.rex.RexNode; import org.apache.calcite.rex.RexOver; -import org.apache.calcite.rex.RexSimplify; import org.apache.calcite.rex.RexUtil; import org.apache.calcite.tools.RelBuilder; import org.apache.calcite.tools.RelBuilderFactory; @@ -120,13 +118,9 @@ public class FilterProjectTransposeRule extends RelOptRule { final RelBuilder relBuilder = call.builder(); RelNode newFilterRel; if (copyFilter) { - final RelOptPredicateList predicates = RelOptPredicateList.EMPTY; - final RexSimplify simplify = - new RexSimplify(relBuilder.getRexBuilder(), predicates, false, - RexUtil.EXECUTOR); - newCondition = simplify.removeNullabilityCast(newCondition); newFilterRel = filter.copy(filter.getTraitSet(), project.getInput(), - newCondition); + RexUtil.removeNullabilityCast(relBuilder.getTypeFactory(), + newCondition)); } else { newFilterRel = relBuilder.push(project.getInput()).filter(newCondition).build(); http://git-wip-us.apache.org/repos/asf/calcite/blob/04cbdb2c/core/src/main/java/org/apache/calcite/rex/RexSimplify.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java index cd42c81..34e3814 100644 --- a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java +++ b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java @@ -953,10 +953,7 @@ public class RexSimplify { * * <p>For example, {@code CAST(1 = 0 AS BOOLEAN)} becomes {@code 1 = 0}. */ public RexNode removeNullabilityCast(RexNode e) { - while (RexUtil.isNullabilityCast(rexBuilder.getTypeFactory(), e)) { - e = ((RexCall) e).operands.get(0); - } - return e; + return RexUtil.removeNullabilityCast(rexBuilder.getTypeFactory(), e); } private static <C extends Comparable<C>> RexNode processRange( http://git-wip-us.apache.org/repos/asf/calcite/blob/04cbdb2c/core/src/main/java/org/apache/calcite/rex/RexUtil.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rex/RexUtil.java b/core/src/main/java/org/apache/calcite/rex/RexUtil.java index 1dd0396..6f467be 100644 --- a/core/src/main/java/org/apache/calcite/rex/RexUtil.java +++ b/core/src/main/java/org/apache/calcite/rex/RexUtil.java @@ -310,6 +310,17 @@ public class RexUtil { return false; } + /** Removes any casts that change nullability but not type. + * + * <p>For example, {@code CAST(1 = 0 AS BOOLEAN)} becomes {@code 1 = 0}. */ + public static RexNode removeNullabilityCast(RelDataTypeFactory typeFactory, + RexNode node) { + while (isNullabilityCast(typeFactory, node)) { + node = ((RexCall) node).operands.get(0); + } + return node; + } + /** Creates a map containing each (e, constant) pair that occurs within * a predicate list. * http://git-wip-us.apache.org/repos/asf/calcite/blob/04cbdb2c/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 ede2daf..e1b0d17 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -963,15 +963,17 @@ public class SqlToRelConverter { SqlNode newWhere = pushDownNotForIn(bb.scope, where); replaceSubQueries(bb, newWhere, RelOptUtil.Logic.UNKNOWN_AS_FALSE); final RexNode convertedWhere = bb.convertExpression(newWhere); + final RexNode convertedWhere2 = + RexUtil.removeNullabilityCast(typeFactory, convertedWhere); // only allocate filter if the condition is not TRUE - if (convertedWhere.isAlwaysTrue()) { + if (convertedWhere2.isAlwaysTrue()) { return; } final RelFactories.FilterFactory factory = RelFactories.DEFAULT_FILTER_FACTORY; - final RelNode filter = factory.createFilter(bb.root, convertedWhere); + final RelNode filter = factory.createFilter(bb.root, convertedWhere2); final RelNode r; final CorrelationUse p = getCorrelationUse(bb, filter); if (p != null) { http://git-wip-us.apache.org/repos/asf/calcite/blob/04cbdb2c/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 de0b268..6c3651a 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -2371,6 +2371,19 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { sql(sql).with(getTesterWithDynamicTable()).ok(); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2080";>[CALCITE-2080] + * Query with NOT IN operator and literal fails throws AssertionError: 'Cast + * for just nullability not allowed'</a>. */ + @Test public void testNotInWithLiteral() { + final String sql = "SELECT *\n" + + "FROM SALES.NATION\n" + + "WHERE n_name NOT IN\n" + + " (SELECT ''\n" + + " FROM SALES.NATION)"; + sql(sql).with(getTesterWithDynamicTable()).ok(); + } + /** * Test case for Dynamic Table / Dynamic Star support * <a href="https://issues.apache.org/jira/browse/CALCITE-1150";>[CALCITE-1150]</a> @@ -2570,27 +2583,24 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { + " pattern (strt down+ up+)\n" + " define\n" + " down as down.mgr < PREV(down.mgr),\n" - + " up as up.mgr > prev(up.mgr)\n" - + " ) mr"; + + " up as up.mgr > prev(up.mgr)) as mr"; sql(sql).ok(); } @Test public void testMatchRecognizeMeasures1() { final String sql = "select *\n" - + " from emp match_recognize\n" - + " (\n" - + " partition by job, sal\n" - + " order by job asc, sal desc\n" - + " measures MATCH_NUMBER() as match_num, " - + " CLASSIFIER() as var_match, " - + " STRT.mgr as start_nw," - + " LAST(DOWN.mgr) as bottom_nw," - + " LAST(up.mgr) as end_nw" - + " pattern (strt down+ up+)\n" - + " define\n" - + " down as down.mgr < PREV(down.mgr),\n" - + " up as up.mgr > prev(up.mgr)\n" - + " ) mr"; + + "from emp match_recognize (\n" + + " partition by job, sal\n" + + " order by job asc, sal desc\n" + + " measures MATCH_NUMBER() as match_num,\n" + + " CLASSIFIER() as var_match,\n" + + " STRT.mgr as start_nw,\n" + + " LAST(DOWN.mgr) as bottom_nw,\n" + + " LAST(up.mgr) as end_nw\n" + + " pattern (strt down+ up+)\n" + + " define\n" + + " down as down.mgr < PREV(down.mgr),\n" + + " up as up.mgr > prev(up.mgr)) as mr"; sql(sql).ok(); } @@ -2600,40 +2610,36 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { * columns</a>. */ @Test public void testMatchRecognizeMeasures2() { final String sql = "select *\n" - + " from emp match_recognize\n" - + " (\n" - + " partition by job\n" - + " order by sal\n" - + " measures MATCH_NUMBER() as match_num, " - + " CLASSIFIER() as var_match, " - + " STRT.mgr as start_nw," - + " LAST(DOWN.mgr) as bottom_nw," - + " LAST(up.mgr) as end_nw" - + " pattern (strt down+ up+)\n" - + " define\n" - + " down as down.mgr < PREV(down.mgr),\n" - + " up as up.mgr > prev(up.mgr)\n" - + " ) mr"; + + "from emp match_recognize (\n" + + " partition by job\n" + + " order by sal\n" + + " measures MATCH_NUMBER() as match_num,\n" + + " CLASSIFIER() as var_match,\n" + + " STRT.mgr as start_nw,\n" + + " LAST(DOWN.mgr) as bottom_nw,\n" + + " LAST(up.mgr) as end_nw\n" + + " pattern (strt down+ up+)\n" + + " define\n" + + " down as down.mgr < PREV(down.mgr),\n" + + " up as up.mgr > prev(up.mgr)) as mr"; sql(sql).ok(); } @Test public void testMatchRecognizeMeasures3() { final String sql = "select *\n" - + " from emp match_recognize\n" - + " (\n" - + " partition by job\n" - + " order by sal\n" - + " measures MATCH_NUMBER() as match_num, " - + " CLASSIFIER() as var_match, " - + " STRT.mgr as start_nw," - + " LAST(DOWN.mgr) as bottom_nw," - + " LAST(up.mgr) as end_nw" - + " ALL ROWS PER MATCH" - + " pattern (strt down+ up+)\n" - + " define\n" - + " down as down.mgr < PREV(down.mgr),\n" - + " up as up.mgr > prev(up.mgr)\n" - + " ) mr"; + + "from emp match_recognize (\n" + + " partition by job\n" + + " order by sal\n" + + " measures MATCH_NUMBER() as match_num,\n" + + " CLASSIFIER() as var_match,\n" + + " STRT.mgr as start_nw,\n" + + " LAST(DOWN.mgr) as bottom_nw,\n" + + " LAST(up.mgr) as end_nw\n" + + " ALL ROWS PER MATCH\n" + + " pattern (strt down+ up+)\n" + + " define\n" + + " down as down.mgr < PREV(down.mgr),\n" + + " up as up.mgr > prev(up.mgr)) as mr"; sql(sql).ok(); } http://git-wip-us.apache.org/repos/asf/calcite/blob/04cbdb2c/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 c7d2b24..de4a0c1 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -361,7 +361,7 @@ window w as (partition by productId)]]> </TestCase> <TestCase name="testArrayOfRecord"> <Resource name="sql"> - <![CDATA[select*from unnest(array(select*from dept))]]> + <![CDATA[select employees[1].skills[2+3].desc from dept_nested]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -648,7 +648,7 @@ LogicalProject(EXPR$0=[CASE(IS NULL($0), IS NOT NULL($1), IS NULL($1), IS NOT NU <Resource name="sql"> <![CDATA[select empno is distinct from deptno from (values (cast(null as int), 1), - (2, cast(null as int))) as emp(empno, deptno)]]> + (2, cast(null as int))) as emp(empno, deptno)]]> </Resource> </TestCase> <TestCase name="testIsNotDistinctFrom"> @@ -1511,7 +1511,7 @@ LogicalSort(offset=[10], fetch=[5]) ]]> </Resource> </TestCase> -<TestCase name="testOffsetFetchWithDynamicParameter"> + <TestCase name="testOffsetFetchWithDynamicParameter"> <Resource name="sql"> <![CDATA[select empno from emp offset ? rows fetch next ? rows only]]> @@ -2444,8 +2444,7 @@ LogicalProject(DEPTNO=[$1], EXPR$1=[$2], EXPR$2=[$3], EXPR$3=[$4]) pattern (strt down+ up+) define down as down.mgr < PREV(down.mgr), - up as up.mgr > prev(up.mgr) - ) mr]]> + up as up.mgr > prev(up.mgr)) as mr]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -2458,15 +2457,18 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ <TestCase name="testMatchRecognizeMeasures1"> <Resource name="sql"> <![CDATA[select * - from emp match_recognize - ( - partition by job, sal - order by job asc, sal desc - measures STRT.mgr as start_nw, LAST(DOWN.mgr) as bottom_nw, LAST(up.mgr) as end_nw pattern (strt down+ up+) - define - down as down.mgr < PREV(down.mgr), - up as up.mgr > prev(up.mgr) - ) mr]]> +from emp match_recognize ( + partition by job, sal + order by job asc, sal desc + measures MATCH_NUMBER() as match_num, + CLASSIFIER() as var_match, + STRT.mgr as start_nw, + LAST(DOWN.mgr) as bottom_nw, + LAST(up.mgr) as end_nw + pattern (strt down+ up+) + define + down as down.mgr < PREV(down.mgr), + up as up.mgr > prev(up.mgr)) as mr]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -2479,15 +2481,18 @@ LogicalProject(JOB=[$0], SAL=[$1], MATCH_NUM=[$2], VAR_MATCH=[$3], START_NW=[$4] <TestCase name="testMatchRecognizeMeasures2"> <Resource name="sql"> <![CDATA[select * - from emp match_recognize - ( - partition by job - order by sal - measures STRT.mgr as start_nw, LAST(DOWN.mgr) as bottom_nw, LAST(up.mgr) as end_nw pattern (strt down+ up+) - define - down as down.mgr < PREV(down.mgr), - up as up.mgr > prev(up.mgr) - ) mr]]> +from emp match_recognize ( + partition by job + order by sal + measures MATCH_NUMBER() as match_num, + CLASSIFIER() as var_match, + STRT.mgr as start_nw, + LAST(DOWN.mgr) as bottom_nw, + LAST(up.mgr) as end_nw + pattern (strt down+ up+) + define + down as down.mgr < PREV(down.mgr), + up as up.mgr > prev(up.mgr)) as mr]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -2500,17 +2505,19 @@ LogicalProject(JOB=[$0], MATCH_NUM=[$1], VAR_MATCH=[$2], START_NW=[$3], BOTTOM_N <TestCase name="testMatchRecognizeMeasures3"> <Resource name="sql"> <![CDATA[select * - from emp match_recognize - ( - partition by job - order by sal - measures STRT.mgr as start_nw, LAST(DOWN.mgr) as bottom_nw, LAST(up.mgr) as end_nw - ALL ROWS PER MATCH - pattern (strt down+ up+) - define - down as down.mgr < PREV(down.mgr), - up as up.mgr > prev(up.mgr) - ) mr]]> +from emp match_recognize ( + partition by job + order by sal + measures MATCH_NUMBER() as match_num, + CLASSIFIER() as var_match, + STRT.mgr as start_nw, + LAST(DOWN.mgr) as bottom_nw, + LAST(up.mgr) as end_nw + ALL ROWS PER MATCH + pattern (strt down+ up+) + define + down as down.mgr < PREV(down.mgr), + up as up.mgr > prev(up.mgr)) as mr]]> </Resource> <Resource name="plan"> <![CDATA[ @@ -2543,7 +2550,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ <TestCase name="testMatchRecognizePrevDown"> <Resource name="sql"> <![CDATA[SELECT * -FROM tmp +FROM emp MATCH_RECOGNIZE ( MEASURES STRT.mgr AS start_mgr, @@ -2611,7 +2618,7 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ <TestCase name="testPrevClassifier"> <Resource name="sql"> <![CDATA[SELECT * -FROM tmp +FROM emp MATCH_RECOGNIZE ( MEASURES STRT.mgr AS start_mgr, @@ -2916,6 +2923,28 @@ LogicalDelta ]]> </Resource> </TestCase> + <TestCase name="testNotInWithLiteral"> + <Resource name="sql"> + <![CDATA[SELECT * +FROM SALES.NATION +WHERE n_name NOT IN + (SELECT '' + FROM SALES.NATION)]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalProject(**=[$1]) + LogicalFilter(condition=[NOT(AND(IS TRUE($4), IS NOT NULL($2)))]) + LogicalJoin(condition=[=($2, $3)], joinType=[left]) + LogicalProject($f0=[$0], $f1=[$1], $f2=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, NATION]]) + LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) + LogicalProject($f0=[$0], $f1=[true]) + LogicalProject(EXPR$0=['']) + LogicalTableScan(table=[[CATALOG, SALES, NATION]]) +]]> + </Resource> + </TestCase> <TestCase name="testSessionTable"> <Resource name="sql"> <