EITS: Histogram estimates for column=least_possible_value are wrong

Description

This is based on observations over and MDEV-4364.

Populate the table with 100 rows with a=0, 100 rows with a=1, etc. (the data distribution is perfectly uniform)

1 2 3 4 5 6 7 8 9 create table ten(a int); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(a int); insert into t1 select A.a from ten A, ten B, ten C; set histogram_size=20; set histogram_type='single_prec_hb'; analyze table t1 persistent for all; set use_stat_tables='preferably'; set optimizer_use_condition_selectivity=4;
1 2 3 4 5 6 mysql> explain extended select * from t1 where a=2; +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 8.67 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+

Not a precise estimate, but it is probably ok.

1 2 3 4 5 6 mysql> explain extended select * from t1 where a=1; +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 8.67 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+

OK.

1 2 3 4 5 6 mysql> explain extended select * from t1 where a=0; +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+

Boom. The number of rows with a=0 is 10% of the table. Not ok.

1 2 3 4 5 6 mysql> explain extended select * from t1 where a=-1; +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+

-1 is not present at all. Not ok.

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