Wrong result (missing rows) with TEXT field, optimizer_use_condition_selectivity >= 3

Description

1 2 3 4 5 6 7 8 9 10 11 set use_stat_tables='preferably'; set optimizer_use_condition_selectivity=3; create table t1(col1 text); insert into t1 values ('a'),('b'),('c'),('d'); analyze table t1; select * from t1 where col1 > 'b' and col1 < 'd'; drop table t1;

Actual result

1 2 MariaDB [test]> select * from t1 where col1 > 'b' and col1 < 'd'; Empty set (0.90 sec)

Plan

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 MariaDB [test]> explain extended select * from t1 where col1 > 'b' and col1 < 'd'; +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+ 1 row in set, 1 warning (1.29 sec) MariaDB [test]> show warnings; +-------+------+--------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where 0 | +-------+------+--------------------------------------------------------------+ 1 row in set (0.00 sec)
1 2 3 4 5 6 7 MariaDB [test]> select * from t1 where col1 > 'b' and col1 < 'd'; +------+ | col1 | +------+ | c | +------+ 1 row in set (4.71 sec)

Environment

None

Status

Assignee

Sergei Petrunia

Reporter

Elena Stepanova

Labels

None

External issue ID

None

External issue ID

None

Components

Fix versions

Affects versions

10.0
10.1

Priority

Major