From 70f5fabdbf50abb494304baaf863feac3fbd5dd9 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 4 Mar 2016 10:09:17 +0100 Subject: MDEV-9682 Assertion `0' failed in Item_cache_row::illegal_method_call on 2nd execution of PS with NULLIF don't cache Item_cache --- mysql-test/r/null.result | 8 ++++++++ mysql-test/r/type_time.result | 16 ++++++++-------- mysql-test/t/null.test | 9 +++++++++ sql/item.cc | 1 + 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`) = (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`) = (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`) = (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`) = (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`) = (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`) = (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`) = (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`) = (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 -- cgit v1.2.1