Details
Description
--source include/have_innodb.inc
|
|
CREATE TABLE t (pk int, a int, PRIMARY KEY (pk), KEY(a)) ENGINE=InnoDB; |
|
INSERT INTO t VALUES (1,3),(2,6),(3,9); |
SELECT * FROM t WHERE a < 8 OR ( pk BETWEEN 1 AND 5 AND a BETWEEN 7 AND 10 ); |
|
# Cleanup
|
DROP TABLE t; |
bb-11.0 0ff27057415 |
SELECT * FROM t WHERE a < 8 OR ( pk BETWEEN 1 AND 5 AND a BETWEEN 7 AND 10 ); |
pk a
|
1 3
|
2 6
|
EXPLAIN EXTENDED SELECT * FROM t WHERE a < 8 OR ( pk BETWEEN 1 AND 5 AND a BETWEEN 7 AND 10 ); |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t range PRIMARY,a a 5 NULL 2 100.00 Using where; Using index |
Warnings:
|
Note 1003 select `test`.`t`.`pk` AS `pk`,`test`.`t`.`a` AS `a` from `test`.`t` where `test`.`t`.`a` < 8 or `test`.`t`.`pk` between 1 and 5 and `test`.`t`.`a` between 7 and 10 |
The baseline returns 3 rows, which is the expected result:
11.0 936436ef43 |
pk a
|
1 3
|
2 6
|
3 9
|
EXPLAIN EXTENDED SELECT * FROM t WHERE a < 8 OR ( pk BETWEEN 1 AND 5 AND a BETWEEN 7 AND 10 ); |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t range PRIMARY,a a 9 NULL 3 100.00 Using where; Using index |
Warnings:
|
Note 1003 select `test`.`t`.`pk` AS `pk`,`test`.`t`.`a` AS `a` from `test`.`t` where `test`.`t`.`a` < 8 or `test`.`t`.`pk` between 1 and 5 and `test`.`t`.`a` between 7 and 10 |
Attachments
Issue Links
- is caused by
-
MDEV-26974 Improve selectivity and related costs in optimizer
- Closed