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:
None 
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
Description:
Run these statements:

CREATE TABLE t0 (c0 INT, c2 INT, PRIMARY KEY (c0));
CREATE TABLE t1 (c0 DATETIME, PRIMARY KEY (c0));
INSERT IGNORE INTO t0(c2) VALUES (1);
INSERT INTO t0(c0,c2) VALUES (1,2),(3,4);
INSERT IGNORE INTO t1(c0) VALUES (NULL);

Then this join:

SELECT t1.c0 FROM t0 JOIN t1 USING (c0) WHERE t1.c0 IS NULL;

It outputs 0000-00-00 00:00:00, but this value is not NULL, so this is a wrong result.

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the queries above.
[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