diff options
Diffstat (limited to 'mysql-test/r/func_in.result')
| -rw-r--r-- | mysql-test/r/func_in.result | 34 |
1 files changed, 30 insertions, 4 deletions
diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index c574244c2b4..c80ea5d13e1 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -271,7 +271,7 @@ select * from t2 where a NOT IN ( '2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00', '2006-04-25 10:06:00', '2006-04-25 10:08:00'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 9 NULL 18 Using index condition +1 SIMPLE t2 range a a 6 NULL 12 Using index condition select * from t2 where a NOT IN ( '2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00', '2006-04-25 10:06:00', '2006-04-25 10:08:00'); @@ -701,11 +701,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE c_datetime IN ('2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using index condition +1 SIMPLE t1 range c_datetime c_datetime 5 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL, '2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using index condition +1 SIMPLE t1 range c_datetime c_datetime 5 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -811,7 +811,9 @@ PREPARE s FROM "SELECT 1 FROM t1 WHERE 1 < ALL (SELECT @:= (1 IN (SELECT 1 FROM EXECUTE s; 1 DROP TABLE t1; +# # End of 5.3 tests +# create table t1 (a int); insert t1 values (1),(2),(3); select * from t1 where 1 in (a, name_const('a', null)); @@ -819,7 +821,7 @@ a 1 drop table t1; # -# Start of 10.0 tests +# End of 5.5 tests # # # MDEV-10020 InnoDB NOT IN Query Crash When One Item Is NULL @@ -837,3 +839,27 @@ DROP TABLE t1; # # End of 10.0 tests # +# +# MDEV-8755 Equal field propagation is not performed any longer for the IN list when multiple comparison types +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +# Ok to propagate equalities into the left IN argument in case of a single comparison type +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a IN (1,2,3); +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 (`test`.`t1`.`a` = 1) +# Ok to propagate equalities into IN () list, even if multiple comparison types +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND 1 IN (1,a,'3'); +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 (`test`.`t1`.`a` = 1) +# Not Ok to propagate equalities into the left IN argument in case of multiple comparison types +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a IN (1,2,'3'); +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 ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`a` in (1,2,'3'))) +DROP TABLE t1; |