Bug #110656 | Join query with wrong result | ||
---|---|---|---|
Submitted: | 11 Apr 2023 16:02 | Modified: | 26 Jan 2024 9:27 |
Reporter: | Pedro Ferreira | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 8.0.32 | OS: | Ubuntu (22.04) |
Assigned to: | CPU Architecture: | x86 (x86_64) | |
Tags: | join |
[11 Apr 2023 16:02]
Pedro Ferreira
[12 Apr 2023 9:39]
MySQL Verification Team
Hello Pedro Ferreira, Thank you for the report and feedback. regards, Umesh
[18 Apr 2023 8:57]
Catalin Besleaga
Posted by developer: Query gets re-written to: select '0000-00-00 00:00:00' AS `c0` from `test`.`t0` join `test`.`t1` where true; Seems like re-writing "t1.c0 IS NULL" to "IS TRUE" is wrong
[26 Jan 2024 9:27]
Pedro Ferreira
I add another situation similar to this one on version 8.3.0 CREATE TABLE t0 (c0 DATE PRIMARY KEY); INSERT INTO t0 (c0) VALUES (DATE '2010-1-10'); INSERT INTO t0 SET c0 = c0; SELECT 1 FROM t0 WHERE t0.c0 IS NULL; --returns one row, wrong a PK cannot be NULL