This is an automated email from the ASF dual-hosted git repository.
xiong pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 84ea4be50b [CALCITE-4758] When SOME sub-query is SqlNodeList and
converted to VALUES, Calcite returns incorrect result
84ea4be50b is described below
commit 84ea4be50b01cd9539cb555c298c21172089249b
Author: Xiong Duan <[email protected]>
AuthorDate: Mon Nov 18 09:32:04 2024 +0800
[CALCITE-4758] When SOME sub-query is SqlNodeList and converted to VALUES,
Calcite returns incorrect result
---
.../apache/calcite/sql2rel/SqlToRelConverter.java | 76 +++++++++-----
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 44 +++-----
.../org/apache/calcite/test/RelOptRulesTest.java | 5 +
core/src/test/resources/sql/some.iq | 86 +++++++++++++++
core/src/test/resources/sql/sub-query.iq | 115 +++++++++++----------
5 files changed, 220 insertions(+), 106 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 571d6abc50..89516750f3 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -1207,9 +1207,7 @@ public class SqlToRelConverter {
case ALL:
call = (SqlBasicCall) subQuery.node;
query = call.operand(1);
- if (!config.isExpand() && !(query instanceof SqlNodeList)) {
- return;
- }
+
final SqlNode leftKeyNode = call.operand(0);
final List<SqlNode> leftSqlKeys;
@@ -1241,6 +1239,31 @@ public class SqlToRelConverter {
// reference to Q below.
}
+ final RelDataType targetRowType =
+ promoteToRowType(typeFactory,
+ validator().getValidatedNodeType(leftKeyNode), null);
+
+ if (!config.isExpand()) {
+ if (query instanceof SqlNodeList) {
+ // convert
+ // select * from "scott".emp where sal > some (4000, 2000)
+ // to
+ // select * from "scott".emp where sal > some (VALUES (4000), (2000))
+ // The SqlNodeList become a RexSubQuery then optimized by
SubQueryRemoveRule.
+ RelNode relNode = convertRowValues(bb, query, (SqlNodeList) query,
false, targetRowType);
+ final ImmutableList.Builder<RexNode> builder =
+ ImmutableList.builder();
+ for (SqlNode node : leftSqlKeys) {
+ builder.add(bb.convertExpression(node));
+ }
+ final ImmutableList<RexNode> list = builder.build();
+ assert relNode != null;
+ subQuery.expr = createSubquery(subQuery.node.getKind(), relNode,
list, call);
+ return;
+ }
+ return;
+ }
+
final List<RexNode> leftKeys = leftSqlKeys.stream()
.map(bb::convertExpression)
.collect(toImmutableList());
@@ -1278,9 +1301,7 @@ public class SqlToRelConverter {
if (bb.root == null) {
return;
}
- final RelDataType targetRowType =
- promoteToRowType(typeFactory,
- validator().getValidatedNodeType(leftKeyNode), null);
+
final boolean notIn = call.getOperator().kind == SqlKind.NOT_IN;
converted =
convertExists(query, RelOptUtil.SubQueryType.IN, subQuery.logic,
@@ -5625,23 +5646,7 @@ public class SqlToRelConverter {
if (correlationUse != null) {
rel = correlationUse.r;
}
-
- switch (kind) {
- case IN:
- return RexSubQuery.in(rel, list);
- case NOT_IN:
- return rexBuilder.makeCall(SqlStdOperatorTable.NOT,
- RexSubQuery.in(rel, list));
- case SOME:
- return RexSubQuery.some(rel, list,
- (SqlQuantifyOperator) call.getOperator());
- case ALL:
- return rexBuilder.makeCall(SqlStdOperatorTable.NOT,
- RexSubQuery.some(rel, list,
- negate((SqlQuantifyOperator) call.getOperator())));
- default:
- throw new AssertionError(kind);
- }
+ return createSubquery(kind, rel, list, call);
}
break;
@@ -5994,6 +5999,31 @@ public class SqlToRelConverter {
}
}
+ /**
+ * Creates an sub-query of type {@code IN, NOT IN, SOME, or ALL}.
+ */
+ private RexNode createSubquery(SqlKind kind,
+ RelNode rel, ImmutableList<RexNode> list, @Nullable SqlCall call) {
+ switch (kind) {
+ case IN:
+ return RexSubQuery.in(rel, list);
+ case NOT_IN:
+ return rexBuilder.makeCall(SqlStdOperatorTable.NOT,
+ RexSubQuery.in(rel, list));
+ case SOME:
+ assert call != null;
+ return RexSubQuery.some(rel, list,
+ (SqlQuantifyOperator) call.getOperator());
+ case ALL:
+ assert call != null;
+ return rexBuilder.makeCall(SqlStdOperatorTable.NOT,
+ RexSubQuery.some(rel, list,
+ negate((SqlQuantifyOperator) call.getOperator())));
+ default:
+ throw new AssertionError(kind);
+ }
+ }
+
private static SqlQuantifyOperator negate(SqlQuantifyOperator operator) {
assert operator.kind == SqlKind.ALL;
return SqlStdOperatorTable.some(operator.comparisonKind.negateNullSafe());
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index e2730583b0..a9701591a9 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -4751,49 +4751,33 @@ class RelToSqlConverterTest {
@Test void convertInListToValues1() {
String query = "select \"product_id\" from \"product\"\n"
+ "where \"product_id\" in (12, null)";
- String expected = "SELECT \"product\".\"product_id\"\n"
+ String expected = "SELECT \"product_id\"\n"
+ "FROM \"foodmart\".\"product\"\n"
- + "INNER JOIN (SELECT \"ROW_VALUE\"\n"
- + "FROM (VALUES (12),\n(NULL)) AS \"t\" (\"ROW_VALUE\")\n"
- + "GROUP BY \"ROW_VALUE\") AS \"t0\" ON \"product\".\"product_id\" =
\"t0\".\"ROW_VALUE\"";
+ + "WHERE \"product_id\" IN (SELECT *\n"
+ + "FROM (VALUES (12),\n"
+ + "(NULL)) AS \"t\" (\"ROW_VALUE\"))";
sql(query).withConfig(c -> c.withInSubQueryThreshold(1)).ok(expected);
}
@Test void convertInListToValues2() {
String query = "select \"brand_name\" from \"product\"\n"
+ "where cast(\"brand_name\" as char) in ('n', null)";
- String expected = "SELECT \"t\".\"brand_name\"\n"
- + "FROM (SELECT \"product_class_id\", \"product_id\","
- + " \"brand_name\", \"product_name\","
- + " \"SKU\", \"SRP\", \"gross_weight\","
- + " \"net_weight\", \"recyclable_package\","
- + " \"low_fat\", \"units_per_case\","
- + " \"cases_per_pallet\", \"shelf_width\","
- + " \"shelf_height\", \"shelf_depth\","
- + " CAST(\"brand_name\" AS CHAR(1) CHARACTER SET \"ISO-8859-1\") AS
\"brand_name0\"\n"
- + "FROM \"foodmart\".\"product\") AS \"t\"\n"
- + "INNER JOIN (SELECT \"ROW_VALUE\"\n"
- + "FROM (VALUES ('n'),\n(NULL)) AS \"t0\" (\"ROW_VALUE\")\n"
- + "GROUP BY \"ROW_VALUE\") AS \"t1\" ON \"t\".\"brand_name0\" =
\"t1\".\"ROW_VALUE\"";
+ String expected = "SELECT \"brand_name\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "WHERE CAST(\"brand_name\" AS CHAR(1) CHARACTER SET \"ISO-8859-1\")
IN (SELECT *\n"
+ + "FROM (VALUES ('n'),\n"
+ + "(NULL)) AS \"t\" (\"ROW_VALUE\"))";
sql(query).withConfig(c -> c.withInSubQueryThreshold(1)).ok(expected);
}
@Test void convertInListToValues3() {
String query = "select \"brand_name\" from \"product\"\n"
+ "where (\"brand_name\" = \"product_name\") in (false, null)";
- String expected = "SELECT \"t\".\"brand_name\"\n"
- + "FROM (SELECT \"product_class_id\", \"product_id\","
- + " \"brand_name\", \"product_name\","
- + " \"SKU\", \"SRP\", \"gross_weight\","
- + " \"net_weight\", \"recyclable_package\","
- + " \"low_fat\", \"units_per_case\","
- + " \"cases_per_pallet\", \"shelf_width\","
- + " \"shelf_height\", \"shelf_depth\","
- + " \"brand_name\" = \"product_name\" AS \"$f15\"\n"
- + "FROM \"foodmart\".\"product\") AS \"t\"\n"
- + "INNER JOIN (SELECT \"ROW_VALUE\"\n"
- + "FROM (VALUES (FALSE),\n(NULL)) AS \"t0\" (\"ROW_VALUE\")\n"
- + "GROUP BY \"ROW_VALUE\") AS \"t1\" ON \"t\".\"$f15\" =
\"t1\".\"ROW_VALUE\"";
+ String expected = "SELECT \"brand_name\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "WHERE (\"brand_name\" = \"product_name\") IN (SELECT *\n"
+ + "FROM (VALUES (FALSE),\n"
+ + "(NULL)) AS \"t\" (\"ROW_VALUE\"))";
sql(query).withConfig(c -> c.withInSubQueryThreshold(1)).ok(expected);
}
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index cca3a70634..8b57500eec 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -3148,6 +3148,7 @@ class RelOptRulesTest extends RelOptTestBase {
+ "from t1\n"
+ "where (t1.a, t1.y) in ((1, 2), (3, null), (7369, null), (7499, 30),
(null, 20), (null, 5))";
sql(sql)
+ .withExpand(true)
.withRelBuilderConfig(b -> b.withSimplifyValues(false))
.withRule(CoreRules.PROJECT_VALUES_MERGE,
CoreRules.UNION_TO_VALUES)
@@ -3163,6 +3164,7 @@ class RelOptRulesTest extends RelOptTestBase {
+ "from t1\n"
+ "where (t1.a, t1.y) in ((cast(1.1 as int), 2), (3, null), (7369,
null), (7499, 30), (null, cast(20.2 as int)), (null, 5))";
sql(sql)
+ .withExpand(true)
.withRelBuilderConfig(b -> b.withSimplifyValues(false))
.withRule(CoreRules.PROJECT_VALUES_MERGE,
CoreRules.UNION_TO_VALUES)
@@ -3173,6 +3175,7 @@ class RelOptRulesTest extends RelOptTestBase {
@Test void testUnionToValuesByInList3() {
final String sql = "select * from dept where deptno in (12, 34, cast(56.4
as int))";
sql(sql)
+ .withExpand(true)
.withRelBuilderConfig(b -> b.withSimplifyValues(false))
.withRule(CoreRules.PROJECT_VALUES_MERGE,
CoreRules.UNION_TO_VALUES)
@@ -3183,6 +3186,7 @@ class RelOptRulesTest extends RelOptTestBase {
@Test void testUnionToValuesByInList4() {
final String sql = "select * from dept where deptno in (12, 34, cast(56.4
as double))";
sql(sql)
+ .withExpand(true)
.withRelBuilderConfig(b -> b.withSimplifyValues(false))
.withRule(CoreRules.PROJECT_VALUES_MERGE,
CoreRules.UNION_TO_VALUES)
@@ -3193,6 +3197,7 @@ class RelOptRulesTest extends RelOptTestBase {
@Test void testUnionToValuesByInList5() {
final String sql = "select deptno in (12, 34, cast(56.4 as double)) from
dept";
sql(sql)
+ .withExpand(true)
.withRelBuilderConfig(b -> b.withSimplifyValues(false))
.withRule(CoreRules.PROJECT_VALUES_MERGE,
CoreRules.UNION_TO_VALUES)
diff --git a/core/src/test/resources/sql/some.iq
b/core/src/test/resources/sql/some.iq
index d8ce6cdb7a..4cb8692a17 100644
--- a/core/src/test/resources/sql/some.iq
+++ b/core/src/test/resources/sql/some.iq
@@ -847,5 +847,91 @@ where sal > all (select comm from "scott".emp where comm
is not null);
!ok
+# [CALCITE-4758] When SOME sub-query is SqlNodeList and converted to VALUES,
Calcite returns incorrect result
+
+# make sure the SqlNodeList converted to VALUES not OR condition
+!set insubquerythreshold 0
+
+select * from "scott".emp
+where sal > all(500, 2000);
++-------+-------+-----------+------+------------+---------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-----------+------+------------+---------+------+--------+
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 |
+| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 |
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 |
+| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
+| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 |
++-------+-------+-----------+------+------------+---------+------+--------+
+(6 rows)
+
+!ok
+
+select * from "scott".emp
+where sal > all (4000, 2000);
++-------+-------+-----------+-----+------------+---------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-----------+-----+------------+---------+------+--------+
+| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
++-------+-------+-----------+-----+------------+---------+------+--------+
+(1 row)
+
+!ok
+
+select * from "scott".emp
+where sal > some (4000, 2000);
++-------+-------+-----------+------+------------+---------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-----------+------+------------+---------+------+--------+
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 |
+| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 |
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 |
+| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
+| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 |
++-------+-------+-----------+------+------------+---------+------+--------+
+(6 rows)
+
+!ok
+
+select sal, sal > some (4000, 2000, null) from "scott".emp;
++---------+--------+
+| SAL | EXPR$1 |
++---------+--------+
+| 1100.00 | |
+| 1250.00 | |
+| 1250.00 | |
+| 1300.00 | |
+| 1500.00 | |
+| 1600.00 | |
+| 2450.00 | true |
+| 2850.00 | true |
+| 2975.00 | true |
+| 3000.00 | true |
+| 3000.00 | true |
+| 5000.00 | true |
+| 800.00 | |
+| 950.00 | |
++---------+--------+
+(14 rows)
+
+!ok
+
+select * from "scott".emp
+where sal > any (4000, 2000);
++-------+-------+-----------+------+------------+---------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-----------+------+------------+---------+------+--------+
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 |
+| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 |
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 |
+| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
+| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 |
++-------+-------+-----------+------+------------+---------+------+--------+
+(6 rows)
+
+!ok
# End some.iq
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 7b42a78681..dc06578779 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -3448,13 +3448,13 @@ select * from "scott".emp where empno not in (null,
7782);
!ok
-EnumerableCalc(expr#0..12=[{inputs}], expr#13=[0:BIGINT], expr#14=[=($t8,
$t13)], expr#15=[IS NULL($t12)], expr#16=[>=($t9, $t8)], expr#17=[AND($t15,
$t16)], expr#18=[OR($t14, $t17)], proj#0..7=[{exprs}], $condition=[$t18])
+EnumerableCalc(expr#0..12=[{inputs}], expr#13=[0], expr#14=[=($t8, $t13)],
expr#15=[IS NULL($t12)], expr#16=[>=($t9, $t8)], expr#17=[AND($t15, $t16)],
expr#18=[OR($t14, $t17)], proj#0..7=[{exprs}], $condition=[$t18])
EnumerableMergeJoin(condition=[=($10, $11)], joinType=[left])
EnumerableSort(sort0=[$10], dir0=[ASC])
EnumerableCalc(expr#0..9=[{inputs}], expr#10=[CAST($t0):INTEGER NOT
NULL], proj#0..10=[{exprs}])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
+ EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
EnumerableValues(tuples=[[{ null }, { 7782 }]])
EnumerableSort(sort0=[$0], dir0=[ASC])
EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
@@ -3470,14 +3470,14 @@ select * from "scott".emp where (empno, deptno) not in
((1, 2), (3, null));
!ok
-EnumerableCalc(expr#0..14=[{inputs}], expr#15=[0:BIGINT], expr#16=[=($t8,
$t15)], expr#17=[IS NULL($t14)], expr#18=[>=($t9, $t8)], expr#19=[IS NOT
NULL($t11)], expr#20=[AND($t17, $t18, $t19)], expr#21=[OR($t16, $t20)],
proj#0..7=[{exprs}], $condition=[$t21])
+EnumerableCalc(expr#0..14=[{inputs}], expr#15=[0], expr#16=[=($t8, $t15)],
expr#17=[IS NULL($t14)], expr#18=[>=($t9, $t8)], expr#19=[IS NOT NULL($t7)],
expr#20=[AND($t17, $t18, $t19)], expr#21=[OR($t16, $t20)], proj#0..7=[{exprs}],
$condition=[$t21])
EnumerableMergeJoin(condition=[AND(=($10, $12), =($11, $13))],
joinType=[left])
EnumerableSort(sort0=[$10], sort1=[$11], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..9=[{inputs}], expr#10=[CAST($t0):INTEGER NOT
NULL], expr#11=[CAST($t7):INTEGER], proj#0..11=[{exprs}])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($1)])
- EnumerableValues(tuples=[[{ 3, null }, { 1, 2 }]])
+ EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
+ EnumerableValues(tuples=[[{ null }, { 2 }]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
EnumerableValues(tuples=[[{ 3, null }, { 1, 2 }]])
@@ -3504,11 +3504,15 @@ select * from "scott".emp where (empno, deptno) not in
((7369, 20), (7499, 30));
!ok
-EnumerableCalc(expr#0..12=[{inputs}], expr#13=[IS NOT TRUE($t12)], expr#14=[IS
NULL($t9)], expr#15=[OR($t13, $t14)], proj#0..7=[{exprs}], $condition=[$t15])
- EnumerableMergeJoin(condition=[AND(=($8, $10), =($9, $11))], joinType=[left])
- EnumerableSort(sort0=[$8], sort1=[$9], dir0=[ASC], dir1=[ASC])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t0):INTEGER NOT
NULL], expr#9=[CAST($t7):INTEGER], proj#0..9=[{exprs}])
- EnumerableTableScan(table=[[scott, EMP]])
+EnumerableCalc(expr#0..14=[{inputs}], expr#15=[0], expr#16=[=($t8, $t15)],
expr#17=[IS NULL($t14)], expr#18=[>=($t9, $t8)], expr#19=[IS NOT NULL($t7)],
expr#20=[AND($t17, $t18, $t19)], expr#21=[OR($t16, $t20)], proj#0..7=[{exprs}],
$condition=[$t21])
+ EnumerableMergeJoin(condition=[AND(=($10, $12), =($11, $13))],
joinType=[left])
+ EnumerableSort(sort0=[$10], sort1=[$11], dir0=[ASC], dir1=[ASC])
+ EnumerableCalc(expr#0..9=[{inputs}], expr#10=[CAST($t0):INTEGER NOT
NULL], expr#11=[CAST($t7):INTEGER], proj#0..11=[{exprs}])
+ EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0=[{inputs}], c=[$t0], ck=[$t0])
+ EnumerableAggregate(group=[{}], c=[COUNT()])
+ EnumerableValues(tuples=[[{ 7369, 20 }, { 7499, 30 }]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
EnumerableValues(tuples=[[{ 7369, 20 }, { 7499, 30 }]])
@@ -3898,16 +3902,10 @@ select empno, empno in (7369, 7499, 7521) from emp;
!ok
-EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t5)],
expr#7=[0:BIGINT], expr#8=[<>($t1, $t7)], expr#9=[AND($t6, $t8)],
expr#10=[<($t2, $t1)], expr#11=[null:BOOLEAN], expr#12=[IS NULL($t5)],
expr#13=[AND($t10, $t11, $t8, $t12)], expr#14=[OR($t9, $t13)],
expr#15=[CAST($t14):BOOLEAN NOT NULL], EMPNO=[$t0], EXPR$1=[$t15])
- EnumerableMergeJoin(condition=[=($3, $4)], joinType=[left])
- EnumerableSort(sort0=[$3], dir0=[ASC])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t2):INTEGER NOT
NULL], EMPNO=[$t2], $f0=[$t0], $f1=[$t1], EMPNO0=[$t3])
- EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
- EnumerableCalc(expr#0=[{inputs}], $f0=[$t0], $f00=[$t0])
- EnumerableAggregate(group=[{}], agg#0=[COUNT()])
- EnumerableValues(tuples=[[{ 7369 }, { 7499 }, { 7521 }]])
- EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
- EnumerableTableScan(table=[[scott, EMP]])
+EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NOT NULL($t3)], EMPNO=[$t0],
EXPR$1=[$t4])
+ EnumerableMergeJoin(condition=[=($1, $2)], joinType=[left])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t0):INTEGER NOT NULL],
EMPNO=[$t0], EMPNO0=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
EnumerableValues(tuples=[[{ 7369 }, { 7499 }, { 7521 }]])
!plan
@@ -3936,15 +3934,15 @@ select comm, comm in (500, 300, 0) from emp;
!ok
-EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t5)],
expr#7=[0:BIGINT], expr#8=[<>($t1, $t7)], expr#9=[AND($t6, $t8)], expr#10=[IS
NULL($t3)], expr#11=[<($t2, $t1)], expr#12=[OR($t10, $t11)],
expr#13=[null:BOOLEAN], expr#14=[IS NULL($t5)], expr#15=[AND($t12, $t13, $t8,
$t14)], expr#16=[OR($t9, $t15)], COMM=[$t0], EXPR$1=[$t16])
- EnumerableMergeJoin(condition=[=($3, $4)], joinType=[left])
- EnumerableSort(sort0=[$3], dir0=[ASC])
- EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):DECIMAL(10, 2)],
COMM=[$t1], $f0=[$t2], $f1=[$t3], COMM0=[$t4])
+EnumerableCalc(expr#0..6=[{inputs}], expr#7=[0], expr#8=[=($t2, $t7)],
expr#9=[false], expr#10=[CAST($t1):DECIMAL(10, 2)], expr#11=[IS NULL($t10)],
expr#12=[null:BOOLEAN], expr#13=[IS NOT NULL($t6)], expr#14=[true],
expr#15=[<($t3, $t2)], expr#16=[CASE($t8, $t9, $t11, $t12, $t13, $t14, $t15,
$t12, $t9)], COMM=[$t1], EXPR$1=[$t16])
+ EnumerableMergeJoin(condition=[=($4, $5)], joinType=[left])
+ EnumerableSort(sort0=[$4], dir0=[ASC])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):DECIMAL(10, 2)],
proj#0..4=[{exprs}])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], COMM=[$t6])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0=[{inputs}], $f0=[$t0], $f00=[$t0])
- EnumerableAggregate(group=[{}], agg#0=[COUNT()])
+ EnumerableCalc(expr#0=[{inputs}], c=[$t0], ck=[$t0])
+ EnumerableAggregate(group=[{}], c=[COUNT()])
EnumerableValues(tuples=[[{ 500.00 }, { 300.00 }, { 0.00 }]])
EnumerableSort(sort0=[$0], dir0=[ASC])
EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
@@ -3976,14 +3974,14 @@ select comm, comm in (500, 300, 0, null) from emp;
!ok
-EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t5)],
expr#7=[0:BIGINT], expr#8=[<>($t1, $t7)], expr#9=[AND($t6, $t8)], expr#10=[IS
NULL($t3)], expr#11=[<($t2, $t1)], expr#12=[OR($t10, $t11)],
expr#13=[null:BOOLEAN], expr#14=[IS NULL($t5)], expr#15=[AND($t12, $t13, $t8,
$t14)], expr#16=[OR($t9, $t15)], COMM=[$t0], EXPR$1=[$t16])
- EnumerableMergeJoin(condition=[=($3, $4)], joinType=[left])
- EnumerableSort(sort0=[$3], dir0=[ASC])
- EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):DECIMAL(12, 2)],
COMM=[$t1], $f0=[$t2], $f1=[$t3], COMM0=[$t4])
+EnumerableCalc(expr#0..6=[{inputs}], expr#7=[0], expr#8=[=($t2, $t7)],
expr#9=[false], expr#10=[CAST($t1):DECIMAL(12, 2)], expr#11=[IS NULL($t10)],
expr#12=[null:BOOLEAN], expr#13=[IS NOT NULL($t6)], expr#14=[true],
expr#15=[<($t3, $t2)], expr#16=[CASE($t8, $t9, $t11, $t12, $t13, $t14, $t15,
$t12, $t9)], COMM=[$t1], EXPR$1=[$t16])
+ EnumerableMergeJoin(condition=[=($4, $5)], joinType=[left])
+ EnumerableSort(sort0=[$4], dir0=[ASC])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):DECIMAL(12, 2)],
proj#0..4=[{exprs}])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], COMM=[$t6])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
+ EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
EnumerableValues(tuples=[[{ 500.00 }, { 300.00 }, { 0.00 }, { null
}]])
EnumerableSort(sort0=[$0], dir0=[ASC])
EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
@@ -4015,16 +4013,10 @@ select empno, (empno, empno) in ((7369, 7369), (7499,
7499), (7521, 7521)) from
!ok
-EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)],
expr#9=[0:BIGINT], expr#10=[<>($t1, $t9)], expr#11=[AND($t8, $t10)],
expr#12=[<($t2, $t1)], expr#13=[null:BOOLEAN], expr#14=[IS NULL($t7)],
expr#15=[AND($t12, $t13, $t10, $t14)], expr#16=[OR($t11, $t15)],
expr#17=[CAST($t16):BOOLEAN NOT NULL], EMPNO=[$t0], EXPR$1=[$t17])
- EnumerableMergeJoin(condition=[AND(=($3, $5), =($4, $6))], joinType=[left])
- EnumerableSort(sort0=[$3], sort1=[$4], dir0=[ASC], dir1=[ASC])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t2):INTEGER NOT
NULL], EMPNO=[$t2], $f0=[$t0], $f1=[$t1], EMPNO0=[$t3], EMPNO1=[$t3])
- EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
- EnumerableCalc(expr#0=[{inputs}], $f0=[$t0], $f00=[$t0])
- EnumerableAggregate(group=[{}], agg#0=[COUNT()])
- EnumerableValues(tuples=[[{ 7369, 7369 }, { 7499, 7499 }, {
7521, 7521 }]])
- EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
- EnumerableTableScan(table=[[scott, EMP]])
+EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t5)], EMPNO=[$t0],
EXPR$1=[$t6])
+ EnumerableMergeJoin(condition=[AND(=($1, $3), =($2, $4))], joinType=[left])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t0):INTEGER NOT NULL],
EMPNO=[$t0], EMPNO0=[$t8], EMPNO1=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
EnumerableValues(tuples=[[{ 7369, 7369 }, { 7499, 7499 }, { 7521, 7521
}]])
@@ -4055,16 +4047,16 @@ select comm, (comm, comm) in ((500, 500), (300, 300),
(0, 0)) from emp;
!ok
-EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)],
expr#9=[0:BIGINT], expr#10=[<>($t1, $t9)], expr#11=[AND($t8, $t10)],
expr#12=[IS NULL($t3)], expr#13=[IS NULL($t4)], expr#14=[<($t2, $t1)],
expr#15=[OR($t12, $t13, $t14)], expr#16=[null:BOOLEAN], expr#17=[IS NULL($t7)],
expr#18=[AND($t15, $t16, $t10, $t17)], expr#19=[OR($t11, $t18)], COMM=[$t0],
EXPR$1=[$t19])
- EnumerableMergeJoin(condition=[AND(=($3, $5), =($4, $6))], joinType=[left])
- EnumerableSort(sort0=[$3], sort1=[$4], dir0=[ASC], dir1=[ASC])
- EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):DECIMAL(10, 2)],
COMM=[$t1], $f0=[$t2], $f1=[$t3], COMM0=[$t4], COMM1=[$t4])
+EnumerableCalc(expr#0..8=[{inputs}], expr#9=[0], expr#10=[=($t2, $t9)],
expr#11=[false], expr#12=[CAST($t1):DECIMAL(10, 2)], expr#13=[IS NULL($t12)],
expr#14=[null:BOOLEAN], expr#15=[IS NOT NULL($t8)], expr#16=[true],
expr#17=[<($t3, $t2)], expr#18=[CASE($t10, $t11, $t13, $t14, $t15, $t16, $t17,
$t14, $t11)], COMM=[$t1], EXPR$1=[$t18])
+ EnumerableMergeJoin(condition=[AND(=($4, $6), =($5, $7))], joinType=[left])
+ EnumerableSort(sort0=[$4], sort1=[$5], dir0=[ASC], dir1=[ASC])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):DECIMAL(10, 2)],
proj#0..4=[{exprs}], COMM1=[$t4])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], COMM=[$t6])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0=[{inputs}], $f0=[$t0], $f00=[$t0])
- EnumerableAggregate(group=[{}], agg#0=[COUNT()])
- EnumerableValues(tuples=[[{ 500.00, 500.00 }, { 300.00, 300.00
}, { 0.00, 0.00 }]])
+ EnumerableCalc(expr#0=[{inputs}], c=[$t0], ck=[$t0])
+ EnumerableAggregate(group=[{}], c=[COUNT()])
+ EnumerableValues(tuples=[[{ 500.00 }, { 300.00 }, { 0.00 }]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
EnumerableValues(tuples=[[{ 500.00, 500.00 }, { 300.00, 300.00 }, {
0.00, 0.00 }]])
@@ -4095,14 +4087,14 @@ select comm, (comm, comm) in ((500, 500), (300, 300),
(0, 0), (null , null)) fro
!ok
-EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)],
expr#9=[0:BIGINT], expr#10=[<>($t1, $t9)], expr#11=[AND($t8, $t10)],
expr#12=[IS NULL($t3)], expr#13=[IS NULL($t4)], expr#14=[<($t2, $t1)],
expr#15=[OR($t12, $t13, $t14)], expr#16=[null:BOOLEAN], expr#17=[IS NULL($t7)],
expr#18=[AND($t15, $t16, $t10, $t17)], expr#19=[OR($t11, $t18)], COMM=[$t0],
EXPR$1=[$t19])
- EnumerableMergeJoin(condition=[AND(=($3, $5), =($4, $6))], joinType=[left])
- EnumerableSort(sort0=[$3], sort1=[$4], dir0=[ASC], dir1=[ASC])
- EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):DECIMAL(10, 2)],
COMM=[$t1], $f0=[$t2], $f1=[$t3], COMM0=[$t4], COMM1=[$t4])
+EnumerableCalc(expr#0..8=[{inputs}], expr#9=[0], expr#10=[=($t2, $t9)],
expr#11=[false], expr#12=[CAST($t1):DECIMAL(10, 2)], expr#13=[IS NULL($t12)],
expr#14=[null:BOOLEAN], expr#15=[IS NOT NULL($t8)], expr#16=[true],
expr#17=[<($t3, $t2)], expr#18=[CASE($t10, $t11, $t13, $t14, $t15, $t16, $t17,
$t14, $t11)], COMM=[$t1], EXPR$1=[$t18])
+ EnumerableMergeJoin(condition=[AND(=($4, $6), =($5, $7))], joinType=[left])
+ EnumerableSort(sort0=[$4], sort1=[$5], dir0=[ASC], dir1=[ASC])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[CAST($t1):DECIMAL(10, 2)],
proj#0..4=[{exprs}], COMM1=[$t4])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], COMM=[$t6])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0,
$1)])
+ EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0, $1)])
EnumerableValues(tuples=[[{ 500.00, 500.00 }, { 300.00, 300.00 },
{ 0.00, 0.00 }, { null, null }]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
@@ -4397,4 +4389,21 @@ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[false],
EXPR$0=[$t2])
EnumerableValues(tuples=[[{ 10, 'Sales ' }, { 20, 'Marketing ' }, {
30, 'Engineering' }, { 40, 'Empty ' }]])
!plan
+# [CALCITE-4758] When SOME sub-query is SqlNodeList and converted to VALUES,
Calcite returns incorrect result
+
+# LHS include NULL value and RHS is not nullable
+select deptno from emp where deptno not in (50, 20);
++--------+
+| DEPTNO |
++--------+
+| 10 |
+| 30 |
+| 10 |
+| 30 |
+| 60 |
++--------+
+(5 rows)
+
+!ok
+
# End sub-query.iq
