Description:
GROUP_CONCAT() returns an incorrectly ordered string when a constant boolean expression is added as the first item in its ORDER BY list, followed by a correlated scalar subquery.
Without the constant item, the query returns 2,4,3,5, which satisfies the specified descending order of the subquery values. Adding TRUE before the existing ordering expression changes the result to 5,4,3,2, which violates that ordering. Using FALSE instead of TRUE produces the same incorrect result.
Since the added expression has the same value for every input row, it should not change the ordering requirements imposed by the subsequent expression.
Reproduced on MySQL 9.6.0 and 26.7.0 using the mysql command-line client. Only the two tables and inserts below are needed.
How to repeat:
Run the following in a schema where these table names do not already exist:
CREATE TABLE t1_func_gconcat (a INT, c INT);
INSERT INTO t1_func_gconcat VALUES (1, 2), (2, 3), (2, 4), (3, 5);
CREATE TABLE t2_func_gconcat (a INT, c INT);
INSERT INTO t2_func_gconcat VALUES (1, 5), (2, 4), (3, 3), (3, 3);
-- Query 1: returns a correctly ordered result.
SELECT GROUP_CONCAT(
c ORDER BY (
SELECT MID(GROUP_CONCAT(c ORDER BY a SEPARATOR ','), 1, 5)
FROM t2_func_gconcat
WHERE t2_func_gconcat.a = t1_func_gconcat.a
) DESC SEPARATOR ','
) AS col1
FROM t1_func_gconcat;
-- Query 2: adding a constant ordering item produces an incorrect result.
SELECT GROUP_CONCAT(
c ORDER BY TRUE, (
SELECT MID(GROUP_CONCAT(c ORDER BY a SEPARATOR ','), 1, 5)
FROM t2_func_gconcat
WHERE t2_func_gconcat.a = t1_func_gconcat.a
) DESC SEPARATOR ','
) AS col1
FROM t1_func_gconcat;
Actual results:
Query1: 2, 4, 3, 5
Query2: 5, 4, 3, 2
Replacing TRUE with FALSE in Query 2 also returns 5,4,3,2.
Expected result:
Both queries should return either 2,3,4,5 or 2,4,3,5.
Suggested fix:
Ensure that constant items in GROUP_CONCAT()'s ORDER BY list do not interfere with the evaluation or comparison of subsequent ordering expressions. Add regression coverage for leading TRUE and FALSE items followed by a correlated scalar subquery.
© 2026, Oracle Corporation and/or its affiliates
Description: GROUP_CONCAT() returns an incorrectly ordered string when a constant boolean expression is added as the first item in its ORDER BY list, followed by a correlated scalar subquery. Without the constant item, the query returns 2,4,3,5, which satisfies the specified descending order of the subquery values. Adding TRUE before the existing ordering expression changes the result to 5,4,3,2, which violates that ordering. Using FALSE instead of TRUE produces the same incorrect result. Since the added expression has the same value for every input row, it should not change the ordering requirements imposed by the subsequent expression. Reproduced on MySQL 9.6.0 and 26.7.0 using the mysql command-line client. Only the two tables and inserts below are needed. How to repeat: Run the following in a schema where these table names do not already exist: CREATE TABLE t1_func_gconcat (a INT, c INT); INSERT INTO t1_func_gconcat VALUES (1, 2), (2, 3), (2, 4), (3, 5); CREATE TABLE t2_func_gconcat (a INT, c INT); INSERT INTO t2_func_gconcat VALUES (1, 5), (2, 4), (3, 3), (3, 3); -- Query 1: returns a correctly ordered result. SELECT GROUP_CONCAT( c ORDER BY ( SELECT MID(GROUP_CONCAT(c ORDER BY a SEPARATOR ','), 1, 5) FROM t2_func_gconcat WHERE t2_func_gconcat.a = t1_func_gconcat.a ) DESC SEPARATOR ',' ) AS col1 FROM t1_func_gconcat; -- Query 2: adding a constant ordering item produces an incorrect result. SELECT GROUP_CONCAT( c ORDER BY TRUE, ( SELECT MID(GROUP_CONCAT(c ORDER BY a SEPARATOR ','), 1, 5) FROM t2_func_gconcat WHERE t2_func_gconcat.a = t1_func_gconcat.a ) DESC SEPARATOR ',' ) AS col1 FROM t1_func_gconcat; Actual results: Query1: 2, 4, 3, 5 Query2: 5, 4, 3, 2 Replacing TRUE with FALSE in Query 2 also returns 5,4,3,2. Expected result: Both queries should return either 2,3,4,5 or 2,4,3,5. Suggested fix: Ensure that constant items in GROUP_CONCAT()'s ORDER BY list do not interfere with the evaluation or comparison of subsequent ordering expressions. Add regression coverage for leading TRUE and FALSE items followed by a correlated scalar subquery.