diff options
Diffstat (limited to 'mysql-test/r/range.result')
| -rw-r--r-- | mysql-test/r/range.result | 45 |
1 files changed, 45 insertions, 0 deletions
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index f2ad42ebc8d..fe528e1b2e9 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -2196,3 +2196,48 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range a a 5 NULL 1 Using where; Using index drop table t1,t2,t3; +# +# MDEV-6480: Remove conditions for which range optimizer returned SEL_ARG::IMPOSSIBLE. +# +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (a int, b int, c int, key(a), key(b)); +insert into t2 +select +A.a + B.a* 10 + C.a * 100, +A.a + B.a* 10 + C.a * 100, +12345 +from +t1 A, t1 B, t1 C; +# EXPLAIN EXTENDED should show that 'b > 25 and b < 15' is removed from the WHERE: +explain extended select * from t2 where (b > 25 and b < 15) or a<44; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range a,b a 5 NULL 43 100.00 Using index condition +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`a` < 44) +# EXPLAIN EXTENDED should show that 'b > 25 and b < 15' is removed from the WHERE: +explain extended select * from t2 where a < 44 or (b > 25 and b < 15); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range a,b a 5 NULL 43 100.00 Using index condition +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`a` < 44) +# Here, conditions b will not be removed, because "c<44" is not sargable +# and hence (b.. and .. b) part is not analyzed at all: +explain extended select * from t2 where c < 44 or (b > 25 and b < 15); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL b NULL NULL NULL 1000 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` < 44) or ((`test`.`t2`.`b` > 25) and (`test`.`t2`.`b` < 15))) +# EXPLAIN EXTENDED should show that 'b > 25 and b < 15' is removed from the WHERE: +explain extended select * from t2 where (b > 25 and b < 15) or c < 44; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL b NULL NULL NULL 1000 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`c` < 44)) +# Try a case where both OR parts produce SEL_ARG::IMPOSSIBLE: +explain extended select * from t2 where (b > 25 and b < 15) or (a>55 and a<44); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where 0 +drop table t1,t2; |