Description:
MySQL 9.6.0 returns different results for two logically equivalent aggregate queries when the only semantic change is the order of expressions in the GROUP BY list.
The issue is reproducible in a query where one side of the join is wrapped in nested CTEs and decorated with window functions. The window-function layer is semantically neutral in this case: ROW_NUMBER() and DENSE_RANK() are computed, but the predicates wf_rn >= 1 and wf_dr >= 1 do not filter out any rows from the reproduced data.
Even so, the two aggregate queries return different results:
Query A groups by (s3_wf.grp_id, s1.tag) and returns one row
Query B groups by (s1.tag, s3_wf.grp_id) and returns an empty result
These two queries should be equivalent, so the result should not change.
How to repeat:
DROP DATABASE IF EXISTS t1;
CREATE DATABASE t1;
USE t1;
CREATE TABLE s1 (
id INT NOT NULL,
tag CHAR(2) NOT NULL,
payload BINARY(1),
flags SET('a','b','c','d') NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE s3 (
grp_id INT NOT NULL,
ref_id INT NOT NULL
);
INSERT INTO s1 VALUES (4101, 'qz', X'02', 'a,c,d');
INSERT INTO s3 VALUES (7200, 4101);
-- Query A
WITH s3_base AS (
SELECT grp_id, ref_id
FROM s3
),
s3_wf AS (
SELECT
grp_id,
ref_id,
ROW_NUMBER() OVER (PARTITION BY grp_id ORDER BY ref_id) AS wf_rn,
DENSE_RANK() OVER (ORDER BY grp_id, ref_id) AS wf_dr
FROM s3_base
)
SELECT s3_wf.grp_id AS grp_id,
s1.tag AS tag,
COUNT(DISTINCT s1.payload) AS cnt
FROM s1
INNER JOIN s3_wf ON s3_wf.ref_id = s1.id
WHERE s3_wf.grp_id IS NOT NULL
AND s3_wf.wf_rn >= 1
AND s3_wf.wf_dr >= 1
GROUP BY s3_wf.grp_id, s1.tag
HAVING MIN(s1.flags) >= 6
ORDER BY s1.tag DESC;
-- Query B
WITH s3_base AS (
SELECT grp_id, ref_id
FROM s3
),
s3_wf AS (
SELECT
grp_id,
ref_id,
ROW_NUMBER() OVER (PARTITION BY grp_id ORDER BY ref_id) AS wf_rn,
DENSE_RANK() OVER (ORDER BY grp_id, ref_id) AS wf_dr
FROM s3_base
)
SELECT s3_wf.grp_id AS grp_id,
s1.tag AS tag,
COUNT(DISTINCT s1.payload) AS cnt
FROM s1
INNER JOIN s3_wf ON s3_wf.ref_id = s1.id
WHERE s3_wf.grp_id IS NOT NULL
AND s3_wf.wf_rn >= 1
AND s3_wf.wf_dr >= 1
GROUP BY s1.tag, s3_wf.grp_id
HAVING MIN(s1.flags) >= 6
ORDER BY s1.tag DESC;
Description: MySQL 9.6.0 returns different results for two logically equivalent aggregate queries when the only semantic change is the order of expressions in the GROUP BY list. The issue is reproducible in a query where one side of the join is wrapped in nested CTEs and decorated with window functions. The window-function layer is semantically neutral in this case: ROW_NUMBER() and DENSE_RANK() are computed, but the predicates wf_rn >= 1 and wf_dr >= 1 do not filter out any rows from the reproduced data. Even so, the two aggregate queries return different results: Query A groups by (s3_wf.grp_id, s1.tag) and returns one row Query B groups by (s1.tag, s3_wf.grp_id) and returns an empty result These two queries should be equivalent, so the result should not change. How to repeat: DROP DATABASE IF EXISTS t1; CREATE DATABASE t1; USE t1; CREATE TABLE s1 ( id INT NOT NULL, tag CHAR(2) NOT NULL, payload BINARY(1), flags SET('a','b','c','d') NOT NULL, PRIMARY KEY (id) ); CREATE TABLE s3 ( grp_id INT NOT NULL, ref_id INT NOT NULL ); INSERT INTO s1 VALUES (4101, 'qz', X'02', 'a,c,d'); INSERT INTO s3 VALUES (7200, 4101); -- Query A WITH s3_base AS ( SELECT grp_id, ref_id FROM s3 ), s3_wf AS ( SELECT grp_id, ref_id, ROW_NUMBER() OVER (PARTITION BY grp_id ORDER BY ref_id) AS wf_rn, DENSE_RANK() OVER (ORDER BY grp_id, ref_id) AS wf_dr FROM s3_base ) SELECT s3_wf.grp_id AS grp_id, s1.tag AS tag, COUNT(DISTINCT s1.payload) AS cnt FROM s1 INNER JOIN s3_wf ON s3_wf.ref_id = s1.id WHERE s3_wf.grp_id IS NOT NULL AND s3_wf.wf_rn >= 1 AND s3_wf.wf_dr >= 1 GROUP BY s3_wf.grp_id, s1.tag HAVING MIN(s1.flags) >= 6 ORDER BY s1.tag DESC; -- Query B WITH s3_base AS ( SELECT grp_id, ref_id FROM s3 ), s3_wf AS ( SELECT grp_id, ref_id, ROW_NUMBER() OVER (PARTITION BY grp_id ORDER BY ref_id) AS wf_rn, DENSE_RANK() OVER (ORDER BY grp_id, ref_id) AS wf_dr FROM s3_base ) SELECT s3_wf.grp_id AS grp_id, s1.tag AS tag, COUNT(DISTINCT s1.payload) AS cnt FROM s1 INNER JOIN s3_wf ON s3_wf.ref_id = s1.id WHERE s3_wf.grp_id IS NOT NULL AND s3_wf.wf_rn >= 1 AND s3_wf.wf_dr >= 1 GROUP BY s1.tag, s3_wf.grp_id HAVING MIN(s1.flags) >= 6 ORDER BY s1.tag DESC;