Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
Repeatable with maria-5.3,maria-5.2,maria-5.1,mysql-5.1. Not repeatable with mysql-5.5
Having a WHERE t3.a IS NULL in a query causes "NULL" to be returned. The same query without the WHERE returns "19".
explain:
explain SELECT t3.a FROM t1 LEFT JOIN (( t2 LEFT JOIN t3 ON t2.a = t3.b ) LEFT JOIN t4 ON t3.a = t4.b) ON t1.a = t2.a WHERE t3.a IS NULL;
---------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------+
| 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Not exists |
| 1 | SIMPLE | t4 | ALL | NULL | NULL | NULL | NULL | 0 | Using where |
---------------------------------------------------------------------------------+
test case:
CREATE TABLE t1 (a int NOT NULL );
INSERT INTO t1 VALUES (9);
CREATE TABLE t2 (a int NOT NULL );
INSERT INTO t2 VALUES (9);
CREATE TABLE t3 (b int, a int NOT NULL);
INSERT INTO t3 VALUES (9,19);
CREATE TABLE t4 (b int) ;
SELECT t3.a FROM t1 LEFT JOIN (( t2 LEFT JOIN t3 ON t2.a = t3.b ) LEFT JOIN t4 ON t3.a = t4.b) ON t1.a = t2.a WHERE t3.a IS NULL;
SELECT t3.a FROM t1 LEFT JOIN (( t2 LEFT JOIN t3 ON t2.a = t3.b ) LEFT JOIN t4 ON t3.a = t4.b) ON t1.a = t2.a;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result with NOT NULL and IS NULL
This bug report is a manifestation of a wider problem of using not_exist optimization for nested outer joins in 5.1.
Originally the problem was reported for bug #49322. The fix for the bug submitted by me on 2010-09-09 and applied to the 5.3 tree only did not handle the problem correctly. That's why this new bug #817360 popped up.
Maybe it makes sense to fix the problem in the 5.1/5.2 tree as well.
Launchpad bug id: 817360
Re: Wrong result with NOT NULL and IS NULL
Igor, please decide in what version to fix this bug.