Details
-
Bug
-
Status: In Review (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.16, 12.2.2
-
None
-
Ubuntu 24.04
-
Q2/2026 Server Development, Q3/2026 Server Maintenance, Q3/2026 Server Development
Description
MariaDB 12.2.2 returns different results for two logically equivalent queries when a derived table is extracted into a view inside a workload that includes a window function.
This issue was found while testing window-function-guided query rewrites. The window-function part is semantically neutral: it uses ROW_NUMBER() OVER (...) only as a one-row anchor in an EXISTS predicate and does not change which rows qualify. Even so, the original derived-table form and the view-extracted form produce different results.
In the reproduced case, the original query returns HEX(GROUP_CONCAT(...)) = 38, while the transformed query returns 08. This means that extracting the grouped BIT(8) projection into a view changes the value representation observed by GROUP_CONCAT, even though the two queries should be equivalent.
DROP DATABASE IF EXISTS t1; |
CREATE DATABASE t1; |
USE t1; |
|
|
CREATE TABLE t2 ( |
c12 BIT(8) NULL |
);
|
|
|
INSERT INTO t2 VALUES (b'00001000'); |
|
|
WITH wf_anchor AS ( |
SELECT |
seed.grp_id,
|
seed.ord_id,
|
ROW_NUMBER() OVER w_main AS wf_rn |
FROM ( |
SELECT 1 AS grp_id, 1 AS ord_id |
UNION ALL |
SELECT 1, 2 |
) AS seed |
WINDOW w_main AS (PARTITION BY seed.grp_id ORDER BY seed.ord_id) |
)
|
SELECT HEX(GROUP_CONCAT(cte.col_4 ORDER BY cte.col_4)) AS col_1_hex |
FROM ( |
SELECT c12 AS col_4 |
FROM t2 |
GROUP BY c12 |
) AS cte |
WHERE EXISTS ( |
SELECT 1 |
FROM wf_anchor |
WHERE grp_id = 1 AND wf_rn = 1 |
);
|
|
|
CREATE VIEW V_cte_6140f8de_wf AS |
SELECT * |
FROM ( |
SELECT c12 AS col_4 |
FROM t2 |
GROUP BY c12 |
) AS cte; |
|
|
WITH wf_anchor AS ( |
SELECT |
seed.grp_id,
|
seed.ord_id,
|
ROW_NUMBER() OVER w_main AS wf_rn |
FROM ( |
SELECT 1 AS grp_id, 1 AS ord_id |
UNION ALL |
SELECT 1, 2 |
) AS seed |
WINDOW w_main AS (PARTITION BY seed.grp_id ORDER BY seed.ord_id) |
)
|
SELECT HEX(GROUP_CONCAT(V_cte_6140f8de_wf.col_4 ORDER BY V_cte_6140f8de_wf.col_4)) AS col_1_hex |
FROM V_cte_6140f8de_wf |
WHERE EXISTS ( |
SELECT 1 |
FROM wf_anchor |
WHERE grp_id = 1 AND wf_rn = 1 |
);
|
For comparison, this did not reproduce on local MySQL 9.6.0, where both forms returned 08.
Attachments
Issue Links
- relates to
-
-
- Closed
-
