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

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
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(EOL)
Component/s: Optimizer
Labels:
None

Description

CREATE OR REPLACE TABLE t1 (id SERIAL, a DECIMAL(10,2) NOT NULL, KEY(a)) ENGINE=MyISAM;

INSERT INTO t1 (a) VALUES (11),(11),(11),(11),(11),(11);

explain SELECT * FROM t1 WHERE a<10.999; -- this rounds to 11.00

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

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

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

|    1 | SIMPLE      | t1    | ALL  | a             | NULL | NULL    | NULL |    6 | Using where |

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

Notice, it uses full table scan. Looks wrong. The expected behavior would be to use range search and exclude all values of 11. The constant 10.999 rounds to 11.00 in case of DECIMAL(10,2). So in the above condition, 10.999 and 11 should be equal.

Note, if I now change the constant from 10.999 to 11:

CREATE OR REPLACE TABLE t1 (id SERIAL, a DECIMAL(10,2) NOT NULL, KEY(a)) ENGINE=MyISAM;

INSERT INTO t1 (a) VALUES (11),(11),(11),(11),(11),(11);

explain SELECT * FROM t1 WHERE a<11;

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

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

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

|    1 | SIMPLE      | t1    | range | a             | a    | 5       | NULL |    1 | Using index condition |

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

it correctly starts to use range search.

Note, the reverse operation (greater-than) works fine. These two scripts:

CREATE OR REPLACE TABLE t1 (id SERIAL, a DECIMAL(10,2) NOT NULL, KEY(a)) ENGINE=MyISAM;

INSERT INTO t1 (a) VALUES (11),(11),(11),(11),(11),(11);

explain SELECT * FROM t1 WHERE a>11.001; -- this rounds to 11.00

CREATE OR REPLACE TABLE t1 (id SERIAL, a DECIMAL(10,2) NOT NULL, KEY(a)) ENGINE=MyISAM;

INSERT INTO t1 (a) VALUES (11),(11),(11),(11),(11),(11);

explain SELECT * FROM t1 WHERE a>11;

both use range search as expected:

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

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

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

|    1 | SIMPLE      | t1    | range | a             | a    | 5       | NULL |    1 | Using index condition |

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

The problem possibly happens because the code in opt_range.cc in case of the less-than operation does not call stored_field_cmp_to_item() and therefore does not notice when the constant rounds to a greater value.

On the contrary, for all other operations (less-or-equal, greater-than, greater-or-equal) the function stored_field_cmp_to_item() is called, so truncation/rounding are taken into account.

Attachments

Issue Links

relates to

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

Open

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

Closed

MDEV-15759 Expect "Impossible WHERE" for indexed_int_column=out_of_range_int_constant

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2018-07-20 04:36

Alexander Barkov made changes - 2018-07-20 04:36

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-15759~~ [ ~~MDEV-15759~~ ]

Alexander Barkov made changes - 2018-07-20 04:42

Link

This issue relates to MDEV-8787 [ MDEV-8787 ]

Alexander Barkov made changes - 2018-07-20 04:42

Link

This issue relates to ~~MDEV-15758~~ [ ~~MDEV-15758~~ ]

Alexander Barkov made changes - 2019-03-26 04:33

Description

{code:sql}
CREATE OR REPLACE TABLE t1 (id SERIAL, a DECIMAL(10,2) NOT NULL, KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 (a) VALUES (11),(11),(11),(11),(11),(11);
explain SELECT * FROM t1 WHERE a<10.999; -- this rounds to 11.00
{code}
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 6 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}

Notice, it uses full table scan. Looks wrong. The expected behavior would be to use range search and exclude all values of {{11}}. The constant {{10.999}} rounds to {{11.00}} in case of {{DECIMAL(10,2)}}. So in the above condition, {{10.999}} and {{11}} should be equal.

Note, if I now change the constant from {{10.999}} to {{11}}:

{code:sql}
CREATE OR REPLACE TABLE t1 (id SERIAL, a DECIMAL(10,2) NOT NULL, KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 (a) VALUES (11),(11),(11),(11),(11),(11);
explain SELECT * FROM t1 WHERE a<10.999;
{code}
{noformat}
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 1 | Using index condition |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
{noformat}
it correctly starts to use range search.

Note, the reverse operation (greater-than) works fine. These two scripts:

{code:sql}
CREATE OR REPLACE TABLE t1 (id SERIAL, a DECIMAL(10,2) NOT NULL, KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 (a) VALUES (11),(11),(11),(11),(11),(11);
explain SELECT * FROM t1 WHERE a>11.001; -- this rounds to 11.00
{code}

{code:sql}
CREATE OR REPLACE TABLE t1 (id SERIAL, a DECIMAL(10,2) NOT NULL, KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 (a) VALUES (11),(11),(11),(11),(11),(11);
explain SELECT * FROM t1 WHERE a>11;
{code}
both use range search as expected:
{noformat}
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 1 | Using index condition |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
{noformat}

The problem possibly happens because the code in opt_range.cc in case of the {{less-than}} operation does not call stored_field_cmp_to_item() and therefore does not notice when the constant rounds to a *greater* value.

On the contrary, for all other operations ({{less-or-equal}}, {{greater-than}}, {{greater-or-equal}}) the function stored_field_cmp_to_item() is called, so truncation/rounding are taken into account.

{code:sql}
CREATE OR REPLACE TABLE t1 (id SERIAL, a DECIMAL(10,2) NOT NULL, KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 (a) VALUES (11),(11),(11),(11),(11),(11);
explain SELECT * FROM t1 WHERE a<10.999; -- this rounds to 11.00
{code}
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 6 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}

Notice, it uses full table scan. Looks wrong. The expected behavior would be to use range search and exclude all values of {{11}}. The constant {{10.999}} rounds to {{11.00}} in case of {{DECIMAL(10,2)}}. So in the above condition, {{10.999}} and {{11}} should be equal.

Note, if I now change the constant from {{10.999}} to {{11}}:

{code:sql}
CREATE OR REPLACE TABLE t1 (id SERIAL, a DECIMAL(10,2) NOT NULL, KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 (a) VALUES (11),(11),(11),(11),(11),(11);
explain SELECT * FROM t1 WHERE a<11;
{code}
{noformat}
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 1 | Using index condition |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
{noformat}
it correctly starts to use range search.

Note, the reverse operation (greater-than) works fine. These two scripts:

{code:sql}
CREATE OR REPLACE TABLE t1 (id SERIAL, a DECIMAL(10,2) NOT NULL, KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 (a) VALUES (11),(11),(11),(11),(11),(11);
explain SELECT * FROM t1 WHERE a>11.001; -- this rounds to 11.00
{code}

{code:sql}
CREATE OR REPLACE TABLE t1 (id SERIAL, a DECIMAL(10,2) NOT NULL, KEY(a)) ENGINE=MyISAM;
INSERT INTO t1 (a) VALUES (11),(11),(11),(11),(11),(11);
explain SELECT * FROM t1 WHERE a>11;
{code}
both use range search as expected:
{noformat}
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 1 | Using index condition |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
{noformat}

The problem possibly happens because the code in opt_range.cc in case of the {{less-than}} operation does not call stored_field_cmp_to_item() and therefore does not notice when the constant rounds to a *greater* value.

On the contrary, for all other operations ({{less-or-equal}}, {{greater-than}}, {{greater-or-equal}}) the function stored_field_cmp_to_item() is called, so truncation/rounding are taken into account.

Sergei Golubchik made changes - 2021-12-06 21:33

Workflow

MariaDB v3 [ 88487 ]

MariaDB v4 [ 140863 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2018-07-20 04:36

Updated:: 2019-03-26 04:33

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration