Wrong result with NOT NULL and IS NULL

[Philip Stoev]

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;

[Timour Katchaounov (timour)]

Igor, please decide in what version to fix this bug.

[Igor Babaev (igorb-seattle)]

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.