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