[Bug]: ORDER BY on top of FULL OUTER JOIN produces rows out of order

[Ariznawlll]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Branch Name

main

Commit ID

f0a2c64

Other Environment Information

• MO Version: 8.0.30-MatrixOne-v
• Discovered while comparing FULL OUTER JOIN output against MySQL 8.0.45 for the test cases added in follow-up to [Bug]: Implement full outer join. #24192.

Actual Behavior

ORDER BY applied on top of FULL OUTER JOIN does not sort the result
correctly when the key column has NULLs on the NULL-padded side. Rows are
emitted out of order compared to any standard-compliant engine.

For the minimal repro below, (NULL, NULL, 14, NULL) is emitted before
(NULL, NULL, 13, 'c'), violating ORDER BY t1.s, t2.s, t1.v, t2.v
(13 < 14 but MO outputs 14 first).

Expected Behavior

Rows should be produced in strict ascending order of the ORDER BY key list,
exactly like MySQL / PostgreSQL / any rewrite via LEFT JOIN ... UNION ALL RIGHT JOIN.

Steps to Reproduce

drop database if exists fj_orderby_bug;
create database fj_orderby_bug;
use fj_orderby_bug;

create table t1(s int, v varchar(5));
create table t2(s int, v varchar(5));
insert into t1 values (1,'a'),(5,'b'),(NULL,'x');
insert into t2 values (13,'c'),(14,NULL);

select t1.s, t1.v, t2.s, t2.v
from t1 full outer join t2 on t1.s = t2.s
order by t1.s, t2.s, t1.v, t2.v;

Observed output (MO):

NULL  x     NULL  NULL
NULL  NULL  14    NULL    <-- should come AFTER (NULL,NULL,13,'c')
NULL  NULL  13    c
1     a     NULL  NULL
5     b     NULL  NULL

Expected output (MySQL via LEFT UNION RIGHT rewrite on the same data):

NULL  NULL  13    c
NULL  NULL  14    NULL
NULL  x     NULL  NULL
1     a     NULL  NULL
5     b     NULL  NULL

The MySQL engine's MySQL-style NULL ordering ("NULL first ascending") gives
a slightly different placement of the NULL x NULL NULL row, but both
engines agree that 13, c must precede 14, NULL. MO does not.

Additional information

• EXPLAIN shows the Sort node above the FULL OUTER Join with the correct
  Sort Key, so the plan is right but the executor does not honor it.

Project
  ->  Sort
        Sort Key: t1.s INTERNAL, t2.s INTERNAL, t1.v INTERNAL, t2.v INTERNAL
        ->  Join
              Join Type: FULL OUTER
              Join Cond: (t2.s = t1.s)
              ->  Table Scan on ...t2
              ->  Table Scan on ...t1

• The issue reproduces only when the join produces mixed matched + NULL-padded
  rows; trivial cases (only unmatched, or only matched) sort correctly.
• A related, simpler case: ORDER BY t1.S1 alone on the 12-row / 10-row t1/t2
  dataset used in test/distributed/cases/join/fulljoin.sql returns rows like
  NULL aaa NULL NULL interleaved between non-matching groups rather than
  being grouped with the other NULL-key rows.
• Sibling bug filed as [Bug]: FULL OUTER JOIN ... USING (col) fails to coalesce the merged column #24247 (FULL OUTER JOIN USING fails to coalesce).

Impact

Any FULL OUTER JOIN query with user-visible ordering produces wrong row
orders. Downstream logic that relies on ordering (LIMIT, window functions,
client-side "first N rows", GROUP_CONCAT via ORDER BY, etc.) is affected.

[Ariznawlll]

本地最新 MO 环境验证通过。

验证环境：

• MO version: 8.0.30-MatrixOne-v3e4bbbfc5
• 验证时间：2026-04-30

ORDER BY 在 FULL OUTER JOIN 上的关键错序已恢复，t2.s=13 现在排在 t2.s=14 前面：

+------+------+------+------+
| s    | v    | s    | v    |
+------+------+------+------+
| NULL | x    | NULL | NULL |
| NULL | NULL |   13 | c    |
| NULL | NULL |   14 | NULL |
|    1 | a    | NULL | NULL |
|    5 | b    | NULL | NULL |
+------+------+------+------+