This is an automated email from the ASF dual-hosted git repository.
xiong pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new fc2fd2dd7 [CALCITE-5021] Double JOIN is created for NOT IN when
IN-list that the values all non-nullable is converted to Values
fc2fd2dd7 is described below
commit fc2fd2dd7efe237905f53ed754a2af2dd0a669ff
Author: NobiGo <nobigo...@gmail.com>
AuthorDate: Fri Feb 25 10:35:13 2022 +0800
[CALCITE-5021] Double JOIN is created for NOT IN when IN-list that the
values all non-nullable is converted to Values
---
.../org/apache/calcite/sql/type/SqlTypeUtil.java | 28 ++++++++++++++++++
.../apache/calcite/sql2rel/SqlToRelConverter.java | 23 ++++++---------
.../apache/calcite/test/SqlToRelConverterTest.xml | 8 +++---
core/src/test/resources/sql/sub-query.iq | 33 +++++++++++++++++++++-
4 files changed, 73 insertions(+), 19 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 0fcff9c33..3f5a8ec96 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
@@ -294,6 +294,34 @@ public abstract class SqlTypeUtil {
return false;
}
+ /**
+ * Creates a RelDataType having the same type of the sourceRelDataType,
+ * and the same nullability as the targetRelDataType.
+ */
+ public static RelDataType keepSourceTypeAndTargetNullability(RelDataType
sourceRelDataType,
+ RelDataType targetRelDataType,
+ RelDataTypeFactory typeFactory) {
+ if (!targetRelDataType.isStruct()) {
+ return typeFactory.createTypeWithNullability(
+ sourceRelDataType, targetRelDataType.isNullable());
+ }
+ List<RelDataTypeField> targetFields = targetRelDataType.getFieldList();
+ List<RelDataTypeField> sourceFields = sourceRelDataType.getFieldList();
+ ImmutableList.Builder<RelDataTypeField> newTargetField =
ImmutableList.builder();
+ for (int i = 0; i < targetRelDataType.getFieldCount(); i++) {
+ RelDataTypeField targetField = targetFields.get(i);
+ RelDataTypeField sourceField = sourceFields.get(i);
+ newTargetField.add(
+ new RelDataTypeFieldImpl(
+ sourceField.getName(),
+ sourceField.getIndex(),
+ keepSourceTypeAndTargetNullability(
+ sourceField.getType(), targetField.getType(),
typeFactory)));
+ }
+ RelDataType relDataType =
typeFactory.createStructType(newTargetField.build());
+ return typeFactory.createTypeWithNullability(relDataType,
targetRelDataType.isNullable());
+ }
+
/**
* Returns typeName.equals(type.getSqlTypeName()). If
* typeName.equals(SqlTypeName.Any) true is always returned.
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 c85c70148..c22ecc5a2 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -1397,15 +1397,16 @@ public class SqlToRelConverter {
//
// The first two lists contain the same number of fields.
final int k = (fieldCount - 1) / 2;
+ ImmutableList.Builder<RexNode> rexNodeBuilder = ImmutableList.builder();
+ rexNodeBuilder.add(rexNode);
for (int i = 0; i < k; i++) {
- rexNode =
+ rexNodeBuilder.add(
rexBuilder.makeCall(
- SqlStdOperatorTable.AND,
- rexNode,
- rexBuilder.makeCall(
- SqlStdOperatorTable.IS_NOT_NULL,
- rexBuilder.makeFieldAccess(rex, i)));
+ SqlStdOperatorTable.IS_NOT_NULL,
+ rexBuilder.makeFieldAccess(rex, i)));
}
+ rexNode = rexBuilder.makeCall(rexNode.getType(), SqlStdOperatorTable.AND,
+ RexUtil.flatten(rexNodeBuilder.build(),
SqlStdOperatorTable.AND));
return rexNode;
case TRUE_FALSE_UNKNOWN:
@@ -1717,15 +1718,9 @@ public class SqlToRelConverter {
final RelDataType rowType;
if (targetRowType != null) {
rowType =
- typeFactory.createTypeWithNullability(
- targetRowType,
- SqlTypeUtil.containsNullable(listType));
+ SqlTypeUtil.keepSourceTypeAndTargetNullability(targetRowType,
listType, typeFactory);
} else {
- rowType =
- SqlTypeUtil.promoteToRowType(
- typeFactory,
- listType,
- null);
+ rowType = SqlTypeUtil.promoteToRowType(typeFactory, listType, null);
}
final List<RelNode> unionInputs = new ArrayList<>();
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 6684c5cbd..407f03ab4 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2713,7 +2713,7 @@ LogicalTableModify(table=[[CATALOG, SALES, EMPDEFAULTS]],
operation=[INSERT], fl
<![CDATA[
LogicalTableModify(table=[[SALES, EMPDEFAULTS]], operation=[INSERT],
flattened=[true])
LogicalFilter(condition=[=($7, 20)])
- LogicalProject(EMPNO=[$2], ENAME=[$0], JOB=[null:VARCHAR(10)],
MGR=[null:INTEGER], HIREDATE=[null:TIMESTAMP(0)], SAL=[$4],
COMM=[null:INTEGER], DEPTNO=[$1], SLACKER=[null:BOOLEAN], EXTRA=[null:BOOLEAN],
UPDATED=[$3])
+ LogicalProject(EMPNO=[$2], ENAME=[$0], JOB=[null:VARCHAR(10)],
MGR=[null:INTEGER], HIREDATE=[null:TIMESTAMP(0)], SAL=[CAST($4):INTEGER],
COMM=[null:INTEGER], DEPTNO=[CAST($1):INTEGER], SLACKER=[null:BOOLEAN],
EXTRA=[null:BOOLEAN], UPDATED=[CAST($3):TIMESTAMP(0)])
LogicalValues(tuples=[[{ 'Fred', 20, 44, 2017-03-12 13:03:05, 999999 }]])
]]>
</Resource>
@@ -2725,7 +2725,7 @@ LogicalTableModify(table=[[SALES, EMPDEFAULTS]],
operation=[INSERT], flattened=[
<Resource name="plan">
<![CDATA[
LogicalTableModify(table=[[SALES, EMPDEFAULTS]], operation=[INSERT],
flattened=[true])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[null:INTEGER],
HIREDATE=[null:TIMESTAMP(0)], SAL=[555], COMM=[null:INTEGER], DEPTNO=[20],
SLACKER=[null:BOOLEAN])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[CAST($2):VARCHAR(10)],
MGR=[null:INTEGER], HIREDATE=[null:TIMESTAMP(0)], SAL=[555],
COMM=[null:INTEGER], DEPTNO=[20], SLACKER=[null:BOOLEAN])
LogicalValues(tuples=[[{ 34625, 'nom', 'accountant' }]])
]]>
</Resource>
@@ -2764,7 +2764,7 @@ values (10, 'Fred')]]>
<![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[INSERT],
flattened=[true])
LogicalFilter(condition=[>($5, 1000)])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[null:VARCHAR(10)],
MGR=[null:INTEGER], HIREDATE=[null:TIMESTAMP(0)], SAL=[null:INTEGER],
COMM=[null:INTEGER], DEPTNO=[20], SLACKER=[null:BOOLEAN])
+ LogicalProject(EMPNO=[$0], ENAME=[CAST($1):VARCHAR(20)],
JOB=[null:VARCHAR(10)], MGR=[null:INTEGER], HIREDATE=[null:TIMESTAMP(0)],
SAL=[null:INTEGER], COMM=[null:INTEGER], DEPTNO=[20], SLACKER=[null:BOOLEAN])
LogicalValues(tuples=[[{ 10, 'Fred' }]])
]]>
</Resource>
@@ -2790,7 +2790,7 @@ values (150, 'Fred')]]>
<![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[INSERT],
flattened=[true])
LogicalFilter(condition=[>($5, 1000)])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[null:VARCHAR(10)],
MGR=[null:INTEGER], HIREDATE=[null:TIMESTAMP(0)], SAL=[null:INTEGER],
COMM=[null:INTEGER], DEPTNO=[20], SLACKER=[null:BOOLEAN])
+ LogicalProject(EMPNO=[$0], ENAME=[CAST($1):VARCHAR(20)],
JOB=[null:VARCHAR(10)], MGR=[null:INTEGER], HIREDATE=[null:TIMESTAMP(0)],
SAL=[null:INTEGER], COMM=[null:INTEGER], DEPTNO=[20], SLACKER=[null:BOOLEAN])
LogicalValues(tuples=[[{ 150, 'Fred' }]])
]]>
</Resource>
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index cbdc992b7..bbad2b6f2 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -3345,11 +3345,42 @@ EnumerableCalc(expr#0..14=[{inputs}],
expr#15=[0:BIGINT], expr#16=[=($t8, $t15)]
EnumerableCalc(expr#0..9=[{inputs}], proj#0..9=[{exprs}], EMPNO0=[$t0],
DEPTNO0=[$t7])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0,
$1)])
+ EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($1)])
EnumerableValues(tuples=[[{ 1, 2 }, { 3, null }]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
EnumerableValues(tuples=[[{ 1, 2 }, { 3, null }]])
!plan
+select * from "scott".emp where (empno, deptno) not in ((7369, 20), (7499,
30));
++-------+--------+-----------+------+------------+---------+---------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+--------+-----------+------+------------+---------+---------+--------+
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 |
+| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
+| 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 |
+| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
+| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 |
+| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 |
+| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 |
++-------+--------+-----------+------+------------+---------+---------+--------+
+(12 rows)
+
+!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}], proj#0..7=[{exprs}], EMPNO0=[$t0],
DEPTNO0=[$t7])
+ 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, 20 }, { 7499, 30 }]])
+!plan
+
# End sub-query.iq
