| Bug #114559 | Unexpected Results by the hint SUBQUERY(MATERIALIZATION) | ||
|---|---|---|---|
| Submitted: | 5 Apr 2024 12:12 | Modified: | 5 Apr 2024 12:26 |
| Reporter: | John Jove | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[5 Apr 2024 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.
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); -- {}