MySQL Bugs: #110656: Join query with wrong result

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

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.

Hello Pedro Ferreira,

Thank you for the report and feedback.

regards,
Umesh

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

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