Description:
When a prepared statement's ORDER BY has two sort keys that are each a correlated scalar subquery wrapped in a function (COALESCE() or NULLIF()), the wrapped subqueries are evaluated as a constant at EXECUTE time instead of per row. Every row then ties on those keys, so the result is ordered only by the remaining (non-subquery) ORDER BY column, giving the wrong row order.
The identical statement with literal values (no placeholders) returns the correct order, so this affects only the prepared/placeholder execution path.
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.
- A following non-subquery ORDER BY column is required to make the wrong order observable (without it the tied rows happen to come back in primary-key order).
The same bug is present in MariaDB; reported there as MDEV-41774 (https://jira.mariadb.org/browse/MDEV-41774).
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 ...)
How to repeat:
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');
-- 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. Column 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 (wrong): 1, 3, 2
-- EXPECTED: 1, 2, 3
-- The same statement with literal values returns the correct order (1,2,3):
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;
