[#MDEV-15759] Expect "Impossible WHERE" for indexed_int_column=out_of_range_int

The range optimizer decides to use "ref" search when testing an equality between an indexed integer column and an out-of-range integer constant:

CREATE OR REPLACE TABLE t1 (a TINYINT, KEY(a));

INSERT INTO t1 VALUES (1),(2),(3),(4),(5);

EXPLAIN SELECT * FROM t1 WHERE a=200;

+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |

+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

|    1 | SIMPLE      | t1    | ref  | a             | a    | 2       | const |    1 | Using where; Using index |

+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

The same happens with the BIT data type:

CREATE OR REPLACE TABLE t1 (a BIT(7), KEY(a));

INSERT INTO t1 VALUES (1),(2),(3),(4),(5);

EXPLAIN SELECT * FROM t1 WHERE a=200;

+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |

+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

|    1 | SIMPLE      | t1    | ref  | a             | a    | 2       | const |    1 | Using where; Using index |

+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

This is wrong. It should return with "Impossible WHERE".

[MDEV-15759] Expect "Impossible WHERE" for indexed_int_column=out_of_range_int_constant Created: 2018-04-03 Updated: 2022-05-12 Resolved: 2018-07-19
Status:	Closed
Project:	MariaDB Server
Component/s:	Data types, Optimizer
Affects Version/s:	5.5, 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s:	10.4.0