Fixed mdev-14911: zero_date is considered as NULL, depending on

optimizer_switch

For DATE and DATETIME columns defined as NOT NULL,
"date_notnull IS NULL" has to be modified to:
"date_notnull IS NULL OR date_notnull == 0"
if date_notnull is from an inner table of outer join);
"date_notnull == 0" - otherwise.

This must hold for such columns of mergeable views and derived
tables as well. So far the code did the above re-writing only
for columns of base tables and temporary tables.

3 files changed, 135 insertions, 5 deletions

diff --git a/mysql-test/r/func_isnull.result b/mysql-test/r/func_isnull.result
index 2dbe3d036f9..88c5bfd5468 100644
--- a/mysql-test/r/func_isnull.result
+++ b/mysql-test/r/func_isnull.result
@@ -24,3 +24,87 @@ INSERT INTO t1( id ) VALUES ( NULL );
 SELECT t1.id  FROM t1  WHERE (id  is not null and id is null );
 id
 DROP TABLE t1;
+# End of 5.1 tests
+#
+# MDEV-14911: IS NULL for field from mergeable view
+#
+CREATE TABLE t1 (d1 datetime NOT NULL);
+INSERT INTO t1 VALUES
+('0000-00-00 00:00:00'), ('0000-00-00 00:00:00'), ('1979-09-03 20:49:36');
+SELECT * FROM t1;
+d1
+0000-00-00 00:00:00
+0000-00-00 00:00:00
+1979-09-03 20:49:36
+SELECT * FROM t1 WHERE d1 IS NULL;
+d1
+0000-00-00 00:00:00
+0000-00-00 00:00:00
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE d1 IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`d1` AS `d1` from `test`.`t1` where (`test`.`t1`.`d1` = 0)
+SELECT count(*) FROM t1 WHERE d1 IS NULL;
+count(*)
+2
+CREATE VIEW v1 AS (SELECT * FROM t1);
+SELECT * FROM v1;
+d1
+0000-00-00 00:00:00
+0000-00-00 00:00:00
+1979-09-03 20:49:36
+SELECT * FROM v1 WHERE d1 IS NULL;
+d1
+0000-00-00 00:00:00
+0000-00-00 00:00:00
+EXPLAIN EXTENDED SELECT * FROM v1 WHERE d1 IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`d1` AS `d1` from `test`.`t1` where (`test`.`t1`.`d1` = 0)
+SELECT count(*) FROM v1 WHERE d1 IS NULL;
+count(*)
+2
+SET @save_optimizer_switch=@@optimizer_switch;
+SET SESSION optimizer_switch='derived_merge=off';
+SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL;
+count(*)
+2
+SET SESSION optimizer_switch='derived_merge=on';
+SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL;
+count(*)
+2
+SET optimizer_switch=@save_optimizer_switch;
+CREATE TABLE t2 (d1 datetime NOT NULL);
+INSERT INTO t2 VALUES
+('1980-09-03 20:49:36'),  ('0000-00-00 00:00:00'), ('1979-09-03 20:49:36');
+SELECT * FROM t2 LEFT JOIN t1 ON t2.d1=t1.d1 WHERE t1.d1 IS NULL;
+d1	d1
+0000-00-00 00:00:00	0000-00-00 00:00:00
+0000-00-00 00:00:00	0000-00-00 00:00:00
+1980-09-03 20:49:36	NULL
+EXPLAIN EXTENDED
+SELECT * FROM t2 LEFT JOIN t1 ON t2.d1=t1.d1 WHERE t1.d1 IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t2`.`d1` AS `d1`,`test`.`t1`.`d1` AS `d1` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`d1` = `test`.`t2`.`d1`)) where ((`test`.`t1`.`d1` = 0) or isnull(`test`.`t1`.`d1`))
+SELECT * FROM t2 LEFT JOIN v1 ON t2.d1=v1.d1 WHERE v1.d1 IS NULL;
+d1	d1
+0000-00-00 00:00:00	0000-00-00 00:00:00
+0000-00-00 00:00:00	0000-00-00 00:00:00
+1980-09-03 20:49:36	NULL
+EXPLAIN EXTENDED
+SELECT * FROM t2 LEFT JOIN v1 ON t2.d1=v1.d1 WHERE v1.d1 IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t2`.`d1` AS `d1`,`test`.`t1`.`d1` AS `d1` from `test`.`t2` left join (`test`.`t1`) on((`test`.`t1`.`d1` = `test`.`t2`.`d1`)) where ((`test`.`t1`.`d1` = 0) or isnull(`test`.`t1`.`d1`))
+DROP VIEW v1;
+DROP TABLE t1,t2;
+#
+# End of 5.5 tests
+#
diff --git a/mysql-test/t/func_isnull.test b/mysql-test/t/func_isnull.test
index 326574e0515..4c59fa3cbe8 100644
--- a/mysql-test/t/func_isnull.test
+++ b/mysql-test/t/func_isnull.test
@@ -38,5 +38,51 @@ INSERT INTO t1( id ) VALUES ( NULL );
 SELECT t1.id  FROM t1  WHERE (id  is not null and id is null );
 DROP TABLE t1;
 
-# End of 5.1 tests
+--echo # End of 5.1 tests
 
+--echo #
+--echo # MDEV-14911: IS NULL for field from mergeable view
+--echo #
+
+CREATE TABLE t1 (d1 datetime NOT NULL);
+INSERT INTO t1 VALUES
+  ('0000-00-00 00:00:00'), ('0000-00-00 00:00:00'), ('1979-09-03 20:49:36');
+
+SELECT * FROM t1;
+SELECT * FROM t1 WHERE d1 IS NULL;
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE d1 IS NULL;
+SELECT count(*) FROM t1 WHERE d1 IS NULL;
+
+CREATE VIEW v1 AS (SELECT * FROM t1);
+SELECT * FROM v1;
+SELECT * FROM v1 WHERE d1 IS NULL;
+EXPLAIN EXTENDED SELECT * FROM v1 WHERE d1 IS NULL;
+SELECT count(*) FROM v1 WHERE d1 IS NULL;
+
+SET @save_optimizer_switch=@@optimizer_switch;
+
+SET SESSION optimizer_switch='derived_merge=off';
+SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL;
+SET SESSION optimizer_switch='derived_merge=on';
+SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL;
+
+SET optimizer_switch=@save_optimizer_switch;
+
+CREATE TABLE t2 (d1 datetime NOT NULL);
+INSERT INTO t2 VALUES
+  ('1980-09-03 20:49:36'),  ('0000-00-00 00:00:00'), ('1979-09-03 20:49:36');
+
+SELECT * FROM t2 LEFT JOIN t1 ON t2.d1=t1.d1 WHERE t1.d1 IS NULL;
+EXPLAIN EXTENDED
+SELECT * FROM t2 LEFT JOIN t1 ON t2.d1=t1.d1 WHERE t1.d1 IS NULL;
+
+SELECT * FROM t2 LEFT JOIN v1 ON t2.d1=v1.d1 WHERE v1.d1 IS NULL;
+EXPLAIN EXTENDED
+SELECT * FROM t2 LEFT JOIN v1 ON t2.d1=v1.d1 WHERE v1.d1 IS NULL;
+
+DROP VIEW v1;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # End of 5.5 tests
+--echo #
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 5a7fa4d2c6f..8c2e90d8e57 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -13980,9 +13980,9 @@ bool cond_is_datetime_is_null(Item *cond)
       ((Item_func*) cond)->functype() == Item_func::ISNULL_FUNC)
   {
     Item **args= ((Item_func_isnull*) cond)->arguments();
-    if (args[0]->type() == Item::FIELD_ITEM)
+    if (args[0]->real_item()->type() == Item::FIELD_ITEM)
     {
-      Field *field=((Item_field*) args[0])->field;
+      Field *field=((Item_field*) (args[0]->real_item()))->field;
 
       if (((field->type() == MYSQL_TYPE_DATE) ||
            (field->type() == MYSQL_TYPE_DATETIME)) &&
@@ -14308,14 +14308,14 @@ internal_remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
 
     */
     Item **args= ((Item_func_isnull*) cond)->arguments();
-    Field *field=((Item_field*) args[0])->field;
+    Field *field=((Item_field*) (args[0]->real_item()))->field;
 
     Item *item0= new(thd->mem_root) Item_int((longlong)0, 1);
     Item *eq_cond= new(thd->mem_root) Item_func_eq(args[0], item0);
     if (!eq_cond)
       return cond;
 
-        if (field->table->pos_in_table_list->is_inner_table_of_outer_join())
+    if (field->table->pos_in_table_list->is_inner_table_of_outer_join())
     {
       // outer join: transform "col IS NULL" to "col IS NULL or col=0"
       Item *or_cond= new(thd->mem_root) Item_cond_or(eq_cond, cond);