Description:
MySQL correctly rejects ORDER BY GROUPING(expr) when expr is not part of the GROUP BY list for a top-level query. The same illegal construct is silently accepted when it appears inside certain subqueries (EXISTS, IN, ALL, ANY) because redundant ORDER BY (and then GROUP BY) can be removed during resolution before GROUPING() arguments are validated against the GROUP BY list. This weakens semantic checks and can mask user errors.
How to repeat:
CREATE TABLE t1 (f1 INT, f2 INT);
INSERT INTO t1 VALUES (1,1), (1,2), (2,1);
1) Top-level (reference — fails as expected)
SELECT 1 FROM t1 GROUP BY f1 ORDER BY GROUPING(f2);
2) Same illegal GROUPING(f2) in ORDER BY, inside subqueries (unexpected success)
SELECT 1 FROM t1 WHERE EXISTS (
SELECT 1 FROM t1 GROUP BY f1 ORDER BY GROUPING(f2)
);
SELECT 1 FROM t1 WHERE 1 IN (
SELECT 1 FROM t1 GROUP BY f1 ORDER BY GROUPING(f2)
);
SELECT 1 FROM t1 WHERE f1 > ALL (
SELECT 1 FROM t1 GROUP BY f1 ORDER BY GROUPING(f2)
);
SELECT 1 FROM t1 WHERE f1 > ANY (
SELECT 1 FROM t1 GROUP BY f1 ORDER BY GROUPING(f2)
);
