| Bug #42405 | Missing/Wrong rows on Falcon's ORDER BY ..LIMIT with range access on pk/index | ||
|---|---|---|---|
| Submitted: | 28 Jan 2009 9:25 | Modified: | 6 Apr 2009 11:55 |
| Reporter: | Nidhi Shrotriya | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Falcon storage engine | Severity: | S3 (Non-critical) |
| Version: | 6.0 | OS: | Linux |
| Assigned to: | Lars-Erik Bjørk | CPU Architecture: | Any |
| Tags: | F_LIMIT | ||
[28 Jan 2009 9:33]
Nidhi Shrotriya
Same happens in case of SELECT * FROM t2 WHERE c1 IN (101,115) ORDER BY c1,c6; c1 c2 c3 c4 c5 c6 c7 101 0 37 38 39 40 41 101 NULL 102 103 104 105 106 101 102 103 104 105 106 107 115 116 117 118 119 120 121 SELECT * FROM t2 WHERE c1 IN (101,115) ORDER BY c1,c6 LIMIT 2; c1 c2 c3 c4 c5 c6 c7 115 116 117 118 119 120 121
[28 Jan 2009 10:08]
Hakan Küçükyılmaz
Verified using latest mysql-6.0-falcon-team tree code: [11:07] root@test> SELECT * FROM t2 WHERE c1 <> 108 ORDER BY c1,c6 LIMIT 2; +-----+------+------+------+------+-----+------+ | c1 | c2 | c3 | c4 | c5 | c6 | c7 | +-----+------+------+------+------+-----+------+ | 115 | 116 | 117 | 118 | 119 | 120 | 121 | | 122 | 123 | 124 | 125 | 126 | 127 | 128 | +-----+------+------+------+------+-----+------+ 2 rows in set (0.00
[28 Jan 2009 11:05]
Nidhi Shrotriya
Same happens with this access on indexed column.
[28 Jan 2009 17:41]
Kevin Lewis
Ann, Can you determine if there is anything Falcon can do to collect successfully do a LIMIT search when there are multiple ranges? This might be a job for the optimizer. Note that this bug is like the problem found in Bug#41742, which was using the IN operator to create multiple ranges. That would fail with this test as well. mysql> SELECT * FROM t2 WHERE c1 IN (101, 115, 122) LIMIT 5; +-----+------+------+------+------+-----+------+ | c1 | c2 | c3 | c4 | c5 | c6 | c7 | +-----+------+------+------+------+-----+------+ | 101 | 102 | 103 | 104 | 105 | 106 | 107 | | 115 | 116 | 117 | 118 | 119 | 120 | 121 | | 122 | 123 | 124 | 125 | 126 | 127 | 128 | +-----+------+------+------+------+-----+------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM t2 WHERE c1 IN (101, 115, 122) ORDER BY c1,c6 LIMIT 5; +-----+------+------+------+------+-----+------+ | c1 | c2 | c3 | c4 | c5 | c6 | c7 | +-----+------+------+------+------+-----+------+ | 122 | 123 | 124 | 125 | 126 | 127 | 128 | +-----+------+------+------+------+-----+------+ 1 row in set (0.00 sec) mysql> SELECT * FROM t2 WHERE c1 IN (101, 115, 122) ORDER BY c6 LIMIT 5; +-----+------+------+------+------+-----+------+ | c1 | c2 | c3 | c4 | c5 | c6 | c7 | +-----+------+------+------+------+-----+------+ | 101 | 102 | 103 | 104 | 105 | 106 | 107 | | 115 | 116 | 117 | 118 | 119 | 120 | 121 | | 122 | 123 | 124 | 125 | 126 | 127 | 128 | +-----+------+------+------+------+-----+------+ 3 rows in set (0.02 sec)
[5 Apr 2009 22:46]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/71414 3097 Vladislav Vaintroub 2009-04-06 Bug#42208 Falcon's ORDER BY ..LIMIT gives wrong/inconsistent results on NULL values Bug#42405 Missing/Wrong rows on Falcon's ORDER BY ..LIMIT with range access on pk/index This patch introduces new Falcon index version. It changes handling of multisegment keys, so binary keys that differ in number of trailing zeros bytes are now considered different. Also, NULL in index is now different from 0, empty string (0x00) and zero length string and sorts according to SQL standard rules, less than any other value. (incorporated changes from Lars-Erik for Bug#42208) INDEX_CURRENT_VERSION is now 2. Minor ODS version is increased to 5. @ mysql-test/suite/falcon/r/falcon_bug_42208.result test for bug 42208 @ mysql-test/suite/falcon/r/falcon_index_v2.result test for correct sorting order of multisegment keys, NULLs and zero length keys @ mysql-test/suite/falcon/t/falcon_bug_42208.test test for bug 42208 @ mysql-test/suite/falcon/t/falcon_index_v2.test test for correct sorting order of multisegment keys, NULLs and zero length keys @ storage/falcon/Database.h Increase ODS version (new index format) @ storage/falcon/Index.cpp Increase ODS version (new index format) @ storage/falcon/Index.h Increase index version @ storage/falcon/StorageDatabase.cpp Use all keys provided by optimizer, do not break at NULLs (but not for index version 1, where NULL is broken)
[6 Apr 2009 11:50]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/71442 3098 Vladislav Vaintroub 2009-04-06 Bug#42405 Missing/Wrong rows on Falcon's ORDER BY ..LIMIT with range access on pk/index The bug is no more reproducible. It is not known what exact change fixed the test.
[6 Apr 2009 11:55]
Vladislav Vaintroub
The bug is no more reproducible. Pushed the test for it.
[15 Apr 2009 16:59]
Bugs System
Pushed into 6.0.11-alpha (revid:hky@sun.com-20090415164923-9arx29ye5pzxd4zf) (version source revid:vvaintroub@mysql.com-20090406114905-nmvmlz0jr6ky1bss) (merge vers: 6.0.11-alpha) (pib:6)
Description: Missing/Wrong rows on Falcon's ORDER BY ..LIMIT with range access on primary key as below- How to repeat: With Falcon's latest code 'mysql-6.0-falcon-team' tree nidhi@nidhi-laptop:~/mysql-builds/mysql-6.0-falcon-team$ bin/mysql --user=root mysql> install plugin falcon soname 'ha_falcon.so'; Query OK, 0 rows affected (0.16 sec) mysql> show plugins; +------------+--------+----------------+--------------+---------+ | Name | Status | Type | Library | License | +------------+--------+----------------+--------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | MARIA | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | Falcon | ACTIVE | STORAGE ENGINE | ha_falcon.so | GPL | +------------+--------+----------------+--------------+---------+ mysql> CREATE TABLE t2(c1 TINYINT UNSIGNED NOT NULL, c2 TINYINT NULL, c3 SMALLINT, c4 MEDIUMINT , c5 INT, c6 INTEGER, c7 BIGINT, PRIMARY KEY(c1,c6)) engine=falcon; Query OK, 0 rows affected (0.01 sec) mysql> show create table t2; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` ( `c1` tinyint(3) unsigned NOT NULL, `c2` tinyint(4) DEFAULT NULL, `c3` smallint(6) DEFAULT NULL, `c4` mediumint(9) DEFAULT NULL, `c5` int(11) DEFAULT NULL, `c6` int(11) NOT NULL DEFAULT '0', `c7` bigint(20) DEFAULT NULL, PRIMARY KEY (`c1`,`c6`) ) ENGINE=Falcon DEFAULT CHARSET=latin1 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO t2 VALUES(101,102,103,104,105,106,107),(108,109,110,111,112,113,114),(115,116,117,118,119,120,121),(122,123,124,125,126,127,128); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from t2; +-----+------+------+------+------+-----+------+ | c1 | c2 | c3 | c4 | c5 | c6 | c7 | +-----+------+------+------+------+-----+------+ | 101 | 102 | 103 | 104 | 105 | 106 | 107 | | 108 | 109 | 110 | 111 | 112 | 113 | 114 | | 115 | 116 | 117 | 118 | 119 | 120 | 121 | | 122 | 123 | 124 | 125 | 126 | 127 | 128 | +-----+------+------+------+------+-----+------+ 4 rows in set (0.01 sec) mysql> SELECT * FROM t2 WHERE c1 <> 108 ORDER BY c1,c6 LIMIT 2 -> ; +-----+------+------+------+------+-----+------+ | c1 | c2 | c3 | c4 | c5 | c6 | c7 | +-----+------+------+------+------+-----+------+ | 115 | 116 | 117 | 118 | 119 | 120 | 121 | | 122 | 123 | 124 | 125 | 126 | 127 | 128 | +-----+------+------+------+------+-----+------+ 2 rows in set (0.00 sec) "Should have returned rows with 101, 115." mysql> SELECT * FROM t2 WHERE c1 <> 115 ORDER BY c1,c6; +-----+------+------+------+------+-----+------+ | c1 | c2 | c3 | c4 | c5 | c6 | c7 | +-----+------+------+------+------+-----+------+ | 101 | 102 | 103 | 104 | 105 | 106 | 107 | | 108 | 109 | 110 | 111 | 112 | 113 | 114 | | 122 | 123 | 124 | 125 | 126 | 127 | 128 | +-----+------+------+------+------+-----+------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM t2 WHERE c1 <> 115 ORDER BY c1,c6 LIMIT 2; +-----+------+------+------+------+-----+------+ | c1 | c2 | c3 | c4 | c5 | c6 | c7 | +-----+------+------+------+------+-----+------+ | 122 | 123 | 124 | 125 | 126 | 127 | 128 | +-----+------+------+------+------+-----+------+ 1 row in set (0.01 sec) "Should have returned 2 rows with 101, 108" Looks like only returning rows that have greater value.