| Bug #115726 | A left join with an impossible condition cause unexpected result | ||
|---|---|---|---|
| Submitted: | 30 Jul 2024 11:28 | Modified: | 1 Aug 2024 6:37 |
| Reporter: | Fenfang Li Fenfang Li | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.35 | OS: | Linux |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | MySQL | ||
[30 Jul 2024 11:43]
MySQL Verification Team
Hi Mr. Li, Thank you for your bug report. We do not see why is that unexpected result. When the Optimiser runs into an impossible condition, it returns the empty set. That is expected behaviour, that is prescribed by SQL Standard 2016. Not a bug.
[30 Jul 2024 15:07]
Fenfang Li Fenfang Li
Thanks for your quick response. But i still have a question.i can get 1 record with MySQL5.7.x. so is there any change from 5.7 to 8.0? or caused by SQL Standard 2016 don't applied in MySQL5.7 ? -- In MySQL5.7 add a impossible condition 't1.id is null' return 1 record MySQL [sbtest]> SELECT t1.*, t2.* FROM test_table1 t1 LEFT JOIN test_table2 t2 ON t1.id = t2.id WHERE t2.datetime_col is null or t1.id is null; +----+----------+------+---------------------+ | id | datetime | id | datetime_col | +----+----------+------+---------------------+ | 1 | NULL | 1 | 0000-00-00 00:00:00 | +----+----------+------+---------------------+ 1 row in set (0.001 sec)
[30 Jul 2024 15:11]
MySQL Verification Team
Hi, That is actually a bug in 5.7, but 5.7 is not maintained for more then a year now ......
[1 Aug 2024 6:37]
Fenfang Li Fenfang Li
But logically speaking, the result returned by 5.7 is what we want, and it also conforms to the logic of SQL true or false = true
[1 Aug 2024 9:16]
MySQL Verification Team
Hi, MySQL conforms with SQL Standard, which prescribes that impossible condition should result in the empty set being returned. SQL Standard is available on-line and is not maintained by us.
[2 Aug 2024 7:03]
Roy Lyseng
Adding an "impossible" condition with an OR to the WHERE clause causes a smaller result set. This is apparently wrong, however this is caused by a special MySQL extension, so the result is actually correct. With the simple predicate "t2.datetime_col IS NULL", we have an IS NULL check against a DATETIME column that is NOT NULL. If this column has the special value "0000-00-00", the predicate actually returns TRUE. This is a rather awkward extension targeted at ODBC drivers that cannot handle the "0000-00-00" value directly. But this extension is limited to simple WHERE clauses. When the WHERE clause is extended with another predicate, the extension is not enforced. Thus, users should be advised not to use the special zero value for DATE values (and zero values for month and day components of a DATE).
[2 Aug 2024 9:36]
MySQL Verification Team
Thank you, Roy.
Description: A left join with an impossible condition cause unexpected result How to repeat: CREATE TABLE test_table1 ( id INT AUTO_INCREMENT PRIMARY KEY, datetime DATETIME ); CREATE TABLE test_table2 ( id INT AUTO_INCREMENT PRIMARY KEY, datetime_col DATETIME NOT NULL ); INSERT INTO test_table1 (datetime) VALUES (NULL), ('2023-04-13 10:00:00'); INSERT INTO test_table2 (datetime_col) VALUES (NULL), ('2023-04-13 10:00:00'); MySQL [sbtest]> SELECT t1.*, t2.* FROM test_table1 t1 LEFT JOIN test_table2 t2 ON t1.id = t2.id; +----+---------------------+------+---------------------+ | id | datetime | id | datetime_col | +----+---------------------+------+---------------------+ | 1 | NULL | 1 | 0000-00-00 00:00:00 | | 2 | 2023-04-13 10:00:00 | 2 | 2023-04-13 10:00:00 | +----+---------------------+------+---------------------+ 2 rows in set (0.001 sec) MySQL [sbtest]> SELECT t1.*, t2.* FROM test_table1 t1 LEFT JOIN test_table2 t2 ON t1.id = t2.id WHERE t2.datetime_col is null; +----+----------+------+---------------------+ | id | datetime | id | datetime_col | +----+----------+------+---------------------+ | 1 | NULL | 1 | 0000-00-00 00:00:00 | +----+----------+------+---------------------+ 1 row in set (0.001 sec) -- add a impossible condition 't1.id is null' return unexpected result MySQL [sbtest]> SELECT t1.*, t2.* FROM test_table1 t1 LEFT JOIN test_table2 t2 ON t1.id = t2.id WHERE t2.datetime_col is null or t1.id is null; Empty set (0.001 sec) MySQL [sbtest]> explain SELECT t1.*, t2.* FROM test_table1 t1 LEFT JOIN test_table2 t2 ON t1.id = t2.id WHERE t2.datetime_col is null or t1.id is null; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 2 | 50.00 | Using where; Not exists; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------+ 2 rows in set, 1 warning (0.001 sec) MySQL [sbtest]> show warnings; +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `sbtest`.`t1`.`id` AS `id`,`sbtest`.`t1`.`datetime` AS `datetime`,`sbtest`.`t2`.`id` AS `id`,`sbtest`.`t2`.`datetime_col` AS `datetime_col` from `sbtest`.`test_table1` `t1` left join `sbtest`.`test_table2` `t2` on((`sbtest`.`t2`.`id` = `sbtest`.`t1`.`id`)) where (`sbtest`.`t2`.`datetime_col` is null) | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.001 sec) --run the query rewritten by mysql with different result MySQL [sbtest]> select `sbtest`.`t1`.`id` AS `id`,`sbtest`.`t1`.`datetime` AS `datetime`,`sbtest`.`t2`.`id` AS `id`,`sbtest`.`t2`.`datetime_col` AS `datetime_col` from `sbtest`.`test_table1` `t1` left join `sbtest`.`test_table2` `t2` on((`sbtest`.`t2`.`id` = `sbtest`.`t1`.`id`)) where (`sbtest`.`t2`.`datetime_col` is null); +----+----------+------+---------------------+ | id | datetime | id | datetime_col | +----+----------+------+---------------------+ | 1 | NULL | 1 | 0000-00-00 00:00:00 | +----+----------+------+---------------------+ 1 row in set (0.001 sec)