andygrove opened a new issue, #26036:
URL: https://github.com/apache/datafusion/issues/26036
### Describe the bug
`ORDER BY` after a LEFT/RIGHT/FULL join can return rows in the wrong order,
and `ORDER BY ... LIMIT` the wrong rows, when the nullable side has a filter
such as `b = coalesce(a, 0)`.
`EquivalenceGroup::join` keeps the nullable side's equivalence classes
unchanged for outer joins, but `b = coalesce(a, 0)` doesn't hold for
NULL-extended rows: `b` is NULL while `coalesce(a, 0)` is 0.
`EnsureRequirements` then replaces the sort on `coalesce(a, 0)` with a sort on
`b`.
The underlying problem predates #25922, but #25922 makes it reach common
plans. Before it, `project_expr_indirect` collapsed such classes at the first
projection after the join, which hid the problem.
### To Reproduce
```sql
SET datafusion.execution.target_partitions = 1;
CREATE TABLE t1 (id BIGINT) AS VALUES (1), (2);
CREATE TABLE t2 (a BIGINT, b BIGINT) AS VALUES (2, 2), (3, 3), (5, 5), (6,
6), (7, 7), (8, 8);
SELECT * FROM t1
LEFT JOIN (SELECT a, b FROM t2 WHERE b = coalesce(a, 0)) ON id = b
ORDER BY coalesce(a, 0), id;
```
On `main` (c3ef34688):
```
+----+------+------+
| id | a | b |
+----+------+------+
| 2 | 2 | 2 |
| 1 | NULL | NULL |
+----+------+------+
```
```
ProjectionExec: expr=[id@2 as id, a@0 as a, b@1 as b]
SortExec: expr=[b@1 ASC NULLS LAST, id@2 ASC NULLS LAST],
preserve_partitioning=[false]
HashJoinExec: mode=CollectLeft, join_type=Right, on=[(b@1, id@0)]
FilterExec: b@1 = CASE WHEN a@0 IS NOT NULL THEN a@0 ELSE 0 END
DataSourceExec: partitions=1, partition_sizes=[1]
DataSourceExec: partitions=1, partition_sizes=[1]
```
### Expected behavior
`(1, NULL, NULL)` first, since `coalesce(NULL, 0) = 0`:
```
+----+------+------+
| id | a | b |
+----+------+------+
| 1 | NULL | NULL |
| 2 | 2 | 2 |
+----+------+------+
```
### Additional context
- The same happens with RIGHT and FULL joins, with `CASE WHEN a IS NULL THEN
0 ELSE a END`, and with `ORDER BY ... LIMIT 1` (returns `(2, 2, 2)`). It also
reproduces with the default `target_partitions`.
- Reverting only the `class.rs` change from #25922 makes this query correct
again (the plan keeps an embedded projection that collapsed the class). The
query is wrong even without #25922 if `t2` is large enough that the join isn't
swapped, so nothing sits between the join and the sort. With 24 rows in `t2`,
for example: `CREATE TABLE t2 (a BIGINT, b BIGINT) AS VALUES (2, 2), (3, 3),
..., (25, 25)`.
- Possible fix: for outer joins, have `EquivalenceGroup::join` keep a
nullable-side member only if it is NULL whenever that side's columns are NULL
(e.g. columns, `get_field`, arithmetic), and drop classes that end up with a
single member. That keeps #25921 fixed. `is_restrict_null_predicate` in the
optimizer makes a similar check for predicates.
- #25980 backports #25922 to `branch-55`, so 55.2.0 would pick this up.
--
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]
