[MDEV-15759] Expect "Impossible WHERE" for indexed_int_column=out_of_range_int_constant - Jira

XML

Word

Printable

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.4.0
Component/s: Data types, Optimizer
Labels:
None

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

blocks

MDEV-18898 SELECT using wrong index when using operator IN with mixed types

relates to

MDEV-8787 Return Impossible WHERE instead of Full table scan on some admittedly false field=const expressions

MDEV-15758 Split Item_bool_func::get_mm_leaf() into virtual methods in Field and Type_handler

MDEV-16784 Range search does not work well for decimal_10_2_column<10.999

MDEV-28531 update on out of range criteria should be Impossible Where

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.