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: | |
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
[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?