# 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.

None

Sergei Petrunia

Sergei Petrunia

None

None

10.0.9

Major
Configure