Description:
MySQL 9.6.0 returns different results for two logically equivalent UNION ALL queries when the base table is replaced with a simple view. The issue was found in a workload that includes a semantically neutral window-function wrapper using ROW_NUMBER().
Extracting t2 into V_t2_07_wf changes the first row of the UNION ALL result.
In the reproduced case, the original query returns 0x2A for the MAX(c12) branch, while the transformed query returns 0x3432. The second BLOB row remains 0xAB in both forms.
Expected result:
Both queries should return the same two rows.
+-----------+
| col_2_hex |
+-----------+
| 2A |
| AB |
+-----------+
Actual result:
-- original query
+-----------+
| col_2_hex |
+-----------+
| 2A |
| AB |
+-----------+
-- transformed query
+-----------+
| col_2_hex |
+-----------+
| 3432 |
| AB |
+-----------+
How to repeat:
DROP DATABASE IF EXISTS t1;
CREATE DATABASE t1;
USE t1;
CREATE TABLE t2 (
c9 BLOB,
c12 BIT(8),
c14 TINYINT
);
INSERT INTO t2 VALUES (X'AB', b'00101010', 2);
WITH wf_anchor AS (
SELECT
grp_id,
ord_id,
ROW_NUMBER() OVER (PARTITION BY grp_id ORDER BY ord_id) AS wf_rn
FROM (
SELECT 1 AS grp_id, 1 AS ord_id
UNION ALL
SELECT 1, 2
) AS seed
)
SELECT HEX(col_2) AS col_2_hex
FROM (
SELECT MAX(c12) AS col_2
FROM t2
WHERE EXISTS (
SELECT 1
FROM wf_anchor
WHERE grp_id = 1 AND wf_rn = 1
)
GROUP BY c14
UNION ALL
SELECT c9 AS col_2
FROM t2
WHERE EXISTS (
SELECT 1
FROM wf_anchor
WHERE grp_id = 1 AND wf_rn = 1
)
) AS q;
CREATE VIEW V_t2_07_wf AS
SELECT * FROM t2;
WITH wf_anchor AS (
SELECT
grp_id,
ord_id,
ROW_NUMBER() OVER (PARTITION BY grp_id ORDER BY ord_id) AS wf_rn
FROM (
SELECT 1 AS grp_id, 1 AS ord_id
UNION ALL
SELECT 1, 2
) AS seed
)
SELECT HEX(col_2) AS col_2_hex
FROM (
SELECT MAX(c12) AS col_2
FROM V_t2_07_wf
WHERE EXISTS (
SELECT 1
FROM wf_anchor
WHERE grp_id = 1 AND wf_rn = 1
)
GROUP BY c14
UNION ALL
SELECT c9 AS col_2
FROM t2
WHERE EXISTS (
SELECT 1
FROM wf_anchor
WHERE grp_id = 1 AND wf_rn = 1
)
) AS q;
Description: MySQL 9.6.0 returns different results for two logically equivalent UNION ALL queries when the base table is replaced with a simple view. The issue was found in a workload that includes a semantically neutral window-function wrapper using ROW_NUMBER(). Extracting t2 into V_t2_07_wf changes the first row of the UNION ALL result. In the reproduced case, the original query returns 0x2A for the MAX(c12) branch, while the transformed query returns 0x3432. The second BLOB row remains 0xAB in both forms. Expected result: Both queries should return the same two rows. +-----------+ | col_2_hex | +-----------+ | 2A | | AB | +-----------+ Actual result: -- original query +-----------+ | col_2_hex | +-----------+ | 2A | | AB | +-----------+ -- transformed query +-----------+ | col_2_hex | +-----------+ | 3432 | | AB | +-----------+ How to repeat: DROP DATABASE IF EXISTS t1; CREATE DATABASE t1; USE t1; CREATE TABLE t2 ( c9 BLOB, c12 BIT(8), c14 TINYINT ); INSERT INTO t2 VALUES (X'AB', b'00101010', 2); WITH wf_anchor AS ( SELECT grp_id, ord_id, ROW_NUMBER() OVER (PARTITION BY grp_id ORDER BY ord_id) AS wf_rn FROM ( SELECT 1 AS grp_id, 1 AS ord_id UNION ALL SELECT 1, 2 ) AS seed ) SELECT HEX(col_2) AS col_2_hex FROM ( SELECT MAX(c12) AS col_2 FROM t2 WHERE EXISTS ( SELECT 1 FROM wf_anchor WHERE grp_id = 1 AND wf_rn = 1 ) GROUP BY c14 UNION ALL SELECT c9 AS col_2 FROM t2 WHERE EXISTS ( SELECT 1 FROM wf_anchor WHERE grp_id = 1 AND wf_rn = 1 ) ) AS q; CREATE VIEW V_t2_07_wf AS SELECT * FROM t2; WITH wf_anchor AS ( SELECT grp_id, ord_id, ROW_NUMBER() OVER (PARTITION BY grp_id ORDER BY ord_id) AS wf_rn FROM ( SELECT 1 AS grp_id, 1 AS ord_id UNION ALL SELECT 1, 2 ) AS seed ) SELECT HEX(col_2) AS col_2_hex FROM ( SELECT MAX(c12) AS col_2 FROM V_t2_07_wf WHERE EXISTS ( SELECT 1 FROM wf_anchor WHERE grp_id = 1 AND wf_rn = 1 ) GROUP BY c14 UNION ALL SELECT c9 AS col_2 FROM t2 WHERE EXISTS ( SELECT 1 FROM wf_anchor WHERE grp_id = 1 AND wf_rn = 1 ) ) AS q;