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 8eb91fe0b6 [CALCITE-6506] Type inference for IN list is incorrect
8eb91fe0b6 is described below
commit 8eb91fe0b6da99552b3874040169dc728607ed78
Author: Xiong Duan <[email protected]>
AuthorDate: Fri Aug 2 08:45:19 2024 +0800
[CALCITE-6506] Type inference for IN list is incorrect
---
.../org/apache/calcite/sql/type/SqlTypeUtil.java | 4 +
.../apache/calcite/sql2rel/SqlToRelConverter.java | 7 +-
core/src/test/resources/sql/sub-query.iq | 238 +++++++++++++++++++++
3 files changed, 244 insertions(+), 5 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
index b7976bf304..6f9492119b 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
@@ -330,6 +330,10 @@ public abstract class SqlTypeUtil {
public static RelDataType keepSourceTypeAndTargetNullability(RelDataType
sourceRelDataType,
RelDataType targetRelDataType,
RelDataTypeFactory typeFactory) {
+ checkArgument(
+ (targetRelDataType.isStruct() && sourceRelDataType.isStruct())
+ || (!targetRelDataType.isStruct() &&
!sourceRelDataType.isStruct()),
+ "one is a struct, while the other one is not");
if (!targetRelDataType.isStruct()) {
return typeFactory.createTypeWithNullability(
sourceRelDataType, targetRelDataType.isNullable());
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 bb655abe25..75f94d6684 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -1901,14 +1901,11 @@ public class SqlToRelConverter {
final ImmutableList.Builder<ImmutableList<RexLiteral>> tupleList =
ImmutableList.builder();
final RelDataType listType = validator().getValidatedNodeType(rowList);
- final RelDataType rowType;
+ RelDataType rowType = promoteToRowType(typeFactory, listType, null);
if (targetRowType != null) {
rowType =
- keepSourceTypeAndTargetNullability(targetRowType, listType,
typeFactory);
- } else {
- rowType = promoteToRowType(typeFactory, listType, null);
+ keepSourceTypeAndTargetNullability(targetRowType, rowType,
typeFactory);
}
-
final List<RelNode> unionInputs = new ArrayList<>();
for (SqlNode node : rows) {
SqlBasicCall call;
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 2201dea8de..27e3321b84 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -3812,4 +3812,242 @@ WHERE s1.total > (SELECT avg(total) FROM agg_sal s2
WHERE s1.deptno = s2.deptno)
!ok
+# [CALCITE-6506] Type inference for IN list is incorrect
+
+# Test LHS is not nullable and RHS is not nullable
+select empno, empno in (7369, 7499, 7521) from emp;
++-------+--------+
+| EMPNO | EXPR$1 |
++-------+--------+
+| 7369 | true |
+| 7499 | true |
+| 7521 | true |
+| 7566 | false |
+| 7654 | false |
+| 7698 | false |
+| 7782 | false |
+| 7788 | false |
+| 7839 | false |
+| 7844 | false |
+| 7876 | false |
+| 7900 | false |
+| 7902 | false |
+| 7934 | false |
++-------+--------+
+(14 rows)
+
+!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}], EMPNO=[$t2], $f0=[$t0], $f1=[$t1],
EMPNO0=[$t2])
+ 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=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
+ EnumerableValues(tuples=[[{ 7369 }, { 7499 }, { 7521 }]])
+!plan
+
+# Test LHS is nullable and RHS is not nullable
+select comm, comm in (500, 300, 0) from emp;
++---------+--------+
+| COMM | EXPR$1 |
++---------+--------+
+| 0.00 | true |
+| 1400.00 | false |
+| 300.00 | true |
+| 500.00 | true |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
++---------+--------+
+(14 rows)
+
+!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}], COMM=[$t1], $f0=[$t2], $f1=[$t3],
COMM0=[$t1])
+ 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 }, { 300.00 }, { 0.00 }]])
+ EnumerableSort(sort0=[$0], dir0=[ASC])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
+ EnumerableValues(tuples=[[{ 500.00 }, { 300.00 }, { 0.00 }]])
+!plan
+
+# Test LHS is nullable and RHS is nullable
+
+select comm, comm in (500, 300, 0, null) from emp;
++---------+--------+
+| COMM | EXPR$1 |
++---------+--------+
+| 0.00 | true |
+| 1400.00 | |
+| 300.00 | true |
+| 500.00 | true |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
++---------+--------+
+(14 rows)
+
+!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}], COMM=[$t1], $f0=[$t2], $f1=[$t3],
COMM0=[$t1])
+ 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)])
+ 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}])
+ EnumerableValues(tuples=[[{ 500.00 }, { 300.00 }, { 0.00 }, { null }]])
+!plan
+
+# Test LHS is (not nullable, not nullable) and RHS is (not nullable, not
nullable)
+
+select empno, (empno, empno) in ((7369, 7369), (7499, 7499), (7521, 7521))
from emp;
++-------+--------+
+| EMPNO | EXPR$1 |
++-------+--------+
+| 7369 | true |
+| 7499 | true |
+| 7521 | true |
+| 7566 | false |
+| 7654 | false |
+| 7698 | false |
+| 7782 | false |
+| 7788 | false |
+| 7839 | false |
+| 7844 | false |
+| 7876 | false |
+| 7900 | false |
+| 7902 | false |
+| 7934 | false |
++-------+--------+
+(14 rows)
+
+!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}], EMPNO=[$t2], $f0=[$t0], $f1=[$t1],
EMPNO0=[$t2], EMPNO1=[$t2])
+ 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]])
+ 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
}]])
+!plan
+
+
+# Test LHS is (nullable, nullable) and RHS is (not nullable, not nullable)
+select comm, (comm, comm) in ((500, 500), (300, 300), (0, 0)) from emp;
++---------+--------+
+| COMM | EXPR$1 |
++---------+--------+
+| 0.00 | true |
+| 1400.00 | false |
+| 300.00 | true |
+| 500.00 | true |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
++---------+--------+
+(14 rows)
+
+!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}], COMM=[$t1], $f0=[$t2], $f1=[$t3],
COMM0=[$t1], COMM1=[$t1])
+ 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 }]])
+ 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 }]])
+!plan
+
+# Test LHS is (nullable, nullable) and RHS is (nullable, nullable)
+
+select comm, (comm, comm) in ((500, 500), (300, 300), (0, 0), (null , null))
from emp;
++---------+--------+
+| COMM | EXPR$1 |
++---------+--------+
+| 0.00 | true |
+| 1400.00 | |
+| 300.00 | true |
+| 500.00 | true |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
+| | |
++---------+--------+
+(14 rows)
+
+!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}], COMM=[$t1], $f0=[$t2], $f1=[$t3],
COMM0=[$t1], COMM1=[$t1])
+ 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)])
+ 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}])
+ EnumerableValues(tuples=[[{ 500.00, 500.00 }, { 300.00, 300.00 }, {
0.00, 0.00 }, { null, null }]])
+!plan
+
# End sub-query.iq
