MySQL Bugs: #120403: Regression in MySQL 8.0.46, 8.4.9 & 9.7.0: WHERE equality condition fails to match existing row

Bug #120403	Regression in MySQL 8.0.46, 8.4.9 & 9.7.0: WHERE equality condition fails to match existing row
Submitted:	5 May 13:47	Modified:	12 Aug 16:14
Reporter:	M F	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.46, 8.4.9, 9.7.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	char, condition, condition pushdown, Empty Result Set, regression, where clause

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

Thanks for the report,  verified as described!

same problem when testing 9.6.0 vs 9.7.0

> same problem when testing 9.6.0 vs 9.7.0

And same problem when testing 8.0.45 vs 8.0.46.

I cannot update Version, please add 9.7.0 and 8.0.46.  Also please add 8.0.46 in Title.

It was caused by this commit(389486f): 

Bug#35912840: Conditions refering to const table were not added to RANGE SCAN.

Posted by developer: Bug status updated to 'Documenting'

Hello,
the version 8.4.10 is also affected. We have already raised an SR for Oracle to prioritize this issue but nothing seem to happened.
According to the release notes this issue is still not fixed in 8.4.11 and we are unable to patch some of our production systems due to this issue.
The suggested workarounds deliver a devastating drop in performance: queries taking 5 seconds instead of 0.05s.

Hi Balazs,

a fix has been pushed to 8.4.11 and 9.7.2, however unfortunately the bug report has not been updated yet.

> a fix has been pushed to 8.4.11 and 9.7.2

What is the Release Note item matching this fix ?  Was this also fixed in 26.7.0 ?

When I check the Release Notes for 8.4.11 [1] and for 9.7.2 [2], I do not find any mention of 120403: is this expected ?

[1]: https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-11.html

[2]: https://dev.mysql.com/doc/relnotes/mysql/9.7/en/news-9-7-2.html

That's my point as well. I started to complain since the release notes don't contain any reference to this issue and I don't see why we as paying customer should test every version on our own instead have the bugfixes documented.
Cheers

Posted by developer:
 
Added the following note to the MySQL Server 8.4.11, 9.7.2, and 26.7.0 release notes:

	A fix in a previous version caused an issue where a WHERE
        equality condition could fail to match an existing row when a
        scalar subquery and its outer query referenced the same CTE.

        This fix prevents cleanup of derived-table temporary keys from a
        query block that does not own them, preserving correct key
        metadata during outer-query optimization and ensuring correct
        result matching.

Thanks for fixing the release notes Edward.