xuzifu666 commented on code in PR #5110:
URL: https://github.com/apache/calcite/pull/5110#discussion_r3610265306
##########
core/src/test/resources/sql/sub-query.iq:
##########
@@ -4339,14 +4339,14 @@ select * from "scott".emp where (mgr, deptno) not in
((1, 2), (3, null), (cast(n
!ok
!if (use_old_decorr) {
-EnumerableCalc(expr#0..13=[{inputs}], expr#14=[0], expr#15=[=($t8, $t14)],
expr#16=[IS NULL($t3)], expr#17=[IS NULL($t7)], expr#18=[IS NOT NULL($t12)],
expr#19=[AND($t13, $t18)], expr#20=[<($t9, $t8)], expr#21=[OR($t16, $t17, $t19,
$t18, $t20)], expr#22=[IS NOT TRUE($t21)], expr#23=[OR($t15, $t22)],
proj#0..7=[{exprs}], $condition=[$t23])
+EnumerableCalc(expr#0..12=[{inputs}], expr#13=[0], expr#14=[=($t8, $t13)],
expr#15=[IS NULL($t12)], expr#16=[>=($t9, $t8)], expr#17=[IS NOT NULL($t3)],
expr#18=[IS NOT NULL($t7)], expr#19=[AND($t15, $t16, $t17, $t18)],
expr#20=[OR($t14, $t19)], proj#0..7=[{exprs}], $condition=[$t20])
Review Comment:
Redundant terms within OR(...) are absorbed by IS NOT NULL/IS NULL-related
terms, shortening the expression.
##########
core/src/test/resources/sql/sub-query.iq:
##########
@@ -4339,14 +4339,14 @@ select * from "scott".emp where (mgr, deptno) not in
((1, 2), (3, null), (cast(n
!ok
!if (use_old_decorr) {
-EnumerableCalc(expr#0..13=[{inputs}], expr#14=[0], expr#15=[=($t8, $t14)],
expr#16=[IS NULL($t3)], expr#17=[IS NULL($t7)], expr#18=[IS NOT NULL($t12)],
expr#19=[AND($t13, $t18)], expr#20=[<($t9, $t8)], expr#21=[OR($t16, $t17, $t19,
$t18, $t20)], expr#22=[IS NOT TRUE($t21)], expr#23=[OR($t15, $t22)],
proj#0..7=[{exprs}], $condition=[$t23])
+EnumerableCalc(expr#0..12=[{inputs}], expr#13=[0], expr#14=[=($t8, $t13)],
expr#15=[IS NULL($t12)], expr#16=[>=($t9, $t8)], expr#17=[IS NOT NULL($t3)],
expr#18=[IS NOT NULL($t7)], expr#19=[AND($t15, $t16, $t17, $t18)],
expr#20=[OR($t14, $t19)], proj#0..7=[{exprs}], $condition=[$t20])
EnumerableNestedLoopJoin(condition=[AND(OR(IS NULL($10), =(CAST($3):INTEGER,
$10)), OR(IS NULL($11), =(CAST($7):INTEGER, $11)))], joinType=[left])
EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT() FILTER $0])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT NULL($t0)],
expr#3=[IS NOT NULL($t1)], expr#4=[OR($t2, $t3)], $f2=[$t4])
EnumerableValues(tuples=[[{ 3, null }, { null, null }, { 1, 2 }]])
- EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], expr#3=[IS NOT
NULL($t0)], expr#4=[IS NOT NULL($t1)], expr#5=[AND($t3, $t4)], expr#6=[OR($t3,
$t4)], proj#0..2=[{exprs}], $f20=[$t5], $condition=[$t6])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], expr#3=[IS NOT
NULL($t0)], expr#4=[IS NOT NULL($t1)], expr#5=[OR($t3, $t4)],
proj#0..2=[{exprs}], $condition=[$t5])
Review Comment:
The original plan involved calculating two columns simultaneously:
```
expr#3=[IS NOT NULL($t0)]
expr#4=[IS NOT NULL($t1)]
expr#5=[AND($t3, $t4)] -- Neither column is empty
expr#6=[OR($t3, $t4)] -- At least one non-empty column
```
After simplification, the upper-level condition only requires "at least one
non-null column" to handle the NULL semantics of NOT IN; AND($t3, $t4) has been
absorbed:
```
expr#3=[IS NOT NULL($t0)]
expr#4=[IS NOT NULL($t1)]
expr#5=[OR($t3, $t4)]
```
##########
core/src/test/resources/sql/sub-query.iq:
##########
@@ -4324,7 +4324,7 @@ EnumerableCalc(expr#0..14=[{inputs}], expr#15=[0],
expr#16=[=($t8, $t15)], expr#
EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT() FILTER $0])
EnumerableValues(tuples=[[{ true }, { true }]])
EnumerableSort(sort0=[$0], dir0=[ASC])
- EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], expr#3=[IS NOT
NULL($t1)], proj#0..3=[{exprs}])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
Review Comment:
After the main Calc node is simplified, the subquery no longer needs to
additionally output the `IS NOT NULL($t1)` column, resulting in this structure.
##########
core/src/test/resources/sql/sub-query.iq:
##########
@@ -4315,7 +4315,7 @@ select * from "scott".emp where (empno, deptno) not in
((1, 2), (3, null));
!ok
!if (use_old_decorr) {
-EnumerableCalc(expr#0..14=[{inputs}], expr#15=[0], expr#16=[=($t8, $t15)],
expr#17=[IS NULL($t7)], expr#18=[IS NOT NULL($t13)], expr#19=[AND($t14, $t18)],
expr#20=[<($t9, $t8)], expr#21=[OR($t17, $t19, $t18, $t20)], expr#22=[IS NOT
TRUE($t21)], expr#23=[OR($t16, $t22)], proj#0..7=[{exprs}], $condition=[$t23])
+EnumerableCalc(expr#0..13=[{inputs}], expr#14=[0], expr#15=[=($t8, $t14)],
expr#16=[IS NULL($t13)], expr#17=[>=($t9, $t8)], expr#18=[IS NOT NULL($t7)],
expr#19=[AND($t16, $t17, $t18)], expr#20=[OR($t15, $t19)], proj#0..7=[{exprs}],
$condition=[$t20])
Review Comment:
Since `IS NOT NULL($t13)` is already part of the `OR` clause and is subsumed
by `AND($t14, IS NOT NULL($t13))`, the term `AND($t14, IS NOT NULL($t13))` is
absorbed by `IS NOT NULL($t13)`. After converting `NOT(<)` to `>=` and
reordering the variables, we obtain:
```
OR(=($t8, 0), AND(IS NULL($t13), >=($t9, $t8), IS NOT NULL($t7)))
```
##########
core/src/test/resources/sql/sub-query.iq:
##########
@@ -4381,7 +4381,7 @@ select * from "scott".emp where (empno, deptno) not in
((7369, 20), (7499, 30));
!ok
!if (use_old_decorr) {
-EnumerableCalc(expr#0..15=[{inputs}], expr#16=[0], expr#17=[=($t8, $t16)],
expr#18=[IS NULL($t7)], expr#19=[IS NOT NULL($t14)], expr#20=[AND($t15, $t19)],
expr#21=[<($t9, $t8)], expr#22=[OR($t18, $t20, $t19, $t21)], expr#23=[IS NOT
TRUE($t22)], expr#24=[OR($t17, $t23)], proj#0..7=[{exprs}], $condition=[$t24])
+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])
Review Comment:
Similarly, the expression is compressed because the `OR(...)` clause
resulting from the expansion of `NOT IN` contains redundant terms that can be
absorbed.
##########
core/src/test/resources/sql/sub-query.iq:
##########
@@ -4391,7 +4391,7 @@ EnumerableCalc(expr#0..15=[{inputs}], expr#16=[0],
expr#17=[=($t8, $t16)], expr#
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], $f2=[$t2])
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}],
$f20=[$t2])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
Review Comment:
After simplification in Calc, the helper column $f20 is no longer needed.
##########
core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java:
##########
@@ -322,9 +322,8 @@ class JdbcAdapterTest {
+ " JdbcProject($f2=[OR(IS NOT NULL($7), IS NOT
NULL($1))])\n"
+ " JdbcTableScan(table=[[SCOTT, EMP]])\n"
+ " JdbcToEnumerableConverter\n"
- + " JdbcAggregate(group=[{0, 1}], i=[LITERAL_AGG(true)],
em=[MAX($2)])\n"
- + " JdbcProject(DEPTNO=[$7], ENAME=[CAST($1):VARCHAR(14)],"
- + " $f2=[AND(IS NOT NULL($7), IS NOT NULL($1))])\n"
+ + " JdbcAggregate(group=[{0, 1}], i=[LITERAL_AGG(true)])\n"
Review Comment:
The intermediate variable `$f2` is no longer needed in the condition; `OR(IS
NULL($7), IS NULL($1))` suffices. Consequently:
`$f2=[AND(IS NOT NULL($7), IS NOT NULL($1))]` is removed from `JdbcProject`;
`em=[MAX($2)]` is removed from `JdbcAggregate`;
At the same time, `JdbcFilter(condition=[OR(IS NOT NULL($7), IS NULL($1))])`
is added to the subquery side to filter out rows where both columns are `NULL`
at an earlier stage.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
