xiedeyantu commented on code in PR #4724:
URL: https://github.com/apache/calcite/pull/4724#discussion_r2664627719
##########
core/src/test/resources/sql/blank.iq:
##########
@@ -247,4 +247,99 @@ from complex_t;
!ok
+# [CALCITE-4765] Complex correlated EXISTS sub-queries used as scalar
subqueries can return wrong results
+CREATE TABLE emps (
+ empid INTEGER NOT NULL,
+ deptno INTEGER NOT NULL,
+ name VARCHAR(10) NOT NULL,
+ salary DECIMAL(10, 2) NOT NULL,
+ commission INTEGER);
+(0 rows modified)
+
+!update
+INSERT INTO emps (empid, deptno, name, salary, commission) VALUES
+(100, 10, 'Bill', 10000.00, 1000),
+(200, 20, 'Eric', 8000.00, 500),
+(150, 10, 'Sebastian', 7000.00, NULL),
+(110, 10, 'Theodore', 11500.00, 250),
+(170, 30, 'Theodore', 11500.00, 250),
+(140, 10, 'Sebastian', 7000.00, NULL);
+(6 rows modified)
+
+!update
+select * from emps e1 where EXISTS(select * from (
+ select e2.deptno from emps e2
+ where e2.commission = e1.commission) as table3
+where table3.deptno <> e1.deptno);
++-------+--------+----------+----------+------------+
+| EMPID | DEPTNO | NAME | SALARY | COMMISSION |
++-------+--------+----------+----------+------------+
+| 110 | 10 | Theodore | 11500.00 | 250 |
+| 170 | 30 | Theodore | 11500.00 | 250 |
++-------+--------+----------+----------+------------+
+(2 rows)
+
+!ok
+!if (use_old_decorr) {
+EnumerableHashJoin(condition=[AND(=($1, $5), =($4, $7))], joinType=[semi])
+ EnumerableTableScan(table=[[BLANK, EMPS]])
+ EnumerableNestedLoopJoin(condition=[<>($1, $0)], joinType=[inner])
+ EnumerableAggregate(group=[{1}])
+ EnumerableTableScan(table=[[BLANK, EMPS]])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[IS NOT NULL($t4)],
DEPTNO=[$t1], COMMISSION=[$t4], $condition=[$t5])
+ EnumerableTableScan(table=[[BLANK, EMPS]])
+!plan
+!}
+
+!if (use_new_decorr) {
+EnumerableHashJoin(condition=[AND(IS NOT DISTINCT FROM($1, $5), IS NOT
DISTINCT FROM($4, $6))], joinType=[semi])
+ EnumerableTableScan(table=[[BLANK, EMPS]])
+ EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}])
+ EnumerableHashJoin(condition=[AND(=($1, $3), <>($2, $0))],
joinType=[inner])
+ EnumerableAggregate(group=[{1, 4}])
+ EnumerableTableScan(table=[[BLANK, EMPS]])
+ EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t1], COMMISSION=[$t4])
+ EnumerableTableScan(table=[[BLANK, EMPS]])
+!plan
+!}
+
+SELECT *, EXISTS(select * from (
+ SELECT e2.deptno FROM emps e2 where e1.commission = e2.commission) as table3
+ where table3.deptno <> e1.deptno)
+from emps e1;
+!if (use_old_decorr) {
Review Comment:
Thank you so much for your prompt attention to this issue. It involves new
Quidm tests, new TopDownGeneralDecorrelator, and new MARK JOINs. Please feel
free to @ me if you'd like to discuss it. @silundong If you have time, you can
also look into this issue, because you might understand it better. Thanks！
--
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]
