diff options
Diffstat (limited to 'mysql-test/r/sp.result')
| -rw-r--r-- | mysql-test/r/sp.result | 38 |
1 files changed, 35 insertions, 3 deletions
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index b5e6e84a49a..defd3955f6c 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -6530,16 +6530,16 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref c1 c1 5 const 1 Using index EXPLAIN SELECT * FROM t1 WHERE c1=f1(); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c1 c1 5 const 0 Using where; Using index +1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index EXPLAIN SELECT * FROM v1 WHERE c1=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref c1 c1 5 const 1 Using index EXPLAIN SELECT * FROM v1 WHERE c1=f1(); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c1 c1 5 const 0 Using where; Using index +1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index EXPLAIN SELECT * FROM t1 WHERE c1=f2(10); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c1 c1 5 const 0 Using where; Using index +1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL c1 5 NULL 5 Using where; Using index @@ -7852,6 +7852,38 @@ c1 c2 count(c3) 2012-03-01 01:00:00 3 1 2012-03-01 02:00:00 3 1 DROP PROCEDURE p1; + +MDEV-3900 Optimizer difference between MySQL and MariaDB with stored functions in WHERE clause of UPDATE or DELETE statements + +CREATE FUNCTION tdn() RETURNS int(7) DETERMINISTIC RETURN to_days(now()); +CREATE TABLE t1 (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, daynum INT, a CHAR(1), INDEX(daynum), INDEX(a)) ENGINE=MyISAM; +INSERT INTO t1 (daynum) VALUES (1),(2),(3),(4),(5),(TO_DAYS(NOW())),(7),(8); +INSERT INTO t1 (daynum) SELECT a1.daynum FROM t1 a1, t1 a2, t1 a3, t1 a4, t1 a5; +FLUSH TABLES; +FLUSH STATUS; +SHOW STATUS LIKE '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +UPDATE t1 SET a = '+' WHERE daynum=tdn(); +SHOW STATUS LIKE '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 2 +Handler_read_last 0 +Handler_read_next 4097 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +drop function tdn; +drop table t1; # # lp:1002157 : testing stored function # bug#62125 result for null incorrectly yields 1292 warning. |