jayzhan211 commented on code in PR #26967:
URL: https://github.com/apache/datafusion/pull/26967#discussion_r4614959558


##########
datafusion/sqllogictest/test_files/sort_merge_join.slt:
##########
@@ -1129,6 +1129,102 @@ DROP TABLE smj_key_r;
 statement ok
 RESET datafusion.execution.batch_size;
 
+# Regression test for https://github.com/apache/datafusion/issues/26623: a 
filter that equates an expression with one of
+# its own sub-expressions must not make the sort below the join fail the
+# sanity check
+statement ok
+CREATE TABLE smj_self_eq (a INT, b INT) AS VALUES (1, 0), (2, 3);
+
+query TT
+EXPLAIN SELECT * FROM smj_self_eq x JOIN smj_self_eq y ON x.a = y.a AND x.b = 
y.b WHERE x.a + x.b = x.a;
+----
+logical_plan
+01)Inner Join: x.a = y.a, x.b = y.b
+02)--SubqueryAlias: x
+03)----Filter: smj_self_eq.a + smj_self_eq.b = smj_self_eq.a
+04)------TableScan: smj_self_eq projection=[a, b]
+05)--SubqueryAlias: y
+06)----Filter: smj_self_eq.a + smj_self_eq.b = smj_self_eq.a
+07)------TableScan: smj_self_eq projection=[a, b]
+physical_plan
+01)SortMergeJoinExec: join_type=Inner, on=[(a@0, a@0), (b@1, b@1)]
+02)--SortExec: expr=[a@0 ASC, b@1 ASC], preserve_partitioning=[true]
+03)----RepartitionExec: partitioning=Hash([a@0, b@1], 4), input_partitions=1
+04)------FilterExec: a@0 + b@1 = a@0
+05)--------DataSourceExec: partitions=1, partition_sizes=[1]
+06)--SortExec: expr=[a@0 ASC, b@1 ASC], preserve_partitioning=[true]
+07)----RepartitionExec: partitioning=Hash([a@0, b@1], 4), input_partitions=1
+08)------FilterExec: a@0 + b@1 = a@0
+09)--------DataSourceExec: partitions=1, partition_sizes=[1]
+
+query IIII
+SELECT * FROM smj_self_eq x JOIN smj_self_eq y ON x.a = y.a AND x.b = y.b 
WHERE x.a + x.b = x.a;
+----
+1 0 1 0
+
+statement ok
+DROP TABLE smj_self_eq;
+
+statement ok
+CREATE TABLE smj_self_eq_cd (a INT, b INT, c INT, d INT) AS VALUES (1, 0, 1, 
1), (2, 3, 5, 2);
+
+# a joins the class of a + b after the class exists
+query TT
+EXPLAIN SELECT x.a, x.b, y.a, y.b FROM smj_self_eq_cd x JOIN smj_self_eq_cd y 
ON x.a = y.a AND x.b = y.b WHERE x.a + x.b = x.c AND x.c = x.a;
+----
+logical_plan
+01)Inner Join: x.a = y.a, x.b = y.b
+02)--SubqueryAlias: x
+03)----Projection: smj_self_eq_cd.a, smj_self_eq_cd.b
+04)------Filter: smj_self_eq_cd.a + smj_self_eq_cd.b = smj_self_eq_cd.c AND 
smj_self_eq_cd.c = smj_self_eq_cd.a
+05)--------TableScan: smj_self_eq_cd projection=[a, b, c]
+06)--SubqueryAlias: y
+07)----TableScan: smj_self_eq_cd projection=[a, b]
+physical_plan
+01)SortMergeJoinExec: join_type=Inner, on=[(a@0, a@0), (b@1, b@1)]
+02)--SortExec: expr=[a@0 ASC, b@1 ASC], preserve_partitioning=[true]
+03)----RepartitionExec: partitioning=Hash([a@0, b@1], 4), input_partitions=1
+04)------FilterExec: a@0 + b@1 = c@2 AND c@2 = a@0, projection=[a@0, b@1]
+05)--------DataSourceExec: partitions=1, partition_sizes=[1]
+06)--SortExec: expr=[a@0 ASC, b@1 ASC], preserve_partitioning=[true]
+07)----RepartitionExec: partitioning=Hash([a@0, b@1], 4), input_partitions=1
+08)------DataSourceExec: partitions=1, partition_sizes=[1]
+
+query IIII
+SELECT x.a, x.b, y.a, y.b FROM smj_self_eq_cd x JOIN smj_self_eq_cd y ON x.a = 
y.a AND x.b = y.b WHERE x.a + x.b = x.c AND x.c = x.a;
+----
+1 0 1 0
+
+# a reaches the class of a + b when two classes merge
+query TT
+EXPLAIN SELECT x.a, x.b, y.a, y.b FROM smj_self_eq_cd x JOIN smj_self_eq_cd y 
ON x.a = y.a AND x.b = y.b WHERE x.a + x.b = x.c AND x.a = x.d AND x.d = x.c;
+----
+logical_plan
+01)Inner Join: x.a = y.a, x.b = y.b
+02)--SubqueryAlias: x
+03)----Projection: smj_self_eq_cd.a, smj_self_eq_cd.b
+04)------Filter: smj_self_eq_cd.a + smj_self_eq_cd.b = smj_self_eq_cd.c AND 
smj_self_eq_cd.d = smj_self_eq_cd.a AND smj_self_eq_cd.d = smj_self_eq_cd.c
+05)--------TableScan: smj_self_eq_cd projection=[a, b, c, d]
+06)--SubqueryAlias: y
+07)----TableScan: smj_self_eq_cd projection=[a, b]
+physical_plan
+01)SortMergeJoinExec: join_type=Inner, on=[(a@0, a@0), (b@1, b@1)]
+02)--SortExec: expr=[a@0 ASC, b@1 ASC], preserve_partitioning=[true]
+03)----RepartitionExec: partitioning=Hash([a@0, b@1], 4), input_partitions=1
+04)------FilterExec: a@0 + b@1 = c@2 AND d@3 = a@0 AND d@3 = c@2, 
projection=[a@0, b@1]
+05)--------DataSourceExec: partitions=1, partition_sizes=[1]
+06)--SortExec: expr=[a@0 ASC, b@1 ASC], preserve_partitioning=[true]
+07)----RepartitionExec: partitioning=Hash([a@0, b@1], 4), input_partitions=1
+08)------DataSourceExec: partitions=1, partition_sizes=[1]
+
+query IIII
+SELECT x.a, x.b, y.a, y.b FROM smj_self_eq_cd x JOIN smj_self_eq_cd y ON x.a = 
y.a AND x.b = y.b WHERE x.a + x.b = x.c AND x.a = x.d AND x.d = x.c;
+----
+1 0 1 0

Review Comment:
   @abp002 thanks for the updates! With `new` no longer calling `push`, 
deleting `self.promote_sub_expressions()` from `push` now passes every test. 
These two queries select only `x.a, x.b`, so the filter gets `projection=[a@0, 
b@1]`, and that projection rebuilds the class through `EquivalenceClass::new`, 
which promotes anyway. With `SELECT *` there is no projection, so removing the 
promotion from `push` fails the first query, removing it from `extend` fails 
the second, and both still fail on main:
   
   ```suggestion
   # Both queries keep every column of x: a projection above the filter would
   # rebuild the class through EquivalenceClass::new, which promotes on its own
   # a joins the class of a + b after the class exists
   query TT
   EXPLAIN SELECT * FROM smj_self_eq_cd x JOIN smj_self_eq_cd y ON x.a = y.a 
AND x.b = y.b WHERE x.a + x.b = x.c AND x.c = x.a;
   ----
   logical_plan
   01)Inner Join: x.a = y.a, x.b = y.b
   02)--SubqueryAlias: x
   03)----Filter: smj_self_eq_cd.a + smj_self_eq_cd.b = smj_self_eq_cd.c AND 
smj_self_eq_cd.c = smj_self_eq_cd.a
   04)------TableScan: smj_self_eq_cd projection=[a, b, c, d]
   05)--SubqueryAlias: y
   06)----TableScan: smj_self_eq_cd projection=[a, b, c, d]
   physical_plan
   01)SortMergeJoinExec: join_type=Inner, on=[(a@0, a@0), (b@1, b@1)]
   02)--SortExec: expr=[a@0 ASC, b@1 ASC], preserve_partitioning=[true]
   03)----RepartitionExec: partitioning=Hash([a@0, b@1], 4), input_partitions=1
   04)------FilterExec: a@0 + b@1 = c@2 AND c@2 = a@0
   05)--------DataSourceExec: partitions=1, partition_sizes=[1]
   06)--SortExec: expr=[a@0 ASC, b@1 ASC], preserve_partitioning=[true]
   07)----RepartitionExec: partitioning=Hash([a@0, b@1], 4), input_partitions=1
   08)------DataSourceExec: partitions=1, partition_sizes=[1]
   
   query IIIIIIII
   SELECT * FROM smj_self_eq_cd x JOIN smj_self_eq_cd y ON x.a = y.a AND x.b = 
y.b WHERE x.a + x.b = x.c AND x.c = x.a;
   ----
   1 0 1 1 1 0 1 1
   
   # a reaches the class of a + b when two classes merge
   query TT
   EXPLAIN SELECT * FROM smj_self_eq_cd x JOIN smj_self_eq_cd y ON x.a = y.a 
AND x.b = y.b WHERE x.a + x.b = x.c AND x.a = x.d AND x.d = x.c;
   ----
   logical_plan
   01)Inner Join: x.a = y.a, x.b = y.b
   02)--SubqueryAlias: x
   03)----Filter: smj_self_eq_cd.a + smj_self_eq_cd.b = smj_self_eq_cd.c AND 
smj_self_eq_cd.d = smj_self_eq_cd.a AND smj_self_eq_cd.d = smj_self_eq_cd.c
   04)------TableScan: smj_self_eq_cd projection=[a, b, c, d]
   05)--SubqueryAlias: y
   06)----TableScan: smj_self_eq_cd projection=[a, b, c, d]
   physical_plan
   01)SortMergeJoinExec: join_type=Inner, on=[(a@0, a@0), (b@1, b@1)]
   02)--SortExec: expr=[a@0 ASC, b@1 ASC], preserve_partitioning=[true]
   03)----RepartitionExec: partitioning=Hash([a@0, b@1], 4), input_partitions=1
   04)------FilterExec: a@0 + b@1 = c@2 AND d@3 = a@0 AND d@3 = c@2
   05)--------DataSourceExec: partitions=1, partition_sizes=[1]
   06)--SortExec: expr=[a@0 ASC, b@1 ASC], preserve_partitioning=[true]
   07)----RepartitionExec: partitioning=Hash([a@0, b@1], 4), input_partitions=1
   08)------DataSourceExec: partitions=1, partition_sizes=[1]
   
   query IIIIIIII
   SELECT * FROM smj_self_eq_cd x JOIN smj_self_eq_cd y ON x.a = y.a AND x.b = 
y.b WHERE x.a + x.b = x.c AND x.a = x.d AND x.d = x.c;
   ----
   1 0 1 1 1 0 1 1
   ```



-- 
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]


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to