MDEV-4836: Merge into 5.5-main

1 files changed, 33 insertions, 0 deletions

diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 0eccb8e1587..7a70c413e8d 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -1686,6 +1686,39 @@ explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or
 
 drop table t0, t1;
 
+--echo #
+--echo # MDEV-4836: Wrong result on <not null date column> IS NULL (old documented hack stopped working)
+--echo #  (this is a regression after fix for MDEV-4817)
+--echo #
+CREATE TABLE t1 (id INT, d DATE NOT NULL);
+INSERT INTO t1 VALUES (1,'0000-00-00'),(2,'0000-00-00');
+CREATE TABLE t2 (i INT);
+SELECT * FROM t1 LEFT JOIN t2 ON (id=i) WHERE NULL OR d IS NULL;
+DROP TABLE t1,t2;
+
+
+CREATE TABLE t1 (i1 INT, d1 DATE NOT NULL);
+INSERT INTO t1 VALUES (1,'2012-12-21'),(2,'0000-00-00');
+
+CREATE TABLE t2 (i2 INT, j2 INT);
+INSERT INTO t2 VALUES (1,10),(2,20);
+
+SELECT * FROM t1 LEFT JOIN t2 ON i1 = j2 WHERE d1 IS NULL AND 1 OR i1 = i2;
+DROP TABLE t1,t2;
+
+--echo # Another testcase
+CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (NULL);
+
+CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM;
+CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
+
+INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3);
+SELECT * FROM t1 LEFT JOIN v2 ON i1 = i2 WHERE a < b;
+SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE a < b;
+
+drop view v2;
+drop table t1,t2;
 
 --echo #
 --echo # Bug mdev-4942: LEFT JOIN with conjunctive