Description:
After upgrading from MySQL Server 8.4.8 to 8.4.9, a regression was observed where a query that previously returned a matching row now returns no rows when using an equality condition in the WHERE clause.
Observed behaviour:
- The query returns 0 rows in MySQL 8.4.9 when using a WHERE predicate.
- The query returns 1 expected row in MySQL 8.4.8 with identical schema, data, and query.
- The row is present and visible when executing the query without the WHERE predicate.
- The issue occurs even with only one row in each involved table.
- The issue reproduces when the column is defined as utf8mb4_general_ci.
- The issue does not reproduce when using utf8mb4_0900_ai_ci.
- Character set and collation of the column and the literal are identical.
- Replacing the WHERE predicate with an equivalent HAVING clause returns the expected row.
- Explicitly casting the filtered column (e.g. CAST(p.uuid AS CHAR(36))) also returns the expected row.
- Removing any of the JOINs also returns the expected row.
- Adding "LIMIT 18446744073709551615" returns the expected row for Query 2, but not for Query 1.
- Setting SET SESSION optimizer_switch='derived_condition_pushdown=off' returns the expected row for Query 2, but not for Query 1.
Expected behaviour:
The query should return the same result in MySQL 8.4.9 as in MySQL 8.4.8, returning the matching row without any additional changes to the query.
How to repeat:
CREATE DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE test;
CREATE TABLE parent (
uuid CHAR(36) NOT NULL PRIMARY KEY
);
CREATE TABLE child (
uuid CHAR(36) NOT NULL PRIMARY KEY,
id VARCHAR(255) NOT NULL,
version INT NOT NULL,
parent_uuid CHAR(36) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
FOREIGN KEY (parent_uuid) REFERENCES parent (uuid)
);
CREATE TABLE sub_child(
uuid CHAR(36) NOT NULL PRIMARY KEY,
child_uuid CHAR(36) NOT NULL,
FOREIGN KEY (child_uuid) REFERENCES child (uuid)
);
INSERT INTO parent VALUES
('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa');
INSERT INTO child VALUES
(UUID(), "A", 1, 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', CURRENT_TIMESTAMP());
INSERT INTO sub_child
SELECT UUID(), c.uuid
FROM child c;
-- Query 1:
WITH ranked_children AS (
SELECT
c.uuid AS child_uuid,
c.parent_uuid,
c.created_at,
ROW_NUMBER() OVER (
PARTITION BY c.id
ORDER BY c.version DESC
) AS rn
FROM child c
GROUP BY c.uuid
)
SELECT p.uuid
FROM parent p
JOIN ranked_children all_rc ON p.uuid = all_rc.parent_uuid AND all_rc.rn = 1
JOIN sub_child all_sc ON all_sc.child_uuid = all_rc.child_uuid
JOIN ranked_children rc ON rc.child_uuid = (
select rc2.child_uuid
from ranked_children rc2
where p.uuid = rc2.parent_uuid and rc2.rn = 1
order by rc2.created_at desc
limit 1
)
JOIN sub_child sc ON sc.child_uuid = rc.child_uuid
WHERE p.uuid = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'
GROUP BY p.uuid;
-- Query 2:
SELECT t.uuid FROM (WITH ranked_children AS (
SELECT
c.uuid AS child_uuid,
c.parent_uuid,
c.created_at,
ROW_NUMBER() OVER (
PARTITION BY c.id
ORDER BY c.version DESC
) AS rn
FROM child c
GROUP BY c.uuid
)
SELECT p.uuid
FROM parent p
JOIN ranked_children all_rc ON p.uuid = all_rc.parent_uuid AND all_rc.rn = 1
JOIN sub_child all_sc ON all_sc.child_uuid = all_rc.child_uuid
JOIN ranked_children rc ON rc.child_uuid = (
select rc2.child_uuid
from ranked_children rc2
where p.uuid = rc2.parent_uuid and rc2.rn = 1
order by rc2.created_at desc
limit 1
)
JOIN sub_child sc ON sc.child_uuid = rc.child_uuid
GROUP BY p.uuid) AS t
WHERE t.uuid = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
Suggested fix:
Workarounds as mentioned before:
For Query 1 and 2:
- Casting column to CHAR
- Using HAVING instead of WHERE
Only for Query 2:
- Adding "LIMIT 18446744073709551615"
- Disabling derived_condition_pushdown
Description: After upgrading from MySQL Server 8.4.8 to 8.4.9, a regression was observed where a query that previously returned a matching row now returns no rows when using an equality condition in the WHERE clause. Observed behaviour: - The query returns 0 rows in MySQL 8.4.9 when using a WHERE predicate. - The query returns 1 expected row in MySQL 8.4.8 with identical schema, data, and query. - The row is present and visible when executing the query without the WHERE predicate. - The issue occurs even with only one row in each involved table. - The issue reproduces when the column is defined as utf8mb4_general_ci. - The issue does not reproduce when using utf8mb4_0900_ai_ci. - Character set and collation of the column and the literal are identical. - Replacing the WHERE predicate with an equivalent HAVING clause returns the expected row. - Explicitly casting the filtered column (e.g. CAST(p.uuid AS CHAR(36))) also returns the expected row. - Removing any of the JOINs also returns the expected row. - Adding "LIMIT 18446744073709551615" returns the expected row for Query 2, but not for Query 1. - Setting SET SESSION optimizer_switch='derived_condition_pushdown=off' returns the expected row for Query 2, but not for Query 1. Expected behaviour: The query should return the same result in MySQL 8.4.9 as in MySQL 8.4.8, returning the matching row without any additional changes to the query. How to repeat: CREATE DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE test; CREATE TABLE parent ( uuid CHAR(36) NOT NULL PRIMARY KEY ); CREATE TABLE child ( uuid CHAR(36) NOT NULL PRIMARY KEY, id VARCHAR(255) NOT NULL, version INT NOT NULL, parent_uuid CHAR(36) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(), FOREIGN KEY (parent_uuid) REFERENCES parent (uuid) ); CREATE TABLE sub_child( uuid CHAR(36) NOT NULL PRIMARY KEY, child_uuid CHAR(36) NOT NULL, FOREIGN KEY (child_uuid) REFERENCES child (uuid) ); INSERT INTO parent VALUES ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'); INSERT INTO child VALUES (UUID(), "A", 1, 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', CURRENT_TIMESTAMP()); INSERT INTO sub_child SELECT UUID(), c.uuid FROM child c; -- Query 1: WITH ranked_children AS ( SELECT c.uuid AS child_uuid, c.parent_uuid, c.created_at, ROW_NUMBER() OVER ( PARTITION BY c.id ORDER BY c.version DESC ) AS rn FROM child c GROUP BY c.uuid ) SELECT p.uuid FROM parent p JOIN ranked_children all_rc ON p.uuid = all_rc.parent_uuid AND all_rc.rn = 1 JOIN sub_child all_sc ON all_sc.child_uuid = all_rc.child_uuid JOIN ranked_children rc ON rc.child_uuid = ( select rc2.child_uuid from ranked_children rc2 where p.uuid = rc2.parent_uuid and rc2.rn = 1 order by rc2.created_at desc limit 1 ) JOIN sub_child sc ON sc.child_uuid = rc.child_uuid WHERE p.uuid = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa' GROUP BY p.uuid; -- Query 2: SELECT t.uuid FROM (WITH ranked_children AS ( SELECT c.uuid AS child_uuid, c.parent_uuid, c.created_at, ROW_NUMBER() OVER ( PARTITION BY c.id ORDER BY c.version DESC ) AS rn FROM child c GROUP BY c.uuid ) SELECT p.uuid FROM parent p JOIN ranked_children all_rc ON p.uuid = all_rc.parent_uuid AND all_rc.rn = 1 JOIN sub_child all_sc ON all_sc.child_uuid = all_rc.child_uuid JOIN ranked_children rc ON rc.child_uuid = ( select rc2.child_uuid from ranked_children rc2 where p.uuid = rc2.parent_uuid and rc2.rn = 1 order by rc2.created_at desc limit 1 ) JOIN sub_child sc ON sc.child_uuid = rc.child_uuid GROUP BY p.uuid) AS t WHERE t.uuid = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'; Suggested fix: Workarounds as mentioned before: For Query 1 and 2: - Casting column to CHAR - Using HAVING instead of WHERE Only for Query 2: - Adding "LIMIT 18446744073709551615" - Disabling derived_condition_pushdown