Wrong ORDER BY results after an outer join when the nullable side equates a column to coalesce/CASE #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

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 (c3ef346):

+----+------+------+
| 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 fix: preserve compound expression equivalences through projection #25922 makes this query correct again (the plan keeps an embedded projection that collapsed the class). The query is wrong even without fix: preserve compound expression equivalences through projection #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 LEFT JOIN with struct-field ORDER BY fails ordering validation #25921 fixed. is_restrict_null_predicate in the optimizer makes a similar check for predicates.
• [branch-55] fix: preserve compound expression equivalences through projection (#25922) #25980 backports fix: preserve compound expression equivalences through projection #25922 to branch-55, so 55.2.0 would pick this up.