Bug #114559 Unexpected Results by the hint SUBQUERY(MATERIALIZATION)
Submitted: 5 Apr 12:12 Modified: 5 Apr 12:26
Reporter: John Jove Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[5 Apr 12:12] John Jove
Description:
I run the statement, in which the same query is enforced to run in different plans by the hint /*+ SUBQUERY(MATERIALIZATION) */. However, I got an unexpected result as follows.

How to repeat:
CREATE TABLE `t1` (`c1` decimal(32,9), `c2` double NOT NULL);
INSERT INTO t1 VALUES (12.9,1);
SELECT c1 FROM t1 WHERE ((INET_ATON(c1)) <= ANY (SELECT c2 FROM t1)) NOT IN (SELECT c1 FROM t1); -- {12.900000000}
SELECT c1 FROM t1 WHERE ((INET_ATON(c1)) <= ANY (SELECT c2 FROM t1)) NOT IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ c1 FROM t1); -- {}
[5 Apr 12:26] MySQL Verification Team
Hi Mr. Jove,

Thank you for your bug report.

We managed to verify your bug report with 8.0.37:

c1
12.900000000
------------------
------------------

Those dashes have been printed after the first query.

Interestingly, this bug is already fixed in 8.3.0, because it outputs VERY consistent results:

------------------
------------------

Hence, this is a verified bug, which only requires the backport of the fix from 8.3 / 8.4 to 8.0.

Verified.