MDEV-9682 Assertion `0' failed in Item_cache_row::illegal_method_call on 2nd execution of PS with NULLIF

don't cache Item_cache

4 files changed, 26 insertions, 8 deletions

diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result
index b8acc3e7319..f321ac978f5 100644
--- a/mysql-test/r/null.result
+++ b/mysql-test/r/null.result
@@ -1558,6 +1558,14 @@ insert into t1 values ('2015-12-31');
 select power( timestamp( nullif( '2002-09-08', f1 ) ), 24 ) from t1;
 ERROR 22003: DOUBLE value is out of range in 'pow(cast((case when '2002-09-08' = '2015-12-31' then NULL else '2002-09-08' end) as datetime(6)),24)'
 drop table t1;
+CREATE TABLE t1 (f1 INT);
+INSERT INTO t1 VALUES (1),(2);
+PREPARE stmt FROM "SELECT * FROM t1 WHERE NULLIF( ( 1, 2 ) IN ( SELECT 3, 4 ), 1 )";
+EXECUTE stmt;
+f1
+EXECUTE stmt;
+f1
+DROP TABLE t1;
 #
 # End of 10.1 tests
 #
diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result
index bdcffc8d824..b5689d31aef 100644
--- a/mysql-test/r/type_time.result
+++ b/mysql-test/r/type_time.result
@@ -989,7 +989,7 @@ SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-0
 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	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = <cache>(00:00:00))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 00:00:00)
 # TIME cast + DATE literal
 SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE'2015-09-11';
 a
@@ -999,7 +999,7 @@ SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE'2015-09
 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	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = <cache>(00:00:00))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 00:00:00)
 # TIME literal + DATE cast
 SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE('2015-09-11');
 a
@@ -1029,7 +1029,7 @@ SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE('2015-09-11')
 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	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = '00:00:00') and (coalesce(`test`.`t1`.`a`) = <cache>(2015-09-11 00:00:00)))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = '00:00:00') and (coalesce(`test`.`t1`.`a`) = 2015-09-11 00:00:00))
 # TIME-alike string literal + DATE literal
 SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE'2015-09-11';
 a
@@ -1049,7 +1049,7 @@ SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE('2015-09-11');
 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	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = 0) and (coalesce(`test`.`t1`.`a`) = <cache>(2015-09-11 00:00:00)))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = 0) and (coalesce(`test`.`t1`.`a`) = 2015-09-11 00:00:00))
 # TIME-alike integer literal + DATE literal
 SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE'2015-09-11';
 a
@@ -1069,7 +1069,7 @@ SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME('00:0
 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	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = <cache>(2015-09-11 00:00:00))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 2015-09-11 00:00:00)
 # DATE cast + TIME literal
 SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME'00:00:00';
 a
@@ -1079,7 +1079,7 @@ SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME'00:00
 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	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = <cache>(2015-09-11 00:00:00))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (coalesce(`test`.`t1`.`a`) = 2015-09-11 00:00:00)
 # DATE cast + TIME-alike string literal
 SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)='00:00:00';
 a
@@ -1089,7 +1089,7 @@ SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)='00:00:00'
 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	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = <cache>(2015-09-11 00:00:00)) and (coalesce(`test`.`t1`.`a`) = '00:00:00'))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = 2015-09-11 00:00:00) and (coalesce(`test`.`t1`.`a`) = '00:00:00'))
 # DATE cast + TIME-alike integer literal
 SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=0;
 a
@@ -1099,7 +1099,7 @@ SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=0;
 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	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = <cache>(2015-09-11 00:00:00)) and (coalesce(`test`.`t1`.`a`) = 0))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((coalesce(`test`.`t1`.`a`) = 2015-09-11 00:00:00) and (coalesce(`test`.`t1`.`a`) = 0))
 # DATE literal + TIME cast
 SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME('00:00:00');
 a
diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test
index 85c7131c33b..cdc9ceb27e0 100644
--- a/mysql-test/t/null.test
+++ b/mysql-test/t/null.test
@@ -988,6 +988,15 @@ insert into t1 values ('2015-12-31');
 select power( timestamp( nullif( '2002-09-08', f1 ) ), 24 ) from t1;
 drop table t1;
 
+#
+# MDEV-9682 Assertion `0' failed in Item_cache_row::illegal_method_call on 2nd execution of PS with NULLIF
+#
+CREATE TABLE t1 (f1 INT);
+INSERT INTO t1 VALUES (1),(2);
+PREPARE stmt FROM "SELECT * FROM t1 WHERE NULLIF( ( 1, 2 ) IN ( SELECT 3, 4 ), 1 )";
+EXECUTE stmt;
+EXECUTE stmt;
+DROP TABLE t1;
 --echo #
 --echo # End of 10.1 tests
 --echo #
diff --git a/sql/item.cc b/sql/item.cc
index d6132ccfbcf..a330cee716a 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -6503,6 +6503,7 @@ bool Item::cache_const_expr_analyzer(uchar **arg)
         !(basic_const_item() || item->basic_const_item() ||
           item->type() == Item::FIELD_ITEM ||
           item->type() == SUBSELECT_ITEM ||
+          item->type() == CACHE_ITEM ||
            /*
              Do not cache GET_USER_VAR() function as its const_item() may
              return TRUE for the current thread but it still may change