Fixed bug mdev-4962.

When a non-nullable datetime field is used under an IS NULL predicate
of the WHERE condition in a query with outer joins the remove_eq_conds
function should check whether this field belongs to an inner table
of any outer join that can be, in a general case, a nested outer join.

3 files changed, 75 insertions, 0 deletions

diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index c30f1584607..284a285dbe3 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1862,4 +1862,30 @@ SELECT * FROM t1 LEFT JOIN t2 ON a1 = a2
 WHERE ( dt IS NULL OR FALSE ) AND b2 IS NULL;
 a1	b1	dt	a2	b2
 DROP TABLE t1,t2;
+#
+# Bug mdev-4962: nested outer join with 
+#                <non-nullable datetime field> IS NULL in WHERE 
+#                causes an assert failure  
+#
+CREATE TABLE t1 (i1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (i2 int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (10),(20);
+CREATE TABLE t3 (i3 int, d3 datetime NOT NULL) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (8,'2008-12-04 17:53:42'),(9,'2012-12-21 12:12:12');
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3
+WHERE d3 IS NULL;
+i1	i2	i3	d3
+1	NULL	NULL	NULL
+2	NULL	NULL	NULL
+EXPLAIN EXTENDED
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3
+WHERE d3 IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`d3` AS `d3` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`i2` = `test`.`t1`.`i1`) and (`test`.`t3`.`i3` = `test`.`t1`.`i1`))) where ((`test`.`t3`.`d3` = 0) or isnull(`test`.`t3`.`d3`))
+DROP TABLE t1,t2,t3;
 SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index ccb402a642c..69fe7cbd6b9 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -1873,6 +1873,32 @@ SELECT * FROM t1 LEFT JOIN t2 ON a1 = a2
 WHERE ( dt IS NULL OR FALSE ) AND b2 IS NULL;
 a1	b1	dt	a2	b2
 DROP TABLE t1,t2;
+#
+# Bug mdev-4962: nested outer join with 
+#                <non-nullable datetime field> IS NULL in WHERE 
+#                causes an assert failure  
+#
+CREATE TABLE t1 (i1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (i2 int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (10),(20);
+CREATE TABLE t3 (i3 int, d3 datetime NOT NULL) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (8,'2008-12-04 17:53:42'),(9,'2012-12-21 12:12:12');
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3
+WHERE d3 IS NULL;
+i1	i2	i3	d3
+1	NULL	NULL	NULL
+2	NULL	NULL	NULL
+EXPLAIN EXTENDED
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3
+WHERE d3 IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`d3` AS `d3` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`i2` = `test`.`t1`.`i1`) and (`test`.`t3`.`i3` = `test`.`t1`.`i1`))) where ((`test`.`t3`.`d3` = 0) or isnull(`test`.`t3`.`d3`))
+DROP TABLE t1,t2,t3;
 SET optimizer_switch=@save_optimizer_switch;
 set join_cache_level=default;
 show variables like 'join_cache_level';
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index ec48818f57f..6a1dac66967 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -1410,4 +1410,27 @@ SELECT * FROM t1 LEFT JOIN t2 ON a1 = a2
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # Bug mdev-4962: nested outer join with 
+--echo #                <non-nullable datetime field> IS NULL in WHERE 
+--echo #                causes an assert failure  
+--echo #
+
+CREATE TABLE t1 (i1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (i2 int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (10),(20);
+
+CREATE TABLE t3 (i3 int, d3 datetime NOT NULL) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (8,'2008-12-04 17:53:42'),(9,'2012-12-21 12:12:12');
+
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3
+  WHERE d3 IS NULL;
+EXPLAIN EXTENDED
+SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3
+  WHERE d3 IS NULL;
+
+DROP TABLE t1,t2,t3;
+
 SET optimizer_switch=@save_optimizer_switch;