Wrong row order from a function-wrapped correlated subquery in ORDER BY under a prepared statement

Both MariaDB and MySQL Bug #121598 return the wrong row order when a prepared statement's ORDER BY has two sort keys that are each a correlated scalar subquery wrapped in a function (COALESCE()/NULLIF()). At EXECUTE time the wrapped subqueries are evaluated as a constant instead of per row, so every row ties on those keys and the result is ordered only by the remaining tie-break column. The identical statement with literal values (no placeholders) sorts correctly.

Data set

CREATE DATABASE ob_bug;

USE ob_bug;

CREATE TABLE m (id INT PRIMARY KEY, w INT);

CREATE TABLE d (mid INT, k VARCHAR(16), v VARCHAR(16), KEY(mid));

INSERT INTO m VALUES (1,1),(2,3),(3,2);

INSERT INTO d VALUES (1,'k1','a'),(2,'k1','a'),(3,'k1','a'),

                     (1,'k2','x'),(2,'k2','y'),(3,'k2','z');

How to repeat
Key k1 is the same ('a') for every row (a tie); key k2 is x<y<z and decides the order, so the correct order is id = 1,2,3. w is only a final tie-break.

PREPARE s FROM 'SELECT id FROM m ORDER BY

  NULLIF((SELECT v FROM d WHERE d.mid = m.id AND d.k = ? LIMIT 1), ''''),

  NULLIF((SELECT v FROM d WHERE d.mid = m.id AND d.k = ? LIMIT 1), ''''),

  w';

SET @a = 'k1', @b = 'k2';

EXECUTE s USING @a, @b;

Actual result (MariaDB 13.0.2, wrong):

+----+

| id |

+----+

|  1 |

|  3 |

|  2 |

+----+

Expected result – the same statement with literal values sorts correctly (MariaDB 11.4+/MySQL 8.4):

SELECT id FROM m ORDER BY

  NULLIF((SELECT v FROM d WHERE d.mid = m.id AND d.k = 'k1' LIMIT 1), ''),

  NULLIF((SELECT v FROM d WHERE d.mid = m.id AND d.k = 'k2' LIMIT 1), ''),

  w;

+----+

| id |

+----+

|  1 |

|  2 |

|  3 |

+----+

Affected versions (tested 2026-10; PREPARE/EXECUTE vs inline literals):

Notes (from bisection)

• Needs two function-wrapped correlated-subquery sort keys; a single such key sorts correctly.
• The wrapping function is the trigger – NULLIF() and COALESCE() both reproduce; a bare correlated subquery (ORDER BY (SELECT ...), no function) sorts correctly.
• Only via the prepared/placeholder path; inline literal SQL is correct (except on 10.2, which is wrong either way).
• A following non-subquery ORDER BY column (here w) is required to make the wrong order observable; without it the tied rows happen to come back in PK order.

Workaround
Keep the subquery bare and move the function logic into operators, e.g. replace ORDER BY NULLIF((SELECT ...), '') with ORDER BY ((SELECT ...) IS NULL OR (SELECT ...) = ''), (SELECT ...).