Bug #64316 search with decimal value on INT field with index gives wrong result with LIMIT
Submitted: 14 Feb 2012 12:04 Modified: 14 Feb 2012 15:50
Reporter: Richard Mastny Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.1.49-3\5.5.21 OS:Linux (Debian)
Assigned to: CPU Architecture:Any

[14 Feb 2012 12:04] Richard Mastny
Description:
IF:
- you do a decimal search (e.g. weight>=0.5) on an INT field
and
- this field has an index
and
- apply "LIMIT 1" to the query

then the second instead of the first row is returned

How to repeat:
Simple 2 column table:
weight	tinyint(4)
price	smallint(5)

mysql> SELECT * FROM test3;
+--------+-------+
| weight | price |
+--------+-------+
|      1 |    10 |
|      2 |    20 |
|      3 |    30 |
|      4 |    40 |
+--------+-------+

Everything is fine, as long as there is no index set on column "weight":

mysql> SELECT * FROM test3 WHERE weight>=0.5 ORDER BY weight LIMIT 1;
+--------+-------+
| weight | price |
+--------+-------+
|      1 |    10 |
+--------+-------+

BUT, after adding an index
mysql> ALTER TABLE test3 ADD INDEX (weight);
Query OK, 4 rows affected (0.31 sec)

THIS happens:
mysql> SELECT * FROM test3 WHERE weight>=0.5 ORDER BY weight LIMIT 1;
+--------+-------+
| weight | price |
+--------+-------+
|      2 |    20 |
+--------+-------+

this is also OK: (do a search with an int value (1) instead decimal (0.5))
mysql> SELECT * FROM test3 WHERE weight>=1 ORDER BY weight LIMIT 1;
+--------+-------+
| weight | price |
+--------+-------+
|      1 |    10 |
+--------+-------+

POSSIBLE WORKAROUNDS:
a) use CEIL()
SELECT * FROM test3 WHERE weight>=CEIL(0.5) ORDER BY weight LIMIT 1;

b) change the "weight" field from INT to float

c) remove the index of field "weight"
[14 Feb 2012 14:16] MySQL Verification Team
Thank you for the bug report.

h:\dbs>h:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.5.21-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >use test
Database changed
mysql 5.5 >SELECT * FROM test3;
+--------+-------+
| weight | price |
+--------+-------+
|      1 |    10 |
|      2 |    20 |
|      3 |    30 |
|      4 |    40 |
+--------+-------+
4 rows in set (0.00 sec)

mysql 5.5 >SELECT * FROM test3 WHERE weight>=0.5 ORDER BY weight LIMIT 1;
+--------+-------+
| weight | price |
+--------+-------+
|      1 |    10 |
+--------+-------+
1 row in set (0.00 sec)

mysql 5.5 >ALTER TABLE test3 ADD INDEX (weight);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.5 >SELECT * FROM test3 WHERE weight>=0.5 ORDER BY weight LIMIT 1;
+--------+-------+
| weight | price |
+--------+-------+
|      2 |    20 |
+--------+-------+
1 row in set (0.00 sec)

mysql 5.5 >DROP INDEX weight ON test3;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.5 >SELECT * FROM test3 WHERE weight>=0.5 ORDER BY weight LIMIT 1;
+--------+-------+
| weight | price |
+--------+-------+
|      1 |    10 |
+--------+-------+
1 row in set (0.00 sec)

mysql 5.5 >
[14 Feb 2012 15:50] Richard Mastny
btw. the bug does not come up in 5.0.22 (5.0.22-Debian_0ubuntu6.06.11-log)
[17 Feb 2012 0:40] MySQL Verification Team
reminds me of things like http://bugs.mysql.com/bug.php?id=45680
[22 Mar 2012 6:56] xiaobin lin
It seems that is because in get_mm_leaf (opt_range.cc), when call stored_field_cmp_to_item(param->thd, field, value), the "field" value is set to a int value, (weight=1 in this case). 
It returns -1 because value is 0.5; this causes " tree->min_flag= NEAR_MIN;" (1)

Then in QUICK_RANGE_SELECT::get_next()->make_min_endpoint (opt_range.h), the kr->flag is set to HA_READ_AFTER_KEY, because it knows that the search value is less than the first value in the index. (2)

But because the type is int, when call "ha_innobase::index_read()", it pass "key=1, HA_READ_AFTER_KEY", so the logic is changed to ">1".

Simply modification can change "kr->flag= ((flag & NEAR_MIN) ? HA_READ_AFTER_KEY" to "kr->flag= ((flag & NEAR_MIN) ? HA_READ_KEY_OR_NEXT :"
Not test enough, just for confirm.
[22 Mar 2012 7:39] xiaobin lin
Perhaps a more elegent modification is the change 0.5 to 1, so the "0.5<1" change to "1<=1", so the flag is set to HA_READ_KEY_OR_NEXT when call innobase::index_read()
[14 Feb 2013 10:51] Hartmut Holzgraefe
can't reproduce this on 5.5.21 or newer ... i *can* still reproduce the related bug #45680 up to 5.6.10 though ...

looks as if a better self contained test case is needed?