Fixed bug mdev-11364.

The function Item_func_isnull::update_used_tables() must
handle the case when the predicate is over not nullable
column in a special way.
This is actually a bug of MariaDB 5.3/5.5, but it's probably
hard to demonstrate that it can cause problems there.

5 files changed, 82 insertions, 14 deletions

diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index 8fb5cd17c51..61a77d135e7 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -1517,3 +1517,24 @@ Note	1003	select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where 0
 drop table t1,t2;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 set use_stat_tables=@save_use_stat_tables;
+#
+# Bug mdev-11364: IS NULL over not nullable datetime column
+#                 in mergeable derived
+#
+set use_stat_tables='preferably';
+set optimizer_use_condition_selectivity=4;
+set HISTOGRAM_SIZE = 255;
+CREATE TABLE t1 (t TIME, d DATE NOT NULL);
+INSERT INTO t1 VALUES ('10:00:00', '0000-00-00'),('11:00:00','0000-00-00');
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+SELECT * FROM (SELECT t FROM t1 WHERE d IS NULL) sq;
+t
+10:00:00
+11:00:00
+DROP TABLE t1;
+set histogram_size=@save_histogram_size;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 3d15131dbb5..a026c2e6d92 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -1521,6 +1521,27 @@ Note	1003	select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where 0
 drop table t1,t2;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 set use_stat_tables=@save_use_stat_tables;
+#
+# Bug mdev-11364: IS NULL over not nullable datetime column
+#                 in mergeable derived
+#
+set use_stat_tables='preferably';
+set optimizer_use_condition_selectivity=4;
+set HISTOGRAM_SIZE = 255;
+CREATE TABLE t1 (t TIME, d DATE NOT NULL);
+INSERT INTO t1 VALUES ('10:00:00', '0000-00-00'),('11:00:00','0000-00-00');
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+SELECT * FROM (SELECT t FROM t1 WHERE d IS NULL) sq;
+t
+10:00:00
+11:00:00
+DROP TABLE t1;
+set histogram_size=@save_histogram_size;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set use_stat_tables=@save_use_stat_tables;
 set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
 set @tmp_ust= @@use_stat_tables;
 set @tmp_oucs= @@optimizer_use_condition_selectivity;
diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test
index 8efc5216ba0..548ef295fb2 100644
--- a/mysql-test/t/selectivity.test
+++ b/mysql-test/t/selectivity.test
@@ -1025,3 +1025,24 @@ drop table t1,t2;
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 set use_stat_tables=@save_use_stat_tables;
 
+--echo #
+--echo # Bug mdev-11364: IS NULL over not nullable datetime column
+--echo #                 in mergeable derived
+--echo #
+
+set use_stat_tables='preferably';
+set optimizer_use_condition_selectivity=4;
+set HISTOGRAM_SIZE = 255;
+
+CREATE TABLE t1 (t TIME, d DATE NOT NULL);
+INSERT INTO t1 VALUES ('10:00:00', '0000-00-00'),('11:00:00','0000-00-00');
+ 
+ANALYZE TABLE t1;
+
+SELECT * FROM (SELECT t FROM t1 WHERE d IS NULL) sq;
+
+DROP TABLE t1;
+
+set histogram_size=@save_histogram_size;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set use_stat_tables=@save_use_stat_tables;
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index dbd96a89a24..9e83b732dc7 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1387,10 +1387,26 @@ public:
     update_used_tables();
   }
   const char *func_name() const { return "isnull"; }
+
+  bool arg_is_datetime_notnull_field()
+  {
+    Item **args= arguments();
+    if (args[0]->type() == Item::FIELD_ITEM)
+    {
+      Field *field=((Item_field*) args[0])->field;
+
+      if (((field->type() == MYSQL_TYPE_DATE) ||
+          (field->type() == MYSQL_TYPE_DATETIME)) &&
+          (field->flags & NOT_NULL_FLAG))
+        return true;
+    }
+    return false;
+  }
+
   /* Optimize case of not_null_column IS NULL */
   virtual void update_used_tables()
   {
-    if (!args[0]->maybe_null)
+    if (!args[0]->maybe_null && !arg_is_datetime_notnull_field())
     {
       used_tables_cache= 0;			/* is always false */
       const_item_cache= 1;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 0cfb964307d..2db9a2b8482 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -14766,20 +14766,9 @@ bool cond_is_datetime_is_null(Item *cond)
   if (cond->type() == Item::FUNC_ITEM &&
       ((Item_func*) cond)->functype() == Item_func::ISNULL_FUNC)
   {
-    Item **args= ((Item_func_isnull*) cond)->arguments();
-    if (args[0]->type() == Item::FIELD_ITEM)
-    {
-      Field *field=((Item_field*) args[0])->field;
-
-      if (((field->type() == MYSQL_TYPE_DATE) ||
-           (field->type() == MYSQL_TYPE_DATETIME)) &&
-          (field->flags & NOT_NULL_FLAG))
-      {
-        return TRUE;
-      }
-    }
+    return ((Item_func_isnull*) cond)->arg_is_datetime_notnull_field();
   }
-  return FALSE;
+  return false;
 }