EITS: different order of predicates in IN (...) causes different estimates

Description

Create the dataset:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 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 t1 (col1 int); insert into t1 select a from one_k; insert into t1 select 2000+a from one_k; insert into t1 select 2000+a from one_k; insert into t1 select 2000+a from one_k; insert into t1 select 2000+a from one_k; insert into t1 select 2000+a from one_k; insert into t1 select 2000+a from one_k; insert into t1 select 2000+a from one_k; insert into t1 select 2000+a from one_k; insert into t1 select 2000+a from one_k; insert into t1 select 2000+a from one_k; set use_stat_tables = 'preferably'; set optimizer_use_condition_selectivity=3; set optimizer_use_condition_selectivity=4; set histogram_size=100; analyze table t1 persistent for all;

Then run:

1 2 3 4 5 6 mysql> explain extended select * from t1 where col1 in (2990, 10); +------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 11000 | 0.03 | Using where | +------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+

And then run:

1 2 3 4 5 6 mysql> explain extended select * from t1 where col1 in (10,2990); +------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 11000 | 0.25 | Using where | +------+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+

Note that IN predicate is essentailly the same, but "filtered" changes.

Environment

None

Status

Assignee

Sergei Petrunia

Reporter

Sergei Petrunia

Labels

External issue ID

None

External issue ID

None

Fix versions

Affects versions

10.0.9

Priority

Major
Configure