|
This issue is a branch-off from MDEV-5985.
The dataset is:
## Dataset from MDEV-5985:
|
create table ten(a int);
|
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
create table one_k(a int);
|
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
|
create table one_k2 as select * from one_k;
|
set histogram_size=100;
|
set use_stat_tables='preferably';
|
set optimizer_use_condition_selectivity=4;
|
analyze table one_k persistent for all;
|
analyze table one_k2 persistent for all;
|
|
# Extra data for this MDEV:
|
create table one_k3 (a int, b int, key(a));
|
insert into one_k3 select A.a + 10*B.a, 12345 from ten A, ten B, ten C;
|
analyze table one_k3 persistent for all;
|
The query is:
explain extended select * from one_k2 straight_join one_k3 where one_k2.a=one_k3.a and one_k2.a<10;
|
+------+-------------+--------+------+---------------+------+---------+--------------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+--------+------+---------------+------+---------+--------------+------+----------+-------------+
|
| 1 | SIMPLE | one_k2 | ALL | NULL | NULL | NULL | NULL | 1000 | 0.99 | Using where |
|
| 1 | SIMPLE | one_k3 | ref | a | a | 5 | j22.one_k2.a | 10 | 9.90 | |
|
+------+-------------+--------+------+---------------+------+---------+--------------+------+----------+-------------+
|
Here, one_k2.filtered=0.99% is correct. However, we should have
one_k3.filtered=100%, because
- ref access makes sure that one_k2.a=one_k3.a,
- "one_k2.a<10" is checked for table one_k2
- hence, checking that "one_k3.a<10" will not produce any selectivity (in fact, the optimizer will not even generate this condition)
|