You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2014/08/28 03:42:49 UTC
[1/4] [OPTIQ-373] NULL values in NOT IN sub-queries
Repository: incubator-optiq
Updated Branches:
refs/heads/master 582be2a29 -> 793e5c4d2
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/test/resources/org/eigenbase/test/SqlToRelConverterTest.xml
----------------------------------------------------------------------
diff --git a/core/src/test/resources/org/eigenbase/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/eigenbase/test/SqlToRelConverterTest.xml
index 1e4d24b..a506886 100644
--- a/core/src/test/resources/org/eigenbase/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/eigenbase/test/SqlToRelConverterTest.xml
@@ -1860,4 +1860,94 @@ ProjectRel(EMPNO=[$0], X=[$1])
]]>
</Resource>
</TestCase>
+ <TestCase name="testInUncorrelatedSubqueryInSelect">
+ <Resource name="sql">
+ <![CDATA[select name, deptno in (
+ select case when true then deptno else null end from emp)
+from dept]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+ProjectRel(NAME=[$1], EXPR$1=[CASE(=($2, 0), false, IS NOT NULL($6), true, IS NULL($4), null, <($3, $2), null, false)])
+ JoinRel(condition=[=($4, $5)], joinType=[left])
+ ProjectRel($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$0])
+ JoinRel(condition=[true], joinType=[inner])
+ TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+ AggregateRel(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
+ ProjectRel($f0=[$0], $f1=[true])
+ ProjectRel(EXPR$0=[CASE(true, CAST($7):INTEGER, null)])
+ TableAccessRel(table=[[CATALOG, SALES, EMP]])
+ AggregateRel(group=[{0}], agg#0=[MIN($1)])
+ ProjectRel($f0=[$0], $f1=[true])
+ ProjectRel(EXPR$0=[CASE(true, CAST($7):INTEGER, null)])
+ TableAccessRel(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNotInUncorrelatedSubqueryInSelect">
+ <Resource name="sql">
+ <![CDATA[select empno, deptno not in (
+ select case when true then deptno else null end from dept)
+from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+ProjectRel(EMPNO=[$0], EXPR$1=[NOT(CASE(=($9, 0), false, IS NOT NULL($13), true, IS NULL($11), null, <($10, $9), null, false))])
+ JoinRel(condition=[=($11, $12)], joinType=[left])
+ ProjectRel($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5], $f6=[$6], $f7=[$7], $f8=[$8], $f9=[$9], $f10=[$10], $f11=[$7])
+ JoinRel(condition=[true], joinType=[inner])
+ TableAccessRel(table=[[CATALOG, SALES, EMP]])
+ AggregateRel(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
+ ProjectRel($f0=[$0], $f1=[true])
+ ProjectRel(EXPR$0=[CASE(true, CAST($0):INTEGER, null)])
+ TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+ AggregateRel(group=[{0}], agg#0=[MIN($1)])
+ ProjectRel($f0=[$0], $f1=[true])
+ ProjectRel(EXPR$0=[CASE(true, CAST($0):INTEGER, null)])
+ TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNotInUncorrelatedSubquery">
+ <Resource name="sql">
+ <![CDATA[select empno from emp where deptno not in (select deptno from dept)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+ProjectRel(EMPNO=[$0])
+ FilterRel(condition=[NOT(CASE(=($9, 0), false, IS NOT NULL($13), true, IS NULL($11), null, <($10, $9), null, false))])
+ JoinRel(condition=[=($11, $12)], joinType=[left])
+ ProjectRel($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5], $f6=[$6], $f7=[$7], $f8=[$8], $f9=[$9], $f10=[$10], $f11=[$7])
+ JoinRel(condition=[true], joinType=[inner])
+ TableAccessRel(table=[[CATALOG, SALES, EMP]])
+ AggregateRel(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
+ ProjectRel($f0=[$0], $f1=[true])
+ ProjectRel(DEPTNO=[$0])
+ TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+ AggregateRel(group=[{0}], agg#0=[MIN($1)])
+ ProjectRel($f0=[$0], $f1=[true])
+ ProjectRel(DEPTNO=[$0])
+ TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNotInUncorrelatedSubqueryInSelectNotNull">
+ <Resource name="sql">
+ <![CDATA[select empno, deptno not in (
+ select deptno from dept)
+from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+ProjectRel(EMPNO=[$0], EXPR$1=[NOT(true)])
+ JoinRel(condition=[=($9, $10)], joinType=[left])
+ ProjectRel($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5], $f6=[$6], $f7=[$7], $f8=[$8], $f9=[$7])
+ TableAccessRel(table=[[CATALOG, SALES, EMP]])
+ AggregateRel(group=[{0}], agg#0=[MIN($1)])
+ ProjectRel($f0=[$0], $f1=[true])
+ ProjectRel(DEPTNO=[$0])
+ TableAccessRel(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
</Root>
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/test/resources/sql/misc.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/misc.oq b/core/src/test/resources/sql/misc.oq
index 91d63fc..fb635a5 100644
--- a/core/src/test/resources/sql/misc.oq
+++ b/core/src/test/resources/sql/misc.oq
@@ -477,53 +477,4 @@ having exists
!ok
-# [OPTIQ-373]
-# the following should return no rows, because the in list has a null.
-# for details on this: see HIVE-784, Dayal's paper from VLDB-87
-!if (false) {
-with
-t1(x) as (select * from (values 1,2, case when 1 = 1 then null else 3 end)),
-t2(x) as (select * from (values 1,case when 1 = 1 then null else 3 end))
-select *
-from t1
-where t1.x not in (select t2.x from t2);
-
-EnumerableCalcRel(expr#0..3=[{inputs}], expr#4=[IS TRUE($t3)], expr#5=[NOT($t4)], expr#6=[IS NOT NULL($t1)], expr#7=[AND($t5, $t6)], X=[$t0], $condition=[$t7])
- EnumerableJoinRel(condition=[=($1, $2)], joinType=[left])
- EnumerableCalcRel(expr#0=[{inputs}], $f0=[$t0], $f1=[$t0])
- EnumerableUnionRel(all=[true])
- EnumerableCalcRel(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1])
- EnumerableValuesRel(tuples=[[{ 0 }]])
- EnumerableCalcRel(expr#0=[{inputs}], expr#1=[2], EXPR$0=[$t1])
- EnumerableValuesRel(tuples=[[{ 0 }]])
- EnumerableCalcRel(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t1, $t1)], expr#3=[null], expr#4=[3], expr#5=[CASE($t2, $t3, $t4)], EXPR$0=[$t5])
- EnumerableValuesRel(tuples=[[{ 0 }]])
- EnumerableAggregateRel(group=[{0}], agg#0=[MIN($1)])
- EnumerableCalcRel(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
- EnumerableUnionRel(all=[true])
- EnumerableCalcRel(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1])
- EnumerableValuesRel(tuples=[[{ 0 }]])
- EnumerableCalcRel(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t1, $t1)], expr#3=[null], expr#4=[3], expr#5=[CASE($t2, $t3, $t4)], EXPR$0=[$t5])
- EnumerableValuesRel(tuples=[[{ 0 }]])
-
-!plan
-!}
-
-# use of case is to get around issue with directly specifying null in values list
-with
-t1(x) as (select * from (values 1,2, case when 1 = 1 then null else 3 end)),
-t2(x) as (select * from (values 1,case when 1 = 1 then null else 3 end))
-select *
-from t1
-where t1.x not in (select t2.x from t2);
-
-+---+
-| X |
-+---+
-| 2 |
-+---+
-(1 row)
-
-!ok
-
# End misc.oq
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/test/resources/sql/outer.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/outer.oq b/core/src/test/resources/sql/outer.oq
index 7936692..e371fe9 100644
--- a/core/src/test/resources/sql/outer.oq
+++ b/core/src/test/resources/sql/outer.oq
@@ -177,6 +177,21 @@ select * from (select * from emp where gender ='F') as emp right join dept on em
!if (false) {
select * from emp full join dept on emp.deptno = dept.deptno and emp.gender = 'F';
+ ename | deptno | gender | deptno | dname
+-------+--------+--------+--------+-------------
+ Jane | 10 | F | 10 | Sales
+ | | | 20 | Marketing
+ Alice | 30 | F | 30 | Engineering
+ Susan | 30 | F | 30 | Engineering
+ | | | 40 | Empty
+ Wilma | | F | |
+ Eric | 20 | M | |
+ Bob | 10 | M | |
+ Eve | 50 | F | |
+ Adam | 50 | M | |
+ Grace | 60 | F | |
+(11 rows)
+
!ok
!}
@@ -213,4 +228,6 @@ select * from (select * from emp where gender ='F') as emp full join dept on emp
!ok
+
+
# End outer.oq
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/test/resources/sql/subquery.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/subquery.oq b/core/src/test/resources/sql/subquery.oq
new file mode 100644
index 0000000..afd5483
--- /dev/null
+++ b/core/src/test/resources/sql/subquery.oq
@@ -0,0 +1,234 @@
+# subquery.oq - Queries involving IN and EXISTS sub-queries
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to you under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+!use post
+!set outputformat psql
+
+# [OPTIQ-373]
+# the following should return no rows, because the IN list has a null.
+# for details on this: see HIVE-784, Dayal's paper from VLDB-87
+with
+t1(x) as (select * from (values 1,2, case when 1 = 1 then null else 3 end)),
+t2(x) as (select * from (values 1,case when 1 = 1 then null else 3 end))
+select *
+from t1
+where t1.x not in (select t2.x from t2);
+ X
+---
+(0 rows)
+
+!ok
+EnumerableCalcRel(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t1, $t6)], expr#8=[false], expr#9=[IS NOT NULL($t5)], expr#10=[true], expr#11=[IS NULL($t3)], expr#12=[null], expr#13=[<($t2, $t1)], expr#14=[CASE($t7, $t8, $t9, $t10, $t11, $t12, $t13, $t12, $t8)], expr#15=[NOT($t14)], X=[$t0], $condition=[$t15])
+ EnumerableJoinRel(condition=[=($3, $4)], joinType=[left])
+ EnumerableCalcRel(expr#0..2=[{inputs}], $f0=[$t2], $f1=[$t0], $f2=[$t1], $f3=[$t2])
+ EnumerableJoinRel(condition=[true], joinType=[inner])
+ EnumerableAggregateRel(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
+ EnumerableCalcRel(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
+ EnumerableUnionRel(all=[true])
+ EnumerableCalcRel(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1])
+ EnumerableValuesRel(tuples=[[{ 0 }]])
+ EnumerableCalcRel(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t1, $t1)], expr#3=[null], expr#4=[3], expr#5=[CASE($t2, $t3, $t4)], EXPR$0=[$t5])
+ EnumerableValuesRel(tuples=[[{ 0 }]])
+ EnumerableUnionRel(all=[true])
+ EnumerableCalcRel(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1])
+ EnumerableValuesRel(tuples=[[{ 0 }]])
+ EnumerableCalcRel(expr#0=[{inputs}], expr#1=[2], EXPR$0=[$t1])
+ EnumerableValuesRel(tuples=[[{ 0 }]])
+ EnumerableCalcRel(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t1, $t1)], expr#3=[null], expr#4=[3], expr#5=[CASE($t2, $t3, $t4)], EXPR$0=[$t5])
+ EnumerableValuesRel(tuples=[[{ 0 }]])
+ EnumerableAggregateRel(group=[{0}], agg#0=[MIN($1)])
+ EnumerableCalcRel(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
+ EnumerableUnionRel(all=[true])
+ EnumerableCalcRel(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1])
+ EnumerableValuesRel(tuples=[[{ 0 }]])
+ EnumerableCalcRel(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t1, $t1)], expr#3=[null], expr#4=[3], expr#5=[CASE($t2, $t3, $t4)], EXPR$0=[$t5])
+ EnumerableValuesRel(tuples=[[{ 0 }]])
+!plan
+
+# Use of case is to get around issue with directly specifying null in values
+# list. Postgres gives 0 rows.
+with
+t1(x) as (select * from (values (1),(2),(case when 1 = 1 then null else 3 end)) as t1),
+t2(x) as (select * from (values (1),(case when 1 = 1 then null else 3 end)) as t2)
+select *
+from t1
+where t1.x not in (select t2.x from t2);
+
+ X
+---
+(0 rows)
+
+!ok
+
+# RHS has a mixture of NULL and NOT NULL keys
+select * from dept where deptno not in (select deptno from emp);
+ DEPTNO | DNAME
+--------+-------
+(0 rows)
+
+!ok
+select deptno, deptno in (select deptno from emp) from dept;
+ DEPTNO | EXPR$1
+--------+--------
+ 10 | true
+ 20 | true
+ 30 | true
+ 40 | null
+(4 rows)
+
+!ok
+select deptno, deptno not in (select deptno from emp) from dept;
+ DEPTNO | EXPR$1
+--------+--------
+ 10 | false
+ 20 | false
+ 30 | false
+ 40 | null
+(4 rows)
+
+!ok
+
+# RHS has only NULL keys
+select * from dept where deptno not in (select deptno from emp where deptno is null);
+ DEPTNO | DNAME
+--------+-------
+(0 rows)
+
+!ok
+select deptno, deptno in (select deptno from emp where deptno is null) from dept;
+ DEPTNO | EXPR$1
+--------+--------
+ 10 | null
+ 20 | null
+ 30 | null
+ 40 | null
+(4 rows)
+
+!ok
+select deptno, deptno not in (select deptno from emp where deptno is null) from dept;
+ DEPTNO | EXPR$1
+--------+--------
+ 10 | null
+ 20 | null
+ 30 | null
+ 40 | null
+(4 rows)
+
+!ok
+
+# RHS has only NOT NULL keys
+select * from dept where deptno not in (select deptno from emp where deptno is not null);
+ DEPTNO | DNAME
+--------+-------------
+ 40 | Empty
+(1 row)
+
+!ok
+select deptno, deptno in (select deptno from emp where deptno is not null) from dept;
+ DEPTNO | EXPR$1
+--------+--------
+ 10 | true
+ 20 | true
+ 30 | true
+ 40 | false
+(4 rows)
+
+!ok
+select deptno, deptno not in (select deptno from emp where deptno is not null) from dept;
+ DEPTNO | EXPR$1
+--------+--------
+ 10 | false
+ 20 | false
+ 30 | false
+ 40 | true
+(4 rows)
+
+!ok
+
+# RHS has no rows
+# Even 'NULL NOT IN ...' is TRUE.
+select * from dept where deptno not in (select deptno from emp where false);
+ DEPTNO | DNAME
+--------+-------------
+ 10 | Sales
+ 20 | Marketing
+ 30 | Engineering
+ 40 | Empty
+(4 rows)
+
+!ok
+select deptno, deptno in (select deptno from emp where false) from dept;
+ DEPTNO | EXPR$1
+--------+--------
+ 10 | false
+ 20 | false
+ 30 | false
+ 40 | false
+(4 rows)
+
+!ok
+select deptno, deptno not in (select deptno from emp where false) from dept;
+ DEPTNO | EXPR$1
+--------+--------
+ 10 | true
+ 20 | true
+ 30 | true
+ 40 | true
+(4 rows)
+
+!ok
+
+# Multiple IN, connected by OR
+select * from dept
+where deptno in (select deptno from emp where gender = 'F')
+or deptno in (select deptno from emp where gender = 'M');
+ DEPTNO | DNAME
+--------+-------------
+ 30 | Engineering
+ 10 | Sales
+ 20 | Marketing
+(3 rows)
+
+!ok
+
+# Mix IN and EXISTS
+select * from dept
+where deptno in (select deptno from emp where gender = 'F')
+or exists (select 99, 101 from emp where gender = 'X');
+ DEPTNO | DNAME
+--------+-------------
+ 30 | Engineering
+ 10 | Sales
+(2 rows)
+
+!ok
+
+# Composite key
+select * from dept
+where (deptno, deptno) in (select deptno * 2 - deptno, deptno from emp where gender = 'F');
+
+# Composite key, part literal
+select * from emp
+where (gender, deptno) in (select gender, 10 from emp where gender = 'F');
+ ENAME | DEPTNO | GENDER
+-------+--------+--------
+ Jane | 10 | F
+(1 row)
+
+!ok
+
+# End subquery.oq
[3/4] git commit: [OPTIQ-387] CompileException when cast TRUE to
nullable boolean
Posted by jh...@apache.org.
[OPTIQ-387] CompileException when cast TRUE to nullable boolean
Project: http://git-wip-us.apache.org/repos/asf/incubator-optiq/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-optiq/commit/dc89f1cd
Tree: http://git-wip-us.apache.org/repos/asf/incubator-optiq/tree/dc89f1cd
Diff: http://git-wip-us.apache.org/repos/asf/incubator-optiq/diff/dc89f1cd
Branch: refs/heads/master
Commit: dc89f1cde6532be7f0c278629f80e8e27c6a4b2b
Parents: f67972b
Author: Julian Hyde <jh...@apache.org>
Authored: Tue Aug 26 00:02:41 2014 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Wed Aug 27 18:22:14 2014 -0700
----------------------------------------------------------------------
.../optiq/rules/java/RexImpTable.java | 15 ++++++++++++---
.../optiq/rules/java/RexToLixTranslator.java | 20 +++++++++++++++-----
.../net/hydromatic/optiq/test/JdbcTest.java | 19 +++++++++++++++++++
.../eigenbase/sql/test/SqlOperatorBaseTest.java | 6 ++++++
4 files changed, 52 insertions(+), 8 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/dc89f1cd/core/src/main/java/net/hydromatic/optiq/rules/java/RexImpTable.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/rules/java/RexImpTable.java b/core/src/main/java/net/hydromatic/optiq/rules/java/RexImpTable.java
index b8fb33f..38fc4cc 100644
--- a/core/src/main/java/net/hydromatic/optiq/rules/java/RexImpTable.java
+++ b/core/src/main/java/net/hydromatic/optiq/rules/java/RexImpTable.java
@@ -31,6 +31,7 @@ import org.eigenbase.sql.*;
import org.eigenbase.sql.fun.SqlStdOperatorTable;
import org.eigenbase.sql.fun.SqlTrimFunction;
import org.eigenbase.sql.type.SqlTypeName;
+import org.eigenbase.sql.type.SqlTypeUtil;
import org.eigenbase.sql.validate.SqlUserDefinedAggFunction;
import org.eigenbase.sql.validate.SqlUserDefinedFunction;
import org.eigenbase.util.Util;
@@ -1348,12 +1349,12 @@ public class RexImpTable {
private static class CaseImplementor implements CallImplementor {
public Expression implement(RexToLixTranslator translator, RexCall call,
- NullAs nullAs) {
+ NullAs nullAs) {
return implementRecurse(translator, call, nullAs, 0);
}
- private Expression implementRecurse(
- RexToLixTranslator translator, RexCall call, NullAs nullAs, int i) {
+ private Expression implementRecurse(RexToLixTranslator translator,
+ RexCall call, NullAs nullAs, int i) {
List<RexNode> operands = call.getOperands();
if (i == operands.size() - 1) {
// the "else" clause
@@ -1406,6 +1407,14 @@ public class RexImpTable {
// No cast required, omit cast
return translator.translate(arg, nullAs);
}
+ if (SqlTypeUtil.equalSansNullability(translator.typeFactory,
+ call.getType(), arg.getType())
+ && nullAs == NullAs.NULL
+ && translator.deref(arg) instanceof RexLiteral) {
+ return RexToLixTranslator.translateLiteral(
+ (RexLiteral) translator.deref(arg), call.getType(),
+ translator.typeFactory, nullAs);
+ }
return accurate.implement(translator, call, nullAs);
}
}
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/dc89f1cd/core/src/main/java/net/hydromatic/optiq/rules/java/RexToLixTranslator.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/rules/java/RexToLixTranslator.java b/core/src/main/java/net/hydromatic/optiq/rules/java/RexToLixTranslator.java
index 55b8303..2b11848 100644
--- a/core/src/main/java/net/hydromatic/optiq/rules/java/RexToLixTranslator.java
+++ b/core/src/main/java/net/hydromatic/optiq/rules/java/RexToLixTranslator.java
@@ -384,12 +384,12 @@ public class RexToLixTranslator {
return unboxed;
case LOCAL_REF:
return translate(
- program.getExprList().get(((RexLocalRef) expr).getIndex()),
+ deref(expr),
nullAs,
storageType);
case LITERAL:
return translateLiteral(
- expr,
+ (RexLiteral) expr,
nullifyType(
expr.getType(),
isNullable(expr)
@@ -407,6 +407,17 @@ public class RexToLixTranslator {
}
}
+ /** Dereferences an expression if it is a
+ * {@link org.eigenbase.rex.RexLocalRef}. */
+ public RexNode deref(RexNode expr) {
+ if (expr instanceof RexLocalRef) {
+ RexLocalRef ref = (RexLocalRef) expr;
+ return program.getExprList().get(ref.getIndex());
+ } else {
+ return expr;
+ }
+ }
+
/** Translates a call to an operator or function. */
private Expression translateCall(RexCall call, RexImpTable.NullAs nullAs) {
final SqlOperator operator = call.getOperator();
@@ -439,12 +450,11 @@ public class RexToLixTranslator {
* {@link net.hydromatic.optiq.rules.java.RexImpTable.NullAs#NOT_POSSIBLE}.
*/
public static Expression translateLiteral(
- RexNode expr,
+ RexLiteral literal,
RelDataType type,
JavaTypeFactory typeFactory,
RexImpTable.NullAs nullAs) {
- final RexLiteral literal = (RexLiteral) expr;
- Comparable value = literal.getValue();
+ final Comparable value = literal.getValue();
if (value == null) {
switch (nullAs) {
case TRUE:
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/dc89f1cd/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java b/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
index 741468a..e840e91 100644
--- a/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
+++ b/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
@@ -1453,6 +1453,25 @@ public class JdbcTest {
});
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/OPTIQ-387">OPTIQ-387</a>,
+ * "CompileException when cast TRUE to nullable boolean". */
+ @Test public void testTrue() {
+ final OptiqAssert.AssertThat that = OptiqAssert.that();
+ that.query(
+ "select case when deptno = 10 then null else true end as x\n"
+ + "from (values (10), (20)) as t(deptno)")
+ .returnsUnordered("X=null", "X=true");
+ that.query(
+ "select case when deptno = 10 then null else 100 end as x\n"
+ + "from (values (10), (20)) as t(deptno)")
+ .returnsUnordered("X=null", "X=100");
+ that.query(
+ "select case when deptno = 10 then null else 'xy' end as x\n"
+ + "from (values (10), (20)) as t(deptno)")
+ .returnsUnordered("X=null", "X=xy");
+ }
+
/** Unit test for self-join. Left and right children of the join are the same
* relational expression. */
@Test public void testSelfJoin() {
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/dc89f1cd/core/src/test/java/org/eigenbase/sql/test/SqlOperatorBaseTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/eigenbase/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/eigenbase/sql/test/SqlOperatorBaseTest.java
index f1080f7..6f3cc18 100644
--- a/core/src/test/java/org/eigenbase/sql/test/SqlOperatorBaseTest.java
+++ b/core/src/test/java/org/eigenbase/sql/test/SqlOperatorBaseTest.java
@@ -1426,6 +1426,12 @@ public abstract class SqlOperatorBaseTest {
// TODO: Check case with multisets
}
+ @Test public void testCaseNull() {
+ tester.setFor(SqlStdOperatorTable.CASE);
+ tester.checkScalarExact("case when 1 = 1 then 10 else null end", "10");
+ tester.checkNull("case when 1 = 2 then 10 else null end");
+ }
+
@Test public void testCaseType() {
tester.setFor(SqlStdOperatorTable.CASE);
tester.checkType(
[2/4] git commit: [OPTIQ-373] NULL values in NOT IN sub-queries
Posted by jh...@apache.org.
[OPTIQ-373] NULL values in NOT IN sub-queries
Project: http://git-wip-us.apache.org/repos/asf/incubator-optiq/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-optiq/commit/793e5c4d
Tree: http://git-wip-us.apache.org/repos/asf/incubator-optiq/tree/793e5c4d
Diff: http://git-wip-us.apache.org/repos/asf/incubator-optiq/diff/793e5c4d
Branch: refs/heads/master
Commit: 793e5c4d234af987809040324177966cee5954aa
Parents: dc89f1c
Author: Julian Hyde <jh...@apache.org>
Authored: Wed Aug 27 16:52:21 2014 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Wed Aug 27 18:22:14 2014 -0700
----------------------------------------------------------------------
.../net/hydromatic/optiq/BuiltinMethod.java | 1 +
.../hydromatic/optiq/rules/java/JavaRules.java | 7 +-
.../optiq/rules/java/RexImpTable.java | 30 +-
.../optiq/rules/java/RexToLixTranslator.java | 4 +-
.../hydromatic/optiq/runtime/SqlFunctions.java | 5 +
.../org/eigenbase/rel/AggregateRelBase.java | 3 +-
.../main/java/org/eigenbase/rel/JoinInfo.java | 30 +-
.../java/org/eigenbase/relopt/RelOptUtil.java | 213 +++--
.../main/java/org/eigenbase/rex/RexUtil.java | 21 -
.../org/eigenbase/sql/fun/SqlInOperator.java | 17 +-
.../eigenbase/sql2rel/SqlToRelConverter.java | 845 +++++++++----------
.../org/eigenbase/util/ImmutableIntList.java | 15 +
core/src/main/java/org/eigenbase/util/Util.java | 44 +
.../net/hydromatic/optiq/test/JdbcTest.java | 58 +-
.../eigenbase/test/SqlToRelConverterTest.java | 58 +-
.../test/java/org/eigenbase/util/UtilTest.java | 65 +-
.../eigenbase/test/SqlToRelConverterTest.xml | 90 ++
core/src/test/resources/sql/misc.oq | 49 --
core/src/test/resources/sql/outer.oq | 17 +
core/src/test/resources/sql/subquery.oq | 234 +++++
20 files changed, 1157 insertions(+), 649 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/net/hydromatic/optiq/BuiltinMethod.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/BuiltinMethod.java b/core/src/main/java/net/hydromatic/optiq/BuiltinMethod.java
index c5cbba1..37063cf 100644
--- a/core/src/main/java/net/hydromatic/optiq/BuiltinMethod.java
+++ b/core/src/main/java/net/hydromatic/optiq/BuiltinMethod.java
@@ -168,6 +168,7 @@ public enum BuiltinMethod {
SIMILAR(SqlFunctions.class, "similar", String.class, String.class),
IS_TRUE(SqlFunctions.class, "isTrue", Boolean.class),
IS_NOT_FALSE(SqlFunctions.class, "isNotFalse", Boolean.class),
+ NOT(SqlFunctions.class, "not", Boolean.class),
MODIFIABLE_TABLE_GET_MODIFIABLE_COLLECTION(ModifiableTable.class,
"getModifiableCollection"),
STRING_TO_BOOLEAN(SqlFunctions.class, "toBoolean", String.class),
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java b/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java
index 094c7a5..16ebfcd 100644
--- a/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java
+++ b/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java
@@ -1076,15 +1076,10 @@ public class JavaRules {
ParameterExpression parameter =
Expressions.parameter(inputPhysType.getJavaRowType(), "a0");
- final List<Expression> keyExpressions = Expressions.list();
- PhysType keyPhysType =
+ final PhysType keyPhysType =
inputPhysType.project(
BitSets.toList(groupSet), JavaRowFormat.LIST);
final int keyArity = groupSet.cardinality();
- for (int groupKey : BitSets.toIter(groupSet)) {
- keyExpressions.add(
- inputPhysType.fieldReference(parameter, groupKey));
- }
final Expression keySelector =
builder.append(
"keySelector",
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/net/hydromatic/optiq/rules/java/RexImpTable.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/rules/java/RexImpTable.java b/core/src/main/java/net/hydromatic/optiq/rules/java/RexImpTable.java
index 38fc4cc..98b78c3 100644
--- a/core/src/main/java/net/hydromatic/optiq/rules/java/RexImpTable.java
+++ b/core/src/main/java/net/hydromatic/optiq/rules/java/RexImpTable.java
@@ -347,21 +347,28 @@ public class RexImpTable {
// else if any arguments are null, result is null;
// else false.
return new CallImplementor() {
- public Expression implement(
- RexToLixTranslator translator, RexCall call, NullAs nullAs) {
- NullAs nullAs2;
+ public Expression implement(RexToLixTranslator translator, RexCall call,
+ NullAs nullAs) {
+ switch (nullAs) {
+ case NULL:
+ return Expressions.call(BuiltinMethod.NOT.method,
+ translator.translateList(call.getOperands(), nullAs));
+ default:
+ return Expressions.not(
+ translator.translate(call.getOperands().get(0),
+ negate(nullAs)));
+ }
+ }
+
+ private NullAs negate(NullAs nullAs) {
switch (nullAs) {
case FALSE:
- nullAs2 = NullAs.TRUE;
- break;
+ return NullAs.TRUE;
case TRUE:
- nullAs2 = NullAs.FALSE;
- break;
+ return NullAs.FALSE;
default:
- nullAs2 = nullAs;
+ return nullAs;
}
- return implementNullSemantics0(
- translator, call, nullAs2, nullPolicy, harmonize, implementor);
}
};
case NONE:
@@ -602,7 +609,8 @@ public class RexImpTable {
RexToLixTranslator translator,
RexCall call,
NullAs nullAs,
- NullPolicy nullPolicy, NotNullImplementor implementor) {
+ NullPolicy nullPolicy,
+ NotNullImplementor implementor) {
final List<Expression> list = new ArrayList<Expression>();
switch (nullAs) {
case NULL:
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/net/hydromatic/optiq/rules/java/RexToLixTranslator.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/rules/java/RexToLixTranslator.java b/core/src/main/java/net/hydromatic/optiq/rules/java/RexToLixTranslator.java
index 2b11848..369f9b1 100644
--- a/core/src/main/java/net/hydromatic/optiq/rules/java/RexToLixTranslator.java
+++ b/core/src/main/java/net/hydromatic/optiq/rules/java/RexToLixTranslator.java
@@ -412,7 +412,9 @@ public class RexToLixTranslator {
public RexNode deref(RexNode expr) {
if (expr instanceof RexLocalRef) {
RexLocalRef ref = (RexLocalRef) expr;
- return program.getExprList().get(ref.getIndex());
+ final RexNode e2 = program.getExprList().get(ref.getIndex());
+ assert ref.getType().equals(e2.getType());
+ return e2;
} else {
return expr;
}
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/net/hydromatic/optiq/runtime/SqlFunctions.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/runtime/SqlFunctions.java b/core/src/main/java/net/hydromatic/optiq/runtime/SqlFunctions.java
index 3670dd7..9b4faac 100644
--- a/core/src/main/java/net/hydromatic/optiq/runtime/SqlFunctions.java
+++ b/core/src/main/java/net/hydromatic/optiq/runtime/SqlFunctions.java
@@ -1631,6 +1631,11 @@ public class SqlFunctions {
return b == null || b;
}
+ /** NULL → NULL, FALSE → TRUE, TRUE → FALSE. */
+ public static Boolean not(Boolean b) {
+ return (b == null) ? null : !b;
+ }
+
/** Converts a JDBC array to a list. */
public static List arrayToList(final java.sql.Array a) {
if (a == null) {
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/org/eigenbase/rel/AggregateRelBase.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/AggregateRelBase.java b/core/src/main/java/org/eigenbase/rel/AggregateRelBase.java
index f741438..3aac21c 100644
--- a/core/src/main/java/org/eigenbase/rel/AggregateRelBase.java
+++ b/core/src/main/java/org/eigenbase/rel/AggregateRelBase.java
@@ -66,7 +66,8 @@ public abstract class AggregateRelBase extends SingleRel {
this.groupSet = groupSet;
assert groupSet != null;
assert groupSet.isEmpty() == (groupSet.cardinality() == 0)
- : "See http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6222207";
+ : "See https://bugs.openjdk.java.net/browse/JDK-6222207, "
+ + "BitSet internal invariants may be violated";
for (AggregateCall aggCall : aggCalls) {
assert typeMatchesInferred(aggCall, true);
}
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/org/eigenbase/rel/JoinInfo.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/JoinInfo.java b/core/src/main/java/org/eigenbase/rel/JoinInfo.java
index 59bf0d9..f40374c 100644
--- a/core/src/main/java/org/eigenbase/rel/JoinInfo.java
+++ b/core/src/main/java/org/eigenbase/rel/JoinInfo.java
@@ -16,17 +16,13 @@
*/
package org.eigenbase.rel;
-import java.util.AbstractList;
import java.util.ArrayList;
import java.util.BitSet;
import java.util.List;
import org.eigenbase.relopt.RelOptUtil;
-import org.eigenbase.reltype.RelDataType;
import org.eigenbase.rex.RexBuilder;
import org.eigenbase.rex.RexNode;
-import org.eigenbase.rex.RexUtil;
-import org.eigenbase.sql.fun.SqlStdOperatorTable;
import org.eigenbase.util.ImmutableIntList;
import org.eigenbase.util.mapping.IntPair;
@@ -95,28 +91,10 @@ public abstract class JoinInfo {
public abstract RexNode getRemaining(RexBuilder rexBuilder);
- public RexNode getEquiCondition(final RelNode left, final RelNode right,
- final RexBuilder rexBuilder) {
- final List<RelDataType> leftTypes =
- RelOptUtil.getFieldTypeList(left.getRowType());
- final List<RelDataType> rightTypes =
- RelOptUtil.getFieldTypeList(right.getRowType());
- return RexUtil.composeConjunction(rexBuilder,
- new AbstractList<RexNode>() {
- @Override public RexNode get(int index) {
- final int leftKey = leftKeys.get(index);
- final int rightKey = rightKeys.get(index);
- return rexBuilder.makeCall(SqlStdOperatorTable.EQUALS,
- rexBuilder.makeInputRef(leftTypes.get(leftKey), leftKey),
- rexBuilder.makeInputRef(rightTypes.get(rightKey),
- leftTypes.size() + rightKey));
- }
-
- @Override public int size() {
- return leftKeys.size();
- }
- },
- false);
+ public RexNode getEquiCondition(RelNode left, RelNode right,
+ RexBuilder rexBuilder) {
+ return RelOptUtil.createEquiJoinCondition(left, leftKeys, right, rightKeys,
+ rexBuilder);
}
/** JoinInfo that represents an equi-join. */
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java b/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java
index caef0e2..c5e11c7 100644
--- a/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java
+++ b/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java
@@ -317,77 +317,92 @@ public abstract class RelOptUtil {
return ret;
}
- public static RelNode createExistsPlan(
- RelOptCluster cluster,
+ /**
+ * Creates a plan suitable for use in <code>EXISTS</code> or <code>IN</code>
+ * statements.
+ *
+ * @see org.eigenbase.sql2rel.SqlToRelConverter#convertExists
+ *
+ * @param seekRel A query rel, for example the resulting rel from 'select *
+ * from emp' or 'values (1,2,3)' or '('Foo', 34)'.
+ * @param subqueryType Sub-query type
+ * @param logic Whether to use 2- or 3-valued boolean logic
+ * @param needsOuterJoin Whether query needs outer join
+ *
+ * @return A pair of a relational expression which outer joins a boolean
+ * condition column, and a numeric offset. The offset is 2 if column 0 is
+ * the number of rows and column 1 is the number of rows with not-null keys;
+ * 0 otherwise.
+ */
+ public static Pair<RelNode, Boolean> createExistsPlan(
RelNode seekRel,
- boolean isIn,
- boolean isExists,
+ SubqueryType subqueryType,
+ Logic logic,
boolean needsOuterJoin) {
- RelNode ret = seekRel;
-
- if (isIn || isExists) {
- RexBuilder rexBuilder = cluster.getRexBuilder();
- RelDataTypeFactory typeFactory = rexBuilder.getTypeFactory();
-
- List<RexNode> exprs = new ArrayList<RexNode>();
+ switch (subqueryType) {
+ case SCALAR:
+ return Pair.of(seekRel, false);
+ default:
+ RelNode ret = seekRel;
+ final RelOptCluster cluster = seekRel.getCluster();
+ final RexBuilder rexBuilder = cluster.getRexBuilder();
+ final RelDataTypeFactory typeFactory = rexBuilder.getTypeFactory();
+
+ final int keyCount = ret.getRowType().getFieldCount();
+ if (!needsOuterJoin) {
+ return Pair.<RelNode, Boolean>of(
+ new AggregateRel(cluster, ret, BitSets.range(keyCount),
+ ImmutableList.<AggregateCall>of()),
+ false);
+ }
- // for IN/NOT IN , it needs to output the fields
- if (isIn) {
- for (int i = 0; i < ret.getRowType().getFieldCount(); i++) {
+ // for IN/NOT IN, it needs to output the fields
+ final List<RexNode> exprs = new ArrayList<RexNode>();
+ if (subqueryType == SubqueryType.IN) {
+ for (int i = 0; i < keyCount; i++) {
exprs.add(rexBuilder.makeInputRef(ret, i));
}
}
- if (needsOuterJoin) {
- // First insert an Agg on top of the subquery
- // agg does not like no agg functions so just pretend it is
- // doing a min(TRUE)
- RexNode trueExp = rexBuilder.makeLiteral(true);
- exprs.add(trueExp);
-
- ret = CalcRel.createProject(ret, exprs, null);
-
- List<RelDataType> argTypes =
- ImmutableList.of(
- typeFactory.createSqlType(SqlTypeName.BOOLEAN));
-
- SqlAggFunction minFunction =
- new SqlMinMaxAggFunction(
- argTypes,
- true,
- SqlMinMaxAggFunction.MINMAX_COMPARABLE);
-
- int newProjFieldCount = ret.getRowType().getFieldCount();
-
- RelDataType returnType =
- minFunction.inferReturnType(new AggregateRelBase.AggCallBinding(
- typeFactory, minFunction, argTypes, newProjFieldCount - 1));
-
- final AggregateCall aggCall =
- new AggregateCall(
- minFunction,
- false,
- Collections.singletonList(newProjFieldCount - 1),
- returnType,
- null);
-
- ret =
- new AggregateRel(
- ret.getCluster(),
- ret,
- BitSets.range(newProjFieldCount - 1),
- Collections.singletonList(aggCall));
- } else {
- ret =
- new AggregateRel(
- ret.getCluster(),
- ret,
- BitSets.range(ret.getRowType().getFieldCount()),
- Collections.<AggregateCall>emptyList());
+ final int projectedKeyCount = exprs.size();
+ exprs.add(rexBuilder.makeLiteral(true));
+
+ ret = CalcRel.createProject(ret, exprs, null);
+
+ final List<RelDataType> argTypes =
+ ImmutableList.of(typeFactory.createSqlType(SqlTypeName.BOOLEAN));
+
+ SqlAggFunction minFunction =
+ new SqlMinMaxAggFunction(argTypes, true,
+ SqlMinMaxAggFunction.MINMAX_COMPARABLE);
+
+ RelDataType returnType =
+ minFunction.inferReturnType(
+ new AggregateRelBase.AggCallBinding(
+ typeFactory, minFunction, argTypes, projectedKeyCount));
+
+ final AggregateCall aggCall =
+ new AggregateCall(
+ minFunction,
+ false,
+ ImmutableList.of(projectedKeyCount),
+ returnType,
+ null);
+
+ ret = new AggregateRel(
+ cluster,
+ ret,
+ BitSets.range(projectedKeyCount),
+ ImmutableList.of(aggCall));
+
+ switch (logic) {
+ case TRUE_FALSE_UNKNOWN:
+ case UNKNOWN_AS_TRUE:
+ return Pair.of(ret, true);
+ default:
+ return Pair.of(ret, false);
}
}
-
- return ret;
}
/**
@@ -1002,6 +1017,33 @@ public abstract class RelOptUtil {
nonEquiList.add(condition);
}
+ /** Builds an equi-join condition from a set of left and right keys. */
+ public static RexNode createEquiJoinCondition(
+ final RelNode left, final List<Integer> leftKeys,
+ final RelNode right, final List<Integer> rightKeys,
+ final RexBuilder rexBuilder) {
+ final List<RelDataType> leftTypes =
+ RelOptUtil.getFieldTypeList(left.getRowType());
+ final List<RelDataType> rightTypes =
+ RelOptUtil.getFieldTypeList(right.getRowType());
+ return RexUtil.composeConjunction(rexBuilder,
+ new AbstractList<RexNode>() {
+ @Override public RexNode get(int index) {
+ final int leftKey = leftKeys.get(index);
+ final int rightKey = rightKeys.get(index);
+ return rexBuilder.makeCall(SqlStdOperatorTable.EQUALS,
+ rexBuilder.makeInputRef(leftTypes.get(leftKey), leftKey),
+ rexBuilder.makeInputRef(rightTypes.get(rightKey),
+ leftTypes.size() + rightKey));
+ }
+
+ @Override public int size() {
+ return leftKeys.size();
+ }
+ },
+ false);
+ }
+
private static SqlKind reverse(SqlKind kind) {
switch (kind) {
case GREATER_THAN:
@@ -2447,6 +2489,46 @@ public abstract class RelOptUtil {
Mappings.apply3(mapping, rowType.getFieldList()));
}
+ /** Policies for handling two- and three-valued boolean logic. */
+ public enum Logic {
+ /** Three-valued boolean logic. */
+ TRUE_FALSE_UNKNOWN,
+
+ /** Nulls are not possible. */
+ TRUE_FALSE,
+
+ /** Two-valued logic where UNKNOWN is treated as FALSE.
+ *
+ * <p>"x IS TRUE" produces the same result, and "WHERE x", "JOIN ... ON x"
+ * and "HAVING x" have the same effect. */
+ UNKNOWN_AS_FALSE,
+
+ /** Two-valued logic where UNKNOWN is treated as TRUE.
+ *
+ * <p>"x IS FALSE" produces the same result, as does "WHERE NOT x", etc.
+ *
+ * <p>In particular, this is the mode used by "WHERE k NOT IN q". If
+ * "k IN q" produces TRUE or UNKNOWN, "NOT k IN q" produces FALSE or
+ * UNKNOWN and the row is eliminated; if "k IN q" it returns FALSE, the
+ * row is retained by the WHERE clause. */
+ UNKNOWN_AS_TRUE,
+
+ /** A semi-join will have been applied, so that only rows for which the
+ * value is TRUE will have been returned. */
+ TRUE;
+
+ public Logic negate() {
+ switch (this) {
+ case UNKNOWN_AS_FALSE:
+ return UNKNOWN_AS_TRUE;
+ case UNKNOWN_AS_TRUE:
+ return UNKNOWN_AS_FALSE;
+ default:
+ return this;
+ }
+ }
+ }
+
//~ Inner Classes ----------------------------------------------------------
/** Visitor that finds all variables used but not stopped in an expression. */
@@ -2709,6 +2791,13 @@ public abstract class RelOptUtil {
}
}
}
+
+ /** What kind of sub-query. */
+ public enum SubqueryType {
+ EXISTS,
+ IN,
+ SCALAR
+ }
}
// End RelOptUtil.java
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/org/eigenbase/rex/RexUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rex/RexUtil.java b/core/src/main/java/org/eigenbase/rex/RexUtil.java
index 2550da0..508b1b2 100644
--- a/core/src/main/java/org/eigenbase/rex/RexUtil.java
+++ b/core/src/main/java/org/eigenbase/rex/RexUtil.java
@@ -915,27 +915,6 @@ public class RexUtil {
}
/**
- * Returns a list generated by applying a function to each index between
- * 0 and {@code size} - 1.
- */
- public static <E> List<E> generate(
- final int size,
- final Function1<Integer, E> fn) {
- if (size < 0) {
- throw new IllegalArgumentException();
- }
- return new AbstractList<E>() {
- public int size() {
- return size;
- }
-
- public E get(int index) {
- return fn.apply(index);
- }
- };
- }
-
- /**
* Shifts every {@link RexInputRef} in an expression by {@code offset}.
*/
public static RexNode shift(RexNode node, final int offset) {
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/org/eigenbase/sql/fun/SqlInOperator.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/sql/fun/SqlInOperator.java b/core/src/main/java/org/eigenbase/sql/fun/SqlInOperator.java
index 5ff2a3a..b120d78 100644
--- a/core/src/main/java/org/eigenbase/sql/fun/SqlInOperator.java
+++ b/core/src/main/java/org/eigenbase/sql/fun/SqlInOperator.java
@@ -142,12 +142,19 @@ public class SqlInOperator extends SqlBinaryOperator {
// Result is a boolean, nullable if there are any nullable types
// on either side.
- RelDataType type = typeFactory.createSqlType(SqlTypeName.BOOLEAN);
- if (leftType.isNullable() || rightType.isNullable()) {
- type = typeFactory.createTypeWithNullability(type, true);
- }
+ return typeFactory.createTypeWithNullability(
+ typeFactory.createSqlType(SqlTypeName.BOOLEAN),
+ anyNullable(leftRowType.getFieldList())
+ || anyNullable(rightRowType.getFieldList()));
+ }
- return type;
+ private static boolean anyNullable(List<RelDataTypeField> fieldList) {
+ for (RelDataTypeField field : fieldList) {
+ if (field.getType().isNullable()) {
+ return true;
+ }
+ }
+ return false;
}
public boolean argumentMustBeScalar(int ordinal) {
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/org/eigenbase/sql2rel/SqlToRelConverter.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/eigenbase/sql2rel/SqlToRelConverter.java
index b241bfb..0ef6e9f 100644
--- a/core/src/main/java/org/eigenbase/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/eigenbase/sql2rel/SqlToRelConverter.java
@@ -38,7 +38,6 @@ import org.eigenbase.util.mapping.Mappings;
import org.eigenbase.util14.*;
import net.hydromatic.linq4j.Ord;
-import net.hydromatic.linq4j.function.Function1;
import net.hydromatic.optiq.ModifiableTable;
import net.hydromatic.optiq.TranslatableTable;
@@ -46,6 +45,7 @@ import net.hydromatic.optiq.prepare.Prepare;
import net.hydromatic.optiq.prepare.RelOptTableImpl;
import net.hydromatic.optiq.util.BitSets;
+import com.google.common.base.Function;
import com.google.common.collect.*;
import static org.eigenbase.sql.SqlUtil.stripAs;
@@ -65,6 +65,13 @@ public class SqlToRelConverter {
protected static final Logger SQL2REL_LOGGER =
EigenbaseTrace.getSqlToRelTracer();
+ private static final Function<SubQuery, SqlNode> FN =
+ new Function<SubQuery, SqlNode>() {
+ public SqlNode apply(SubQuery input) {
+ return input.node;
+ }
+ };
+
//~ Instance fields --------------------------------------------------------
protected final SqlValidator validator;
@@ -793,7 +800,7 @@ public class SqlToRelConverter {
return;
}
SqlNode newWhere = pushDownNotForIn(where);
- replaceSubqueries(bb, newWhere);
+ replaceSubqueries(bb, newWhere, RelOptUtil.Logic.UNKNOWN_AS_FALSE);
final RexNode convertedWhere = bb.convertExpression(newWhere);
// only allocate filter if the condition is not TRUE
@@ -806,93 +813,73 @@ public class SqlToRelConverter {
private void replaceSubqueries(
final Blackboard bb,
- final SqlNode expr) {
- findSubqueries(bb, expr, false);
- for (SqlNode node : bb.subqueryList) {
+ final SqlNode expr,
+ RelOptUtil.Logic logic) {
+ findSubqueries(bb, expr, logic, false);
+ for (SubQuery node : bb.subqueryList) {
substituteSubquery(bb, node);
}
}
- private void substituteSubquery(Blackboard bb, SqlNode node) {
- JoinRelType joinType = JoinRelType.INNER;
- RexNode[] leftJoinKeysForIn = null;
- boolean isNotIn;
- boolean subqueryNeedsOuterJoin = bb.subqueryNeedsOuterJoin;
- SqlBasicCall call;
- SqlNode query;
-
- final RexNode expr = bb.mapSubqueryToExpr.get(node);
+ private void substituteSubquery(Blackboard bb, SubQuery subQuery) {
+ final RexNode expr = subQuery.expr;
if (expr != null) {
// Already done.
return;
}
- RelNode converted;
- switch (node.getKind()) {
+
+ final SqlBasicCall call;
+ final RelNode rel;
+ final SqlNode query;
+ final Pair<RelNode, Boolean> converted;
+ switch (subQuery.node.getKind()) {
case CURSOR:
- convertCursor(bb, (SqlCall) node);
+ convertCursor(bb, subQuery);
return;
+
case MULTISET_QUERY_CONSTRUCTOR:
case MULTISET_VALUE_CONSTRUCTOR:
- converted = convertMultisets(
- ImmutableList.of(node),
- bb);
- break;
+ rel = convertMultisets(ImmutableList.of(subQuery.node), bb);
+ subQuery.expr = bb.register(rel, JoinRelType.INNER);
+ return;
+
case IN:
- call = (SqlBasicCall) node;
+ call = (SqlBasicCall) subQuery.node;
final SqlNode[] operands = call.getOperands();
- isNotIn = ((SqlInOperator) call.getOperator()).isNotIn();
SqlNode leftKeyNode = operands[0];
- SqlNode seek = operands[1];
-
- if ((leftKeyNode instanceof SqlCall)
- && (((SqlCall) leftKeyNode).getOperator()
- instanceof SqlRowOperator)) {
- SqlBasicCall keyCall = (SqlBasicCall) leftKeyNode;
- SqlNode[] keyCallOperands = keyCall.getOperands();
- int rowLength = keyCallOperands.length;
- leftJoinKeysForIn = new RexNode[rowLength];
-
- for (int i = 0; i < rowLength; i++) {
- SqlNode sqlExpr = keyCallOperands[i];
- leftJoinKeysForIn[i] = bb.convertExpression(sqlExpr);
+ query = operands[1];
+
+ final List<RexNode> leftKeys;
+ switch (leftKeyNode.getKind()) {
+ case ROW:
+ leftKeys = Lists.newArrayList();
+ for (SqlNode sqlExpr : ((SqlBasicCall) leftKeyNode).getOperandList()) {
+ leftKeys.add(bb.convertExpression(sqlExpr));
}
- } else {
- leftJoinKeysForIn = new RexNode[1];
- leftJoinKeysForIn[0] = bb.convertExpression(leftKeyNode);
+ break;
+ default:
+ leftKeys = ImmutableList.of(bb.convertExpression(leftKeyNode));
}
- if (seek instanceof SqlNodeList) {
- SqlNodeList valueList = (SqlNodeList) seek;
- boolean seenNull = false;
-
- // check for nulls
- for (int i = 0; i < valueList.size(); i++) {
- SqlNode sqlNode = valueList.getList().get(i);
- if (sqlNode instanceof SqlLiteral) {
- SqlLiteral lit = (SqlLiteral) sqlNode;
- if (lit.getValue() == null) {
- seenNull = true;
- }
- }
- }
-
- if (!seenNull
- && (valueList.size() < getInSubqueryThreshold())) {
+ final boolean isNotIn = ((SqlInOperator) call.getOperator()).isNotIn();
+ if (query instanceof SqlNodeList) {
+ SqlNodeList valueList = (SqlNodeList) query;
+ if (!containsNullLiteral(valueList)
+ && valueList.size() < getInSubqueryThreshold()) {
// We're under the threshold, so convert to OR.
- RexNode expression =
+ subQuery.expr =
convertInToOr(
bb,
- leftJoinKeysForIn,
+ leftKeys,
valueList,
isNotIn);
- bb.mapSubqueryToExpr.put(node, expression);
return;
- } else {
- // Otherwise, let convertExists translate
- // values list into an inline table for the
- // reference to Q below.
}
+
+ // Otherwise, let convertExists translate
+ // values list into an inline table for the
+ // reference to Q below.
}
// Project out the search columns from the left side
@@ -917,18 +904,45 @@ public class SqlToRelConverter {
// where emp.deptno <> null
// and q.indicator <> TRUE"
//
+ final boolean outerJoin = bb.subqueryNeedsOuterJoin
+ || isNotIn
+ || subQuery.logic == RelOptUtil.Logic.TRUE_FALSE_UNKNOWN;
converted =
- convertExists(
- seek,
- true,
- false,
- subqueryNeedsOuterJoin || isNotIn);
- if (subqueryNeedsOuterJoin || isNotIn) {
- joinType = JoinRelType.LEFT;
- } else {
- joinType = JoinRelType.INNER;
+ convertExists(query, RelOptUtil.SubqueryType.IN, subQuery.logic,
+ outerJoin);
+ if (converted.right) {
+ // Generate
+ // emp CROSS JOIN (SELECT COUNT(*) AS c,
+ // COUNT(deptno) AS ck FROM dept)
+ final RelDataType longType =
+ typeFactory.createSqlType(SqlTypeName.BIGINT);
+ final RelNode seek = converted.left.getInput(0); // fragile
+ final int keyCount = leftKeys.size();
+ final List<Integer> args = ImmutableIntList.range(0, keyCount);
+ AggregateRel aggregate =
+ new AggregateRel(cluster, seek, BitSets.of(),
+ ImmutableList.of(
+ new AggregateCall(SqlStdOperatorTable.COUNT, false,
+ ImmutableList.<Integer>of(), longType, null),
+ new AggregateCall(SqlStdOperatorTable.COUNT, false,
+ args, longType, null)));
+ JoinRel join =
+ new JoinRel(cluster, bb.root, aggregate,
+ rexBuilder.makeLiteral(true), JoinRelType.INNER,
+ ImmutableSet.<String>of());
+ bb.setRoot(join, false);
}
- break;
+ RexNode rex =
+ bb.register(converted.left,
+ outerJoin ? JoinRelType.LEFT : JoinRelType.INNER, leftKeys);
+
+ subQuery.expr = translateIn(subQuery, bb.root, rex);
+ if (isNotIn) {
+ subQuery.expr =
+ rexBuilder.makeCall(SqlStdOperatorTable.NOT, subQuery.expr);
+ }
+ return;
+
case EXISTS:
// "select from emp where exists (select a from T)"
//
@@ -939,63 +953,168 @@ public class SqlToRelConverter {
//
// If there is no correlation, the expression is replaced with a
// boolean indicating whether the subquery returned 0 or >= 1 row.
- call = (SqlBasicCall) node;
+ call = (SqlBasicCall) subQuery.node;
query = call.getOperands()[0];
- converted = convertExists(query, false, true, true);
- if (convertNonCorrelatedSubq(call, bb, converted, true)) {
+ converted = convertExists(query, RelOptUtil.SubqueryType.EXISTS,
+ subQuery.logic, true);
+ assert !converted.right;
+ if (convertNonCorrelatedSubQuery(subQuery, bb, converted.left, true)) {
return;
}
- joinType = JoinRelType.LEFT;
- break;
+ subQuery.expr = bb.register(converted.left, JoinRelType.LEFT);
+ return;
case SCALAR_QUERY:
// Convert the subquery. If it's non-correlated, convert it
// to a constant expression.
- call = (SqlBasicCall) node;
+ call = (SqlBasicCall) subQuery.node;
query = call.getOperands()[0];
- converted = convertExists(query, false, false, true);
- if (convertNonCorrelatedSubq(call, bb, converted, false)) {
+ converted = convertExists(query, RelOptUtil.SubqueryType.SCALAR,
+ subQuery.logic, true);
+ assert !converted.right;
+ if (convertNonCorrelatedSubQuery(subQuery, bb, converted.left, false)) {
return;
}
- converted = convertToSingleValueSubq(query, converted);
- joinType = JoinRelType.LEFT;
- break;
+ rel = convertToSingleValueSubq(query, converted.left);
+ subQuery.expr = bb.register(rel, JoinRelType.LEFT);
+ return;
case SELECT:
// This is used when converting multiset queries:
//
// select * from unnest(select multiset[deptno] from emps);
//
- converted = convertExists(node, false, false, true);
- joinType = JoinRelType.LEFT;
- break;
+ converted = convertExists(subQuery.node, RelOptUtil.SubqueryType.SCALAR,
+ subQuery.logic, true);
+ assert !converted.right;
+ subQuery.expr = bb.register(converted.left, JoinRelType.LEFT);
+ return;
+
default:
- throw Util.newInternal(
- "unexpected kind of subquery :" + node);
- }
- final RexNode expression =
- bb.register(
- converted,
- joinType,
- leftJoinKeysForIn);
- bb.mapSubqueryToExpr.put(node, expression);
+ throw Util.newInternal("unexpected kind of subquery :" + subQuery.node);
+ }
+ }
+
+ private RexNode translateIn(SubQuery subQuery, RelNode root,
+ final RexNode rex) {
+ switch (subQuery.logic) {
+ case TRUE:
+ return rexBuilder.makeLiteral(true);
+
+ case UNKNOWN_AS_FALSE:
+ assert rex instanceof RexRangeRef;
+ final int fieldCount = rex.getType().getFieldCount();
+ RexNode rexNode = rexBuilder.makeFieldAccess(rex, fieldCount - 1);
+ rexNode = rexBuilder.makeCall(SqlStdOperatorTable.IS_TRUE, rexNode);
+
+ // Then append the IS NOT NULL(leftKeysForIn).
+ //
+ // RexRangeRef contains the following fields:
+ // leftKeysForIn,
+ // rightKeysForIn (the original subquery select list),
+ // nullIndicator
+ //
+ // The first two lists contain the same number of fields.
+ final int k = (fieldCount - 1) / 2;
+ for (int i = 0; i < k; i++) {
+ rexNode =
+ rexBuilder.makeCall(
+ SqlStdOperatorTable.AND,
+ rexNode,
+ rexBuilder.makeCall(
+ SqlStdOperatorTable.IS_NOT_NULL,
+ rexBuilder.makeFieldAccess(rex, i)));
+ }
+ return rexNode;
+
+ case TRUE_FALSE_UNKNOWN:
+ case UNKNOWN_AS_TRUE:
+ // select e.deptno,
+ // case
+ // when ct.c = 0 then false
+ // when dt.i is not null then true
+ // when e.deptno is null then null
+ // when ct.ck < ct.c then null
+ // else false
+ // end
+ // from e
+ // cross join (select count(*) as c, count(deptno) as ck from v) as ct
+ // left join (select distinct deptno, true as i from v) as dt
+ // on e.deptno = dt.deptno
+ final JoinRelBase join = (JoinRelBase) root;
+ final ProjectRelBase left = (ProjectRelBase) join.getLeft();
+ final RelNode leftLeft = ((JoinRelBase) left.getInput(0)).getLeft();
+ final int leftLeftCount = leftLeft.getRowType().getFieldCount();
+ final RelDataType nullableBooleanType =
+ typeFactory.createTypeWithNullability(
+ typeFactory.createSqlType(SqlTypeName.BOOLEAN), true);
+ final RelDataType longType =
+ typeFactory.createSqlType(SqlTypeName.BIGINT);
+ final RexNode cRef = rexBuilder.makeInputRef(root, leftLeftCount);
+ final RexNode ckRef = rexBuilder.makeInputRef(root, leftLeftCount + 1);
+ final RexNode iRef =
+ rexBuilder.makeInputRef(root, root.getRowType().getFieldCount() - 1);
+
+ final RexLiteral zero =
+ rexBuilder.makeExactLiteral(BigDecimal.ZERO, longType);
+ final RexLiteral trueLiteral = rexBuilder.makeLiteral(true);
+ final RexLiteral falseLiteral = rexBuilder.makeLiteral(false);
+ final RexNode unknownLiteral =
+ rexBuilder.makeNullLiteral(SqlTypeName.BOOLEAN);
+
+ final ImmutableList.Builder<RexNode> args = ImmutableList.builder();
+ args.add(rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, cRef, zero),
+ falseLiteral,
+ rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL, iRef),
+ trueLiteral);
+ final JoinInfo joinInfo = join.analyzeCondition();
+ for (int leftKey : joinInfo.leftKeys) {
+ final RexNode kRef = rexBuilder.makeInputRef(root, leftKey);
+ args.add(rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, kRef),
+ unknownLiteral);
+ }
+ args.add(rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN, ckRef, cRef),
+ unknownLiteral,
+ falseLiteral);
+
+ return rexBuilder.makeCall(
+ nullableBooleanType,
+ SqlStdOperatorTable.CASE,
+ args.build());
+
+ default:
+ throw new AssertionError(subQuery.logic);
+ }
+ }
+
+ private static boolean containsNullLiteral(SqlNodeList valueList) {
+ for (SqlNode node : valueList.getList()) {
+ if (node instanceof SqlLiteral) {
+ SqlLiteral lit = (SqlLiteral) node;
+ if (lit.getValue() == null) {
+ return true;
+ }
+ }
+ }
+ return false;
}
/**
* Determines if a subquery is non-correlated and if so, converts it to a
* constant.
*
- * @param call the call that references the subquery
+ * @param subQuery the call that references the subquery
* @param bb blackboard used to convert the subquery
* @param converted RelNode tree corresponding to the subquery
* @param isExists true if the subquery is part of an EXISTS expression
* @return if the subquery can be converted to a constant
*/
- private boolean convertNonCorrelatedSubq(
- SqlCall call,
+ private boolean convertNonCorrelatedSubQuery(
+ SubQuery subQuery,
Blackboard bb,
RelNode converted,
boolean isExists) {
+ SqlCall call = (SqlBasicCall) subQuery.node;
if (subqueryConverter.canConvertSubquery()
&& isSubqNonCorrelated(converted, bb)) {
// First check if the subquery has already been converted
@@ -1011,7 +1130,7 @@ public class SqlToRelConverter {
isExplain);
}
if (constExpr != null) {
- bb.mapSubqueryToExpr.put(call, constExpr);
+ subQuery.expr = constExpr;
mapConvertedNonCorrSubqs.put(call, constExpr);
return true;
}
@@ -1065,35 +1184,32 @@ public class SqlToRelConverter {
*/
private RexNode convertInToOr(
final Blackboard bb,
- final RexNode[] leftKeys,
+ final List<RexNode> leftKeys,
SqlNodeList valuesList,
boolean isNotIn) {
List<RexNode> comparisons = new ArrayList<RexNode>();
for (SqlNode rightVals : valuesList) {
- RexNode rexComparison = null;
- if (leftKeys.length == 1) {
+ RexNode rexComparison;
+ if (leftKeys.size() == 1) {
rexComparison =
rexBuilder.makeCall(
SqlStdOperatorTable.EQUALS,
- leftKeys[0],
+ leftKeys.get(0),
bb.convertExpression(rightVals));
} else {
assert rightVals instanceof SqlCall;
final SqlBasicCall call = (SqlBasicCall) rightVals;
assert (call.getOperator() instanceof SqlRowOperator)
- && call.getOperands().length == leftKeys.length;
+ && call.getOperands().length == leftKeys.size();
rexComparison =
RexUtil.composeConjunction(
rexBuilder,
- RexUtil.generate(
- leftKeys.length,
- new Function1<Integer, RexNode>() {
- public RexNode apply(Integer i) {
- return rexBuilder.makeCall(
- SqlStdOperatorTable.EQUALS,
- leftKeys[i],
- bb.convertExpression(
- call.getOperands()[i]));
+ Iterables.transform(
+ Pair.zip(leftKeys, call.getOperandList()),
+ new Function<Pair<RexNode, SqlNode>, RexNode>() {
+ public RexNode apply(Pair<RexNode, SqlNode> pair) {
+ return rexBuilder.makeCall(SqlStdOperatorTable.EQUALS,
+ pair.left, bb.convertExpression(pair.right));
}
}),
false);
@@ -1129,62 +1245,27 @@ public class SqlToRelConverter {
}
/**
- * Creates the condition for a join implementing an IN clause.
- *
- * @param bb blackboard to use, bb.root points to the LHS
- * @param leftKeys LHS of IN
- * @param rightRel Relational expression on RHS
- * @return join condition
- */
- private RexNode createJoinConditionForIn(
- Blackboard bb,
- List<RexNode> leftKeys,
- RelNode rightRel) {
- List<RexNode> joinConditions = new ArrayList<RexNode>();
-
- // right fields appear after the LHS fields.
- int rightInputOffset = bb.root.getRowType().getFieldCount();
-
- List<RelDataTypeField> rightTypeFields =
- rightRel.getRowType().getFieldList();
- assert leftKeys.size() <= rightTypeFields.size();
-
- for (Ord<RexNode> key : Ord.zip(leftKeys)) {
- joinConditions.add(
- rexBuilder.makeCall(
- SqlStdOperatorTable.EQUALS,
- key.e,
- rexBuilder.makeInputRef(
- rightTypeFields.get(key.i).getType(),
- rightInputOffset + key.i)));
- }
-
- return RexUtil.composeConjunction(rexBuilder, joinConditions, true);
- }
-
- /**
* Converts an EXISTS or IN predicate into a join. For EXISTS, the subquery
* produces an indicator variable, and the result is a relational expression
* which outer joins that indicator to the original query. After performing
* the outer join, the condition will be TRUE if the EXISTS condition holds,
* NULL otherwise.
*
- * <p>FIXME jvs 1-May-2006: null semantics for IN are currently broken
- *
* @param seek A query, for example 'select * from emp' or
* 'values (1,2,3)' or '('Foo', 34)'.
- * @param isIn Whether is an IN predicate
- * @param isExists Whether is an EXISTS predicate
+ * @param subqueryType Whether sub-query is IN, EXISTS or scalar
+ * @param logic Whether the answer needs to be in full 3-valued logic (TRUE,
+ * FALSE, UNKNOWN) will be required, or whether we can accept an
+ * approximation (say representing UNKNOWN as FALSE)
* @param needsOuterJoin Whether an outer join is needed
* @return join expression
* @pre extraExpr == null || extraName != null
*/
- private RelNode convertExists(
+ private Pair<RelNode, Boolean> convertExists(
SqlNode seek,
- boolean isIn,
- boolean isExists,
+ RelOptUtil.SubqueryType subqueryType,
+ RelOptUtil.Logic logic,
boolean needsOuterJoin) {
- assert !isIn || !isExists;
final SqlValidatorScope seekScope =
(seek instanceof SqlSelect)
? validator.getSelectScope((SqlSelect) seek)
@@ -1192,11 +1273,7 @@ public class SqlToRelConverter {
final Blackboard seekBb = createBlackboard(seekScope, null);
RelNode seekRel = convertQueryOrInList(seekBb, seek);
- return RelOptUtil.createExistsPlan(
- cluster,
- seekRel,
- isIn,
- isExists,
+ return RelOptUtil.createExistsPlan(seekRel, subqueryType, logic,
needsOuterJoin);
}
@@ -1394,6 +1471,9 @@ public class SqlToRelConverter {
*
* @param bb blackboard
* @param node the SQL parse tree
+ * @param logic Whether the answer needs to be in full 3-valued logic (TRUE,
+ * FALSE, UNKNOWN) will be required, or whether we can accept
+ * an approximation (say representing UNKNOWN as FALSE)
* @param registerOnlyScalarSubqueries if set to true and the parse tree
* corresponds to a variation of a select
* node, only register it if it's a scalar
@@ -1402,6 +1482,7 @@ public class SqlToRelConverter {
private void findSubqueries(
Blackboard bb,
SqlNode node,
+ RelOptUtil.Logic logic,
boolean registerOnlyScalarSubqueries) {
final SqlKind kind = node.getKind();
switch (kind) {
@@ -1413,46 +1494,62 @@ public class SqlToRelConverter {
case SCALAR_QUERY:
if (!registerOnlyScalarSubqueries
|| (kind == SqlKind.SCALAR_QUERY)) {
- bb.registerSubquery(node);
+ bb.registerSubquery(node, RelOptUtil.Logic.TRUE_FALSE);
}
return;
- default:
- if (node instanceof SqlCall) {
- if (kind == SqlKind.OR
- || kind == SqlKind.NOT) {
- // It's always correct to outer join subquery with
- // containing query; however, when predicates involve Or
- // or NOT, outer join might be necessary.
- bb.subqueryNeedsOuterJoin = true;
- }
- for (SqlNode operand : ((SqlCall) node).getOperandList()) {
- if (operand != null) {
- // In the case of an IN expression, locate scalar
- // subqueries so we can convert them to constants
- findSubqueries(
- bb,
- operand,
- kind == SqlKind.IN || registerOnlyScalarSubqueries);
- }
- }
- } else if (node instanceof SqlNodeList) {
- final SqlNodeList nodes = (SqlNodeList) node;
- for (int i = 0; i < nodes.size(); i++) {
- SqlNode child = nodes.get(i);
+ case IN:
+ if (((SqlCall) node).getOperator() == SqlStdOperatorTable.NOT_IN) {
+ logic = logic.negate();
+ }
+ break;
+ case NOT:
+ logic = logic.negate();
+ break;
+ }
+ if (node instanceof SqlCall) {
+ if (kind == SqlKind.OR
+ || kind == SqlKind.NOT) {
+ // It's always correct to outer join subquery with
+ // containing query; however, when predicates involve Or
+ // or NOT, outer join might be necessary.
+ bb.subqueryNeedsOuterJoin = true;
+ }
+ for (SqlNode operand : ((SqlCall) node).getOperandList()) {
+ if (operand != null) {
+ // In the case of an IN expression, locate scalar
+ // subqueries so we can convert them to constants
findSubqueries(
bb,
- child,
+ operand,
+ logic,
kind == SqlKind.IN || registerOnlyScalarSubqueries);
}
}
+ } else if (node instanceof SqlNodeList) {
+ for (SqlNode child : (SqlNodeList) node) {
+ findSubqueries(
+ bb,
+ child,
+ logic,
+ kind == SqlKind.IN || registerOnlyScalarSubqueries);
+ }
+ }
- // Now that we've located any scalar subqueries inside the IN
- // expression, register the IN expression itself. We need to
- // register the scalar subqueries first so they can be converted
- // before the IN expression is converted.
- if (kind == SqlKind.IN) {
- bb.registerSubquery(node);
+ // Now that we've located any scalar subqueries inside the IN
+ // expression, register the IN expression itself. We need to
+ // register the scalar subqueries first so they can be converted
+ // before the IN expression is converted.
+ if (kind == SqlKind.IN) {
+ if (logic == RelOptUtil.Logic.TRUE_FALSE_UNKNOWN
+ && !validator.getValidatedNodeType(node).isNullable()) {
+ logic = RelOptUtil.Logic.UNKNOWN_AS_FALSE;
+ }
+ // TODO: This conversion is only valid in the WHERE clause
+ if (logic == RelOptUtil.Logic.UNKNOWN_AS_FALSE
+ && !bb.subqueryNeedsOuterJoin) {
+ logic = RelOptUtil.Logic.TRUE;
}
+ bb.registerSubquery(node, logic);
}
}
@@ -1501,9 +1598,9 @@ public class SqlToRelConverter {
/**
* Converts a non-standard expression.
*
- * <p>This method is an extension-point for derived classes can override. If
+ * <p>This method is an extension-point that derived classes can override. If
* this method returns a null result, the normal expression translation
- * process will proceeed. The default implementation always returns null.
+ * process will proceed. The default implementation always returns null.
*
* @param node Expression
* @param bb Blackboard
@@ -1724,7 +1821,7 @@ public class SqlToRelConverter {
case UNNEST:
final SqlNode node = ((SqlCall) from).operand(0);
- replaceSubqueries(bb, node);
+ replaceSubqueries(bb, node, RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
final RelNode childRel =
CalcRel.createProject(
(null != bb.root) ? bb.root : new OneRowRel(cluster),
@@ -1767,7 +1864,7 @@ public class SqlToRelConverter {
datasetStack.pop();
return;
}
- replaceSubqueries(bb, call);
+ replaceSubqueries(bb, call, RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
// Expand table macro if possible. It's more efficient than
// TableFunctionRel.
@@ -1777,8 +1874,7 @@ public class SqlToRelConverter {
final TranslatableTable table = udf.getTable(typeFactory,
call.getOperandList());
final RelDataType rowType = table.getRowType(typeFactory);
- RelOptTable relOptTable =
- RelOptTableImpl.create(null, rowType, (TranslatableTable) table);
+ RelOptTable relOptTable = RelOptTableImpl.create(null, rowType, table);
RelNode converted = toRel(relOptTable);
bb.setRoot(converted, true);
return;
@@ -1834,12 +1930,9 @@ public class SqlToRelConverter {
RelNode rightRel,
RexNode joinCond,
JoinRelType joinType) {
- Set<String> correlatedVariables = RelOptUtil.getVariablesUsed(rightRel);
-
- if (joinCond == null) {
- joinCond = rexBuilder.makeLiteral(true);
- }
+ assert joinCond != null;
+ Set<String> correlatedVariables = RelOptUtil.getVariablesUsed(rightRel);
if (correlatedVariables.size() > 0) {
final List<Correlation> correlations = Lists.newArrayList();
@@ -2190,7 +2283,7 @@ public class SqlToRelConverter {
return rexBuilder.makeLiteral(true);
}
bb.setRoot(ImmutableList.of(leftRel, rightRel));
- replaceSubqueries(bb, condition);
+ replaceSubqueries(bb, condition, RelOptUtil.Logic.UNKNOWN_AS_FALSE);
switch (conditionType) {
case ON:
bb.setRoot(ImmutableList.of(leftRel, rightRel));
@@ -2217,14 +2310,14 @@ public class SqlToRelConverter {
* @param leftRel Left input to the join
* @param rightRel Right input to the join
* @param nameList List of column names to join on
- * @return Expression to match columns from name list, or null if name list
+ * @return Expression to match columns from name list, or true if name list
* is empty
*/
private RexNode convertUsing(
RelNode leftRel,
RelNode rightRel,
List<String> nameList) {
- RexNode conditionExp = null;
+ final List<RexNode> list = Lists.newArrayList();
for (String name : nameList) {
final RelDataType leftRowType = leftRel.getRowType();
RelDataTypeField leftField = catalogReader.field(leftRowType, name);
@@ -2244,17 +2337,9 @@ public class SqlToRelConverter {
SqlStdOperatorTable.EQUALS,
left,
right);
- if (conditionExp == null) {
- conditionExp = equalsCall;
- } else {
- conditionExp =
- rexBuilder.makeCall(
- SqlStdOperatorTable.AND,
- conditionExp,
- equalsCall);
- }
+ list.add(equalsCall);
}
- return conditionExp;
+ return RexUtil.composeConjunction(rexBuilder, list, false);
}
private static JoinRelType convertJoinType(JoinType joinType) {
@@ -2322,7 +2407,7 @@ public class SqlToRelConverter {
// first replace the subqueries inside the aggregates
// because they will provide input rows to the aggregates.
- replaceSubqueries(bb, aggList);
+ replaceSubqueries(bb, aggList, RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
// If group-by clause is missing, pretend that it has zero elements.
if (groupList == null) {
@@ -2461,7 +2546,7 @@ public class SqlToRelConverter {
// the replaced expressions
if (having != null) {
SqlNode newHaving = pushDownNotForIn(having);
- replaceSubqueries(bb, newHaving);
+ replaceSubqueries(bb, newHaving, RelOptUtil.Logic.UNKNOWN_AS_FALSE);
havingExpr = bb.convertExpression(newHaving);
if (havingExpr.isAlwaysTrue()) {
havingExpr = null;
@@ -2472,7 +2557,7 @@ public class SqlToRelConverter {
// This needs to be done separately from the subquery inside
// any aggregate in the select list, and after the aggregate rel
// is allocated.
- replaceSubqueries(bb, selectList);
+ replaceSubqueries(bb, selectList, RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
// Now subqueries in the entire select list have been converted.
// Convert the select expressions to get the final list to be
@@ -3167,17 +3252,17 @@ public class SqlToRelConverter {
return convertMultisets(operands, bb);
}
- private RelNode convertCursor(Blackboard bb, SqlCall cursorCall) {
+ private RelNode convertCursor(Blackboard bb, SubQuery subQuery) {
+ final SqlCall cursorCall = (SqlCall) subQuery.node;
assert cursorCall.operandCount() == 1;
SqlNode query = cursorCall.operand(0);
RelNode converted = convertQuery(query, false, false);
int iCursor = bb.cursors.size();
bb.cursors.add(converted);
- RexNode expr =
+ subQuery.expr =
new RexInputRef(
iCursor,
converted.getRowType());
- bb.mapSubqueryToExpr.put(cursorCall, expr);
return converted;
}
@@ -3326,7 +3411,7 @@ public class SqlToRelConverter {
SqlNodeList selectList = select.getSelectList();
selectList = validator.expandStar(selectList, select, false);
- replaceSubqueries(bb, selectList);
+ replaceSubqueries(bb, selectList, RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
List<String> fieldNames = new ArrayList<String>();
List<RexNode> exprs = new ArrayList<RexNode>();
@@ -3465,7 +3550,8 @@ public class SqlToRelConverter {
for (SqlNode rowConstructor1 : values.getOperandList()) {
SqlCall rowConstructor = (SqlCall) rowConstructor1;
Blackboard tmpBb = createBlackboard(bb.scope, null);
- replaceSubqueries(tmpBb, rowConstructor);
+ replaceSubqueries(tmpBb, rowConstructor,
+ RelOptUtil.Logic.TRUE_FALSE_UNKNOWN);
List<Pair<RexNode, String>> exps =
new ArrayList<Pair<RexNode, String>>();
for (Ord<SqlNode> operand : Ord.zip(rowConstructor.getOperandList())) {
@@ -3539,14 +3625,10 @@ public class SqlToRelConverter {
* List of <code>IN</code> and <code>EXISTS</code> nodes inside this
* <code>SELECT</code> statement (but not inside sub-queries).
*/
- private final List<SqlNode> subqueryList = new ArrayList<SqlNode>();
+ private final Set<SubQuery> subqueryList = Sets.newLinkedHashSet();
- /**
- * Maps IN and EXISTS {@link SqlSelect sub-queries} to the expressions
- * which will be used to access them.
- */
- private final Map<SqlNode, RexNode> mapSubqueryToExpr =
- new HashMap<SqlNode, RexNode>();
+ private final Map<SqlNode, SubQuery> subqueryMap =
+ Util.asIndexMap(subqueryList, FN);
private boolean subqueryNeedsOuterJoin;
@@ -3606,118 +3688,109 @@ public class SqlToRelConverter {
*
* @param rel Relational expression
* @param joinType Join type
- * @param leftJoinKeysForIn LHS of IN clause, or null for expressions
+ * @param leftKeys LHS of IN clause, or null for expressions
* other than IN
* @return Expression with which to refer to the row (or partial row)
- * coming from this relational expression's side of the join. rchen
- * 2006-08-17: temporarily translate select * from X where a not in
- * (select b form Y); to select X.* from X, (select distinct b from Y)
- * where not (a = b);
+ * coming from this relational expression's side of the join
*/
public RexNode register(
RelNode rel,
JoinRelType joinType,
- RexNode[] leftJoinKeysForIn) {
+ List<RexNode> leftKeys) {
assert joinType != null;
if (root == null) {
- assert leftJoinKeysForIn == null;
+ assert leftKeys == null;
setRoot(rel, false);
return rexBuilder.makeRangeReference(
root.getRowType(),
0,
false);
- } else {
- RexNode joinCond = null;
-
- final int origLeftInputCount =
- root.getRowType().getFieldCount();
- if (leftJoinKeysForIn != null) {
- List<RexNode> newLeftInputExpr =
- new ArrayList<RexNode>();
-
- for (int i = 0; i < origLeftInputCount; i++) {
- newLeftInputExpr.add(
- rexBuilder.makeInputRef(root, i));
- }
-
- Collections.addAll(newLeftInputExpr, leftJoinKeysForIn);
-
- ProjectRel newLeftInput =
- (ProjectRel) CalcRel.createProject(
- root,
- newLeftInputExpr,
- null,
- true);
+ }
- // maintain the group by mapping in the new ProjectRel
- if (mapRootRelToFieldProjection.containsKey(root)) {
- mapRootRelToFieldProjection.put(
- newLeftInput,
- mapRootRelToFieldProjection.get(root));
- }
+ final RexNode joinCond;
+ final int origLeftInputCount = root.getRowType().getFieldCount();
+ if (leftKeys != null) {
+ List<RexNode> newLeftInputExpr = Lists.newArrayList();
+ for (int i = 0; i < origLeftInputCount; i++) {
+ newLeftInputExpr.add(rexBuilder.makeInputRef(root, i));
+ }
- setRoot(newLeftInput, false);
+ final List<Integer> leftJoinKeys = Lists.newArrayList();
+ for (RexNode leftKey : leftKeys) {
+ newLeftInputExpr.add(leftKey);
+ leftJoinKeys.add(origLeftInputCount + leftJoinKeys.size());
+ }
- List<RexNode> newLeftJoinKeysForIn =
- new ArrayList<RexNode>();
+ ProjectRel newLeftInput =
+ (ProjectRel) CalcRel.createProject(
+ root,
+ newLeftInputExpr,
+ null,
+ true);
+
+ // maintain the group by mapping in the new ProjectRel
+ if (mapRootRelToFieldProjection.containsKey(root)) {
+ mapRootRelToFieldProjection.put(
+ newLeftInput,
+ mapRootRelToFieldProjection.get(root));
+ }
- for (int i = 0; i < leftJoinKeysForIn.length; i++) {
- final int x = origLeftInputCount + i;
- newLeftJoinKeysForIn.add(
- rexBuilder.makeInputRef(
- newLeftInput.getProjects().get(x).getType(),
- x));
- }
+ setRoot(newLeftInput, false);
- joinCond =
- createJoinConditionForIn(
- this,
- newLeftJoinKeysForIn,
- rel);
- }
+ // right fields appear after the LHS fields.
+ final int rightOffset = root.getRowType().getFieldCount()
+ - newLeftInput.getRowType().getFieldCount();
+ final List<Integer> rightKeys =
+ Util.range(rightOffset, rightOffset + leftJoinKeys.size());
- int leftFieldCount = root.getRowType().getFieldCount();
- final RelNode join =
- createJoin(
- this,
- root,
- rel,
- joinCond,
- joinType);
-
- setRoot(join, false);
-
- if ((leftJoinKeysForIn != null)
- && (joinType == JoinRelType.LEFT)) {
- int rightFieldLength = rel.getRowType().getFieldCount();
- assert leftJoinKeysForIn.length == rightFieldLength - 1;
-
- final int rexRangeRefLength =
- leftJoinKeysForIn.length + rightFieldLength;
- RelDataType returnType =
- typeFactory.createStructType(
- new AbstractList<Map.Entry<String, RelDataType>>() {
- public Map.Entry<String, RelDataType> get(
- int index) {
- return join.getRowType().getFieldList()
- .get(origLeftInputCount + index);
- }
+ joinCond =
+ RelOptUtil.createEquiJoinCondition(newLeftInput, leftJoinKeys,
+ rel, rightKeys, rexBuilder);
+ } else {
+ joinCond = rexBuilder.makeLiteral(true);
+ }
- public int size() {
- return rexRangeRefLength;
- }
- });
+ int leftFieldCount = root.getRowType().getFieldCount();
+ final RelNode join =
+ createJoin(
+ this,
+ root,
+ rel,
+ joinCond,
+ joinType);
+
+ setRoot(join, false);
+
+ if (leftKeys != null
+ && joinType == JoinRelType.LEFT) {
+ final int leftKeyCount = leftKeys.size();
+ int rightFieldLength = rel.getRowType().getFieldCount();
+ assert leftKeyCount == rightFieldLength - 1;
+
+ final int rexRangeRefLength = leftKeyCount + rightFieldLength;
+ RelDataType returnType =
+ typeFactory.createStructType(
+ new AbstractList<Map.Entry<String, RelDataType>>() {
+ public Map.Entry<String, RelDataType> get(
+ int index) {
+ return join.getRowType().getFieldList()
+ .get(origLeftInputCount + index);
+ }
+
+ public int size() {
+ return rexRangeRefLength;
+ }
+ });
- return rexBuilder.makeRangeReference(
- returnType,
- origLeftInputCount,
- false);
- } else {
- return rexBuilder.makeRangeReference(
- rel.getRowType(),
- leftFieldCount,
- joinType.generatesNullsOnRight());
- }
+ return rexBuilder.makeRangeReference(
+ returnType,
+ origLeftInputCount,
+ false);
+ } else {
+ return rexBuilder.makeRangeReference(
+ rel.getRowType(),
+ leftFieldCount,
+ joinType.generatesNullsOnRight());
}
}
@@ -3874,8 +3947,8 @@ public class SqlToRelConverter {
}
}
- void registerSubquery(SqlNode node) {
- subqueryList.add(node);
+ void registerSubquery(SqlNode node, RelOptUtil.Logic logic) {
+ subqueryList.add(new SubQuery(node, logic));
}
ImmutableList<RelNode> retrieveCursors() {
@@ -3916,13 +3989,15 @@ public class SqlToRelConverter {
// Sub-queries and OVER expressions are not like ordinary
// expressions.
final SqlKind kind = expr.getKind();
+ final SubQuery subQuery;
switch (kind) {
case CURSOR:
case SELECT:
case EXISTS:
case SCALAR_QUERY:
- rex = mapSubqueryToExpr.get(expr);
-
+ subQuery = subqueryMap.get(expr);
+ assert subQuery != null;
+ rex = subQuery.expr;
assert rex != null : "rex != null";
if (kind == SqlKind.CURSOR) {
@@ -3964,63 +4039,10 @@ public class SqlToRelConverter {
return fieldAccess;
case IN:
- rex = mapSubqueryToExpr.get(expr);
-
- assert rex != null : "rex != null";
-
- RexNode rexNode;
- boolean isNotInFilter;
- if (rex instanceof RexRangeRef) {
- // IN was converted to subquery.
- isNotInFilter =
- ((SqlInOperator) ((SqlCall) expr).getOperator())
- .isNotIn();
- needTruthTest = subqueryNeedsOuterJoin || isNotInFilter;
- } else {
- // IN was converted to OR; nothing more needed.
- return rex;
- }
-
- if (needTruthTest) {
- assert rex instanceof RexRangeRef;
- rexNode =
- rexBuilder.makeFieldAccess(
- rex,
- rex.getType().getFieldCount() - 1);
- if (!isNotInFilter) {
- rexNode =
- rexBuilder.makeCall(
- SqlStdOperatorTable.IS_TRUE,
- rexNode);
- } else {
- rexNode =
- rexBuilder.makeCall(
- SqlStdOperatorTable.NOT,
- rexBuilder.makeCall(
- SqlStdOperatorTable.IS_TRUE,
- rexNode));
-
- // then append the IS NOT NULL(leftKeysForIn)
- // RexRangeRef contains the following fields:
- // leftKeysForIn, rightKeysForIn(the original subquery
- // select list), nullIndicator The first two lists
- // contain the same number of fields
- for (int i = 0;
- i < ((rex.getType().getFieldCount() - 1) / 2);
- i++) {
- rexNode =
- rexBuilder.makeCall(
- SqlStdOperatorTable.AND,
- rexNode,
- rexBuilder.makeCall(
- SqlStdOperatorTable.IS_NOT_NULL,
- rexBuilder.makeFieldAccess(rex, i)));
- }
- }
- } else {
- rexNode = rexBuilder.makeLiteral(true);
- }
- return rexNode;
+ subQuery = subqueryMap.get(expr);
+ assert subQuery != null;
+ assert subQuery.expr != null : "expr != null";
+ return subQuery.expr;
case OVER:
return convertOver(this, expr);
@@ -4095,7 +4117,9 @@ public class SqlToRelConverter {
// implement SqlRexContext
public RexRangeRef getSubqueryExpr(SqlCall call) {
- return (RexRangeRef) mapSubqueryToExpr.get(call);
+ final SubQuery subQuery = subqueryMap.get(call);
+ assert subQuery != null;
+ return (RexRangeRef) subQuery.expr;
}
// implement SqlRexContext
@@ -4163,46 +4187,6 @@ public class SqlToRelConverter {
return convertInterval(intervalQualifier);
}
- /**
- * Shifts the expressions used to reference subqueries to the right.
- * Moves any reference ≥ <code>index</code> <code>count</code> places
- * to the right.
- *
- * @param index Position where new expression was inserted
- * @param count Number of new expressions inserted
- */
- public void adjustSubqueries(final int index, final int count) {
- for (Map.Entry<SqlNode, RexNode> entry : mapSubqueryToExpr.entrySet()) {
- RexNode expr = entry.getValue();
- RexShuttle shuttle =
- new RexShuttle() {
- public RexNode visitRangeRef(RexRangeRef rangeRef) {
- if (rangeRef.getOffset() >= index) {
- return rexBuilder.makeRangeReference(
- rangeRef.getType(),
- rangeRef.getOffset() + count,
- false);
- } else {
- return rangeRef;
- }
- }
-
- public RexNode visitInputRef(RexInputRef inputRef) {
- if (inputRef.getIndex() >= index) {
- return rexBuilder.makeInputRef(
- inputRef.getType(),
- inputRef.getIndex() + count);
- } else {
- return inputRef;
- }
- }
- };
-
- RexNode newExpr = expr.accept(shuttle);
- entry.setValue(newExpr);
- }
- }
-
public List<SqlMonotonicity> getColumnMonotonicities() {
return columnMonotonicities;
}
@@ -4773,6 +4757,19 @@ public class SqlToRelConverter {
}
}
}
+
+ /** A sub-query, whether it needs to be translated using 2- or 3-valued
+ * logic. */
+ private static class SubQuery {
+ final SqlNode node;
+ final RelOptUtil.Logic logic;
+ RexNode expr;
+
+ private SubQuery(SqlNode node, RelOptUtil.Logic logic) {
+ this.node = node;
+ this.logic = logic;
+ }
+ }
}
// End SqlToRelConverter.java
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/org/eigenbase/util/ImmutableIntList.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/util/ImmutableIntList.java b/core/src/main/java/org/eigenbase/util/ImmutableIntList.java
index 3254ff6..0aefb05 100644
--- a/core/src/main/java/org/eigenbase/util/ImmutableIntList.java
+++ b/core/src/main/java/org/eigenbase/util/ImmutableIntList.java
@@ -198,6 +198,21 @@ public class ImmutableIntList extends FlatLists.AbstractFlatList<Integer> {
return -1;
}
+ /** Returns a list that contains the values lower to upper - 1.
+ *
+ * <p>For example, {@code range(1, 3)} contains [1, 2]. */
+ public static List<Integer> range(final int lower, final int upper) {
+ return new AbstractList<Integer>() {
+ @Override public Integer get(int index) {
+ return lower + index;
+ }
+
+ @Override public int size() {
+ return upper - lower;
+ }
+ };
+ }
+
private static class EmptyImmutableIntList extends ImmutableIntList {
@Override
public Object[] toArray() {
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/main/java/org/eigenbase/util/Util.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/util/Util.java b/core/src/main/java/org/eigenbase/util/Util.java
index 9d947f8..5f98ba2 100644
--- a/core/src/main/java/org/eigenbase/util/Util.java
+++ b/core/src/main/java/org/eigenbase/util/Util.java
@@ -30,11 +30,15 @@ import java.util.jar.*;
import java.util.logging.*;
import java.util.regex.*;
+import javax.annotation.Nullable;
+
import net.hydromatic.linq4j.Ord;
+import com.google.common.base.Function;
import com.google.common.cache.CacheBuilder;
import com.google.common.cache.CacheLoader;
import com.google.common.cache.LoadingCache;
+import com.google.common.collect.Collections2;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
@@ -2102,6 +2106,46 @@ public class Util {
}
}
+ /** Returns a map that is a view onto a collection of values, using the
+ * provided function to convert a value to a key.
+ *
+ * <p>Unlike
+ * {@link com.google.common.collect.Maps#uniqueIndex(Iterable, com.google.common.base.Function)},
+ * returns a view whose contents change as the collection of values changes.
+ *
+ * @param values Collection of values
+ * @param function Function to map value to key
+ * @param <K> Key type
+ * @param <V> Value type
+ * @return Map that is a view onto the values
+ */
+ public static <K, V> Map<K, V> asIndexMap(
+ final Collection<V> values,
+ final Function<V, K> function) {
+ final Collection<Map.Entry<K, V>> entries =
+ Collections2.transform(values,
+ new Function<V, Map.Entry<K, V>>() {
+ public Map.Entry<K, V> apply(@Nullable V input) {
+ return Pair.of(function.apply(input), input);
+ }
+ });
+ final Set<Map.Entry<K, V>> entrySet =
+ new AbstractSet<Map.Entry<K, V>>() {
+ public Iterator<Map.Entry<K, V>> iterator() {
+ return entries.iterator();
+ }
+
+ public int size() {
+ return entries.size();
+ }
+ };
+ return new AbstractMap<K, V>() {
+ public Set<Entry<K, V>> entrySet() {
+ return entrySet;
+ }
+ };
+ }
+
//~ Inner Classes ----------------------------------------------------------
/**
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java b/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
index e840e91..53e5cdc 100644
--- a/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
+++ b/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
@@ -4053,9 +4053,11 @@ public class JdbcTest {
}
@Test public void testNotInEmptyQuery() {
- // RHS is empty, therefore returns all rows from emp
+ // RHS is empty, therefore returns all rows from emp, including the one
+ // with deptno = NULL.
checkOuter("select deptno from emp where deptno not in (\n"
+ "select deptno from dept where deptno = -1)",
+ "DEPTNO=null",
"DEPTNO=10",
"DEPTNO=10",
"DEPTNO=20",
@@ -4067,6 +4069,7 @@ public class JdbcTest {
}
@Test public void testNotInQuery() {
+ // None of the rows from RHS is NULL.
checkOuter("select deptno from emp where deptno not in (\n"
+ "select deptno from dept)",
"DEPTNO=50",
@@ -4075,8 +4078,8 @@ public class JdbcTest {
}
@Test public void testNotInQueryWithNull() {
- // There is a NULL on the RHS, and '10 not in (20, null)' yields null,
- // so no rows are returned.
+ // There is a NULL on the RHS, and '10 not in (20, null)' yields unknown
+ // (similarly for every other value of deptno), so no rows are returned.
checkOuter("select deptno from emp where deptno not in (\n"
+ "select deptno from emp)");
}
@@ -4119,6 +4122,17 @@ public class JdbcTest {
"empid=110; deptno=10; name=Theodore; salary=11500.0; commission=250");
}
+ @Test public void testNotExistsCorrelated() {
+ OptiqAssert.that()
+ .with(OptiqAssert.Config.REGULAR)
+ .query(
+ "select * from \"hr\".\"emps\" where not exists (\n"
+ + " select 1 from \"hr\".\"depts\"\n"
+ + " where \"emps\".\"deptno\"=\"depts\".\"deptno\")")
+ .returnsUnordered(
+ "empid=200; deptno=20; name=Eric; salary=8000.0; commission=500");
+ }
+
/** Test case for
* <a href="https://issues.apache.org/jira/browse/OPTIQ-313">OPTIQ-313</a>,
* "Query decorrelation fails". */
@@ -4269,6 +4283,11 @@ public class JdbcTest {
checkRun("sql/outer.oq");
}
+ @Ignore
+ @Test public void testRunFoo() throws Exception {
+ checkRun("/tmp/foo.oq");
+ }
+
@Test public void testRunWinAgg() throws Exception {
checkRun("sql/winagg.oq");
}
@@ -4277,17 +4296,30 @@ public class JdbcTest {
checkRun("sql/misc.oq");
}
+ @Test public void testRunSubquery() throws Exception {
+ checkRun("sql/subquery.oq");
+ }
+
private void checkRun(String path) throws Exception {
- // e.g. "file:/home/fred/optiq/core/target/test-classes/sql/outer.oq"
- final URL inUrl = JdbcTest.class.getResource("/" + path);
- String x = inUrl.getFile();
- assert x.endsWith(path);
- x = x.substring(0, x.length() - path.length());
- assert x.endsWith("/test-classes/");
- x = x.substring(0, x.length() - "/test-classes/".length());
- final File base = new File(x);
- final File inFile = new File(base, "/test-classes/" + path);
- final File outFile = new File(base, "/surefire/" + path);
+ final File inFile;
+ final File outFile;
+ if (path.startsWith("/")) {
+ // e.g. path = "/tmp/foo.oq"
+ inFile = new File(path);
+ outFile = new File(path + ".out");
+ } else {
+ // e.g. path = "sql/outer.oq"
+ // inUrl = "file:/home/fred/optiq/core/target/test-classes/sql/outer.oq"
+ final URL inUrl = JdbcTest.class.getResource("/" + path);
+ String x = inUrl.getFile();
+ assert x.endsWith(path);
+ x = x.substring(0, x.length() - path.length());
+ assert x.endsWith("/test-classes/");
+ x = x.substring(0, x.length() - "/test-classes/".length());
+ final File base = new File(x);
+ inFile = new File(base, "/test-classes/" + path);
+ outFile = new File(base, "/surefire/" + path);
+ }
outFile.getParentFile().mkdirs();
final FileReader fileReader = new FileReader(inFile);
final BufferedReader bufferedReader = new BufferedReader(fileReader);
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/test/java/org/eigenbase/test/SqlToRelConverterTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/eigenbase/test/SqlToRelConverterTest.java b/core/src/test/java/org/eigenbase/test/SqlToRelConverterTest.java
index 218859f..26cdd2a 100644
--- a/core/src/test/java/org/eigenbase/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/eigenbase/test/SqlToRelConverterTest.java
@@ -466,10 +466,10 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
@Test public void testWithInsideWhereExists() {
tester.withDecorrelation(false).assertConvertsTo("select * from emp\n"
- + "where exists (\n"
- + " with dept2 as (select * from dept where dept.deptno >= emp.deptno)\n"
- + " select 1 from dept2 where deptno <= emp.deptno)",
- "${plan}");
+ + "where exists (\n"
+ + " with dept2 as (select * from dept where dept.deptno >= emp.deptno)\n"
+ + " select 1 from dept2 where deptno <= emp.deptno)",
+ "${plan}");
}
@Test public void testWithInsideWhereExistsDecorrelate() {
@@ -482,10 +482,10 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
@Test public void testWithInsideScalarSubquery() {
check("select (\n"
- + " with dept2 as (select * from dept where deptno > 10)"
- + " select count(*) from dept2) as c\n"
- + "from emp",
- "${plan}");
+ + " with dept2 as (select * from dept where deptno > 10)"
+ + " select count(*) from dept2) as c\n"
+ + "from emp",
+ "${plan}");
}
@Test public void testExplicitTable() {
@@ -551,8 +551,8 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
@Test public void testCollectionTableWithCursorParam() {
tester.withDecorrelation(false).assertConvertsTo(
"select * from table(dedup("
- + "cursor(select ename from emp),"
- + " cursor(select name from dept), 'NAME'))",
+ + "cursor(select ename from emp),"
+ + " cursor(select name from dept), 'NAME'))",
"${plan}");
}
@@ -648,6 +648,44 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
"${plan}");
}
+ @Test public void testNotInUncorrelatedSubquery() {
+ check(
+ "select empno from emp where deptno not in"
+ + " (select deptno from dept)",
+ "${plan}");
+ }
+
+ @Test public void testInUncorrelatedSubqueryInSelect() {
+ // In the SELECT clause, the value of IN remains in 3-valued logic
+ // -- it's not forced into 2-valued by the "... IS TRUE" wrapper as in the
+ // WHERE clause -- so the translation is more complicated.
+ check(
+ "select name, deptno in (\n"
+ + " select case when true then deptno else null end from emp)\n"
+ + "from dept",
+ "${plan}");
+ }
+
+ /** Plan should be as {@link #testInUncorrelatedSubqueryInSelect}, but with
+ * an extra NOT. Both queries require 3-valued logic. */
+ @Test public void testNotInUncorrelatedSubqueryInSelect() {
+ check(
+ "select empno, deptno not in (\n"
+ + " select case when true then deptno else null end from dept)\n"
+ + "from emp",
+ "${plan}");
+ }
+
+ /** Since 'deptno NOT IN (SELECT deptno FROM dept)' can not be null, we
+ * generate a simpler plan. */
+ @Test public void testNotInUncorrelatedSubqueryInSelectNotNull() {
+ check(
+ "select empno, deptno not in (\n"
+ + " select deptno from dept)\n"
+ + "from emp",
+ "${plan}");
+ }
+
@Test public void testUnnestSelect() {
check(
"select*from unnest(select multiset[deptno] from dept)",
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/793e5c4d/core/src/test/java/org/eigenbase/util/UtilTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/eigenbase/util/UtilTest.java b/core/src/test/java/org/eigenbase/util/UtilTest.java
index b5e6484..7328bc5 100644
--- a/core/src/test/java/org/eigenbase/util/UtilTest.java
+++ b/core/src/test/java/org/eigenbase/util/UtilTest.java
@@ -23,6 +23,8 @@ import java.sql.Timestamp;
import java.text.MessageFormat;
import java.util.*;
+import javax.annotation.Nullable;
+
import org.eigenbase.resource.Resources;
import org.eigenbase.sql.*;
import org.eigenbase.sql.util.*;
@@ -36,10 +38,12 @@ import net.hydromatic.optiq.util.BitSets;
import net.hydromatic.optiq.util.Compatible;
import net.hydromatic.optiq.util.CompositeMap;
+import com.google.common.base.Function;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMultiset;
import com.google.common.collect.ImmutableSortedSet;
import com.google.common.collect.Iterables;
+import com.google.common.collect.Lists;
import org.junit.BeforeClass;
import org.junit.Test;
@@ -396,14 +400,14 @@ public class UtilTest {
assertThat(Util.toLinux(diff),
equalTo(
"1a2\n"
- + "> (they call her \"Polythene Pam\")\n"
- + "3c4,5\n"
- + "< She's the kind of a girl that makes The News of The World\n"
- + "---\n"
- + "> She's the kind of a girl that makes The Sunday Times\n"
- + "> seem more interesting.\n"
- + "5d6\n"
- + "< Yeah yeah yeah.\n"));
+ + "> (they call her \"Polythene Pam\")\n"
+ + "3c4,5\n"
+ + "< She's the kind of a girl that makes The News of The World\n"
+ + "---\n"
+ + "> She's the kind of a girl that makes The Sunday Times\n"
+ + "> seem more interesting.\n"
+ + "5d6\n"
+ + "< Yeah yeah yeah.\n"));
}
/**
@@ -973,18 +977,18 @@ public class UtilTest {
map.put("nullValue", null);
assertEquals(
"{\n"
- + " foo: 1,\n"
- + " baz: true,\n"
- + " bar: \"can't\",\n"
- + " list: [\n"
- + " 2,\n"
- + " 3,\n"
- + " [],\n"
- + " {},\n"
- + " null\n"
- + " ],\n"
- + " nullValue: null\n"
- + "}",
+ + " foo: 1,\n"
+ + " baz: true,\n"
+ + " bar: \"can't\",\n"
+ + " list: [\n"
+ + " 2,\n"
+ + " 3,\n"
+ + " [],\n"
+ + " {},\n"
+ + " null\n"
+ + " ],\n"
+ + " nullValue: null\n"
+ + "}",
builder.toJsonString(map));
}
@@ -1307,6 +1311,27 @@ public class UtilTest {
assertThat(Util.human(0.00000181111D), equalTo("1.81111E-6"));
}
+
+ @Test public void testAsIndexView() {
+ final List<String> values = Lists.newArrayList("abCde", "X", "y");
+ final Map<String, String> map = Util.asIndexMap(values,
+ new Function<String, String>() {
+ public String apply(@Nullable String input) {
+ return input.toUpperCase();
+ }
+ });
+ assertThat(map.size(), equalTo(values.size()));
+ assertThat(map.get("X"), equalTo("X"));
+ assertThat(map.get("Y"), equalTo("y"));
+ assertThat(map.get("y"), is((String) null));
+ assertThat(map.get("ABCDE"), equalTo("abCde"));
+
+ // If you change the values collection, the map changes.
+ values.remove(1);
+ assertThat(map.size(), equalTo(values.size()));
+ assertThat(map.get("X"), is((String) null));
+ assertThat(map.get("Y"), equalTo("y"));
+ }
}
// End UtilTest.java
[4/4] git commit: SqlRun: Carry on after certain errors (e.g.
AssertionError). Add 'if (true)' command.
Posted by jh...@apache.org.
SqlRun: Carry on after certain errors (e.g. AssertionError). Add 'if (true)' command.
Project: http://git-wip-us.apache.org/repos/asf/incubator-optiq/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-optiq/commit/f67972b3
Tree: http://git-wip-us.apache.org/repos/asf/incubator-optiq/tree/f67972b3
Diff: http://git-wip-us.apache.org/repos/asf/incubator-optiq/diff/f67972b3
Branch: refs/heads/master
Commit: f67972b3af8f36086936835249c270bad51331c2
Parents: 582be2a
Author: Julian Hyde <jh...@apache.org>
Authored: Wed Aug 27 18:21:25 2014 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Wed Aug 27 18:22:14 2014 -0700
----------------------------------------------------------------------
.../java/net/hydromatic/optiq/tools/SqlRun.java | 67 ++++++++++++++++----
.../net/hydromatic/optiq/tools/SqlRunTest.java | 24 ++++++-
2 files changed, 76 insertions(+), 15 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f67972b3/core/src/main/java/net/hydromatic/optiq/tools/SqlRun.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/tools/SqlRun.java b/core/src/main/java/net/hydromatic/optiq/tools/SqlRun.java
index 57e3bcb..edac068 100644
--- a/core/src/main/java/net/hydromatic/optiq/tools/SqlRun.java
+++ b/core/src/main/java/net/hydromatic/optiq/tools/SqlRun.java
@@ -102,6 +102,9 @@ public class SqlRun {
} catch (Exception e) {
throw new RuntimeException(
"Error while executing command " + command, e);
+ } catch (AssertionError e) {
+ throw new RuntimeException(
+ "Error while executing command " + command, e);
}
} finally {
printWriter.flush();
@@ -135,11 +138,9 @@ public class SqlRun {
private static CharSequence chars(final char c, final int length) {
return new CharSequence() {
@Override public String toString() {
- final StringBuilder buf = new StringBuilder();
- for (int i = 0; i < length; i++) {
- buf.append(c);
- }
- return buf.toString();
+ final char[] chars = new char[length];
+ Arrays.fill(chars, c);
+ return new String(chars);
}
public int length() {
@@ -217,7 +218,13 @@ public class SqlRun {
List<String> ifLines = ImmutableList.copyOf(lines);
lines.clear();
Command command = new Parser().parse();
- return new IfCommand(ifLines, lines, command);
+ return new IfCommand(ifLines, lines, command, false);
+ }
+ if (line.equals("if (true) {")) {
+ List<String> ifLines = ImmutableList.copyOf(lines);
+ lines.clear();
+ Command command = new Parser().parse();
+ return new IfCommand(ifLines, lines, command, true);
}
if (line.equals("}")) {
return null;
@@ -501,6 +508,10 @@ public class SqlRun {
this.output = output;
}
+ @Override public String toString() {
+ return "CheckResultCommand [sql: " + sqlCommand.sql + "]";
+ }
+
public void execute(boolean execute) throws Exception {
if (execute) {
if (connection == null) {
@@ -512,7 +523,7 @@ public class SqlRun {
}
try {
if (OptiqPrepareImpl.DEBUG) {
- System.out.println("sql=" + sqlCommand.sql);
+ System.out.println("execute: " + this);
}
resultSet = null;
resultSetException = null;
@@ -593,6 +604,10 @@ public class SqlRun {
this.content = content;
}
+ @Override public String toString() {
+ return "ExplainCommand [sql: " + sqlCommand.sql + "]";
+ }
+
public void execute(boolean execute) throws Exception {
if (execute) {
final Statement statement = connection.createStatement();
@@ -676,9 +691,11 @@ public class SqlRun {
private final List<String> ifLines;
private final List<String> endLines;
private final Command command;
+ private final boolean enable;
- public IfCommand(List<String> ifLines,
- List<String> endLines, Command command) {
+ public IfCommand(List<String> ifLines, List<String> endLines,
+ Command command, boolean enable) {
+ this.enable = enable;
this.ifLines = ImmutableList.copyOf(ifLines);
this.endLines = ImmutableList.copyOf(endLines);
this.command = command;
@@ -688,10 +705,13 @@ public class SqlRun {
echo(ifLines);
// Switch to a mode where we don't execute, just echo.
boolean oldExecute = SqlRun.this.execute;
- boolean newExecute = execute;
- if (!skip) {
- // If "skip" is set, stay in the current mode.
- newExecute = false;
+ boolean newExecute;
+ if (skip) {
+ // If "skip" is set, stay in current (disabled) mode.
+ newExecute = oldExecute;
+ } else {
+ // If "enable" is true, stay in the current mode.
+ newExecute = enable;
}
command.execute(newExecute);
echo(endLines);
@@ -723,13 +743,32 @@ public class SqlRun {
}
public void execute(boolean execute) throws Exception {
+ // We handle all RuntimeExceptions, all Exceptions, and a limited number
+ // of Errors. If we don't understand an Error (e.g. OutOfMemoryError)
+ // then we print it out, then abort.
for (Command command : commands) {
+ boolean abort = false;
+ Throwable e = null;
try {
command.execute(execute);
- } catch (Exception e) {
+ } catch (RuntimeException e0) {
+ e = e0;
+ } catch (Exception e0) {
+ e = e0;
+ } catch (AssertionError e0) {
+ // We handle a limited number of errors.
+ e = e0;
+ } catch (Throwable e0) {
+ e = e0;
+ abort = true;
+ }
+ if (e != null) {
command.execute(false); // echo the command
printWriter.println("Error while executing command " + command);
e.printStackTrace(printWriter);
+ if (abort) {
+ throw (Error) e;
+ }
}
}
}
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f67972b3/core/src/test/java/net/hydromatic/optiq/tools/SqlRunTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/net/hydromatic/optiq/tools/SqlRunTest.java b/core/src/test/java/net/hydromatic/optiq/tools/SqlRunTest.java
index 4f42608..cba84a5 100644
--- a/core/src/test/java/net/hydromatic/optiq/tools/SqlRunTest.java
+++ b/core/src/test/java/net/hydromatic/optiq/tools/SqlRunTest.java
@@ -179,7 +179,7 @@ public class SqlRunTest {
+ "\n"));
}
- @Test public void testPlanDisabled() {
+ @Test public void testIfFalse() {
check(
"!use foodmart\n"
+ "!if (false) {\n"
@@ -200,6 +200,28 @@ public class SqlRunTest {
+ "\n"));
}
+ @Test public void testIfTrue() {
+ check(
+ "!use foodmart\n"
+ + "!if (true) {\n"
+ + "values (1), (2);\n"
+ + "anything\n"
+ + "you like\n"
+ + "!ok\n"
+ + "!}\n"
+ + "\n",
+ containsString(
+ "!use foodmart\n"
+ + "!if (true) {\n"
+ + "values (1), (2);\n"
+ + "EXPR$0\n"
+ + "1\n"
+ + "2\n"
+ + "!ok\n"
+ + "!}\n"
+ + "\n"));
+ }
+
static void check(String input, String expected) {
check(input, CoreMatchers.equalTo(expected));
}