summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorIgor Babaev <igor@askmonty.org>2016-11-29 11:28:15 -0800
committerIgor Babaev <igor@askmonty.org>2016-11-29 11:29:07 -0800
commit748d993cca6c63d40236ac7c937630965283242d (patch)
treeeda809397f2567629970423af6fca97fbc377396
parentb209bc3eedfabc205af89f9e4c3af86bdfc6277a (diff)
downloadmariadb-git-748d993cca6c63d40236ac7c937630965283242d.tar.gz
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.
-rw-r--r--mysql-test/r/selectivity.result21
-rw-r--r--mysql-test/r/selectivity_innodb.result21
-rw-r--r--mysql-test/t/selectivity.test21
-rw-r--r--sql/item_cmpfunc.h18
-rw-r--r--sql/sql_select.cc15
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;
}