Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
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;