| Bug #30622 | ORDER BY on a SELECT causes results to be missed | ||
|---|---|---|---|
| Submitted: | 24 Aug 2007 16:16 | Modified: | 10 Apr 2008 16:18 |
| Reporter: | Mark Robson | ||
| Status: | Closed | ||
| Category: | Server: General | Severity: | S1 (Critical) |
| Version: | 5.2.6-alpha | OS: | Linux |
| Assigned to: | Bugs System | Target Version: | 6.0 |
| Triage: | D2 (Serious) | ||
[24 Aug 2007 16:16]
Mark Robson
Table needed to reproduce.
Attachment: phraselistitems.sql (text/x-sql), 2.42 KiB.
[24 Aug 2007 16:44]
Mark Robson
explain select * FROM PhraseListItems WHERE ParentPhraseListID=1130 AND SupersededDate IS
NULL;
+----+-------------+-----------------+------+--------------------+--------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-----------------+------+--------------------+--------------------+---------+-------+------+-------------+
| 1 | SIMPLE | PhraseListItems | ref | ParentPhraseListID | ParentPhraseListID | 4
| const | 4 | Using where |
+----+-------------+-----------------+------+--------------------+--------------------+---------+-------+------+-------------+
explain select * FROM PhraseListItems WHERE ParentPhraseListID=1130 AND SupersededDate IS
NULL ORDER BY Phrase;
+----+-------------+-----------------+------+--------------------+--------------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-----------------+------+--------------------+--------------------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | PhraseListItems | ref | ParentPhraseListID | ParentPhraseListID | 4
| const | 4 | Using where; Using filesort |
+----+-------------+-----------------+------+--------------------+--------------------+---------+-------+------+-----------------------------+
The main difference being the "Using filesort" - filesort could be the problem
[24 Aug 2007 17:06]
Miguel Solorzano
Thank you for the bug report.
[miguel@skybr 5.2]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.2.6-alpha-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT * FROM PhraseListItems WHERE ParentPhraseListID=1130 AND SupersededDate IS
NULL;
+----+----------------+--------------------+-------------------+--------+-------+-------+----------------+------------------+
| ID | SupersededDate | ParentPhraseListID | ChildPhraseListID | Phrase | Regex | Score |
ApplyTestOn | AdvancedViewUsed |
+----+----------------+--------------------+-------------------+--------+-------+-------+----------------+------------------+
| 1 | NULL | 1130 | NULL | Hello | NULL | 100 |
bodyandsubject | 0 |
| 2 | NULL | 1130 | NULL | bye | NULL | 100 |
bodyandsubject | 0 |
| 3 | NULL | 1130 | NULL | red | NULL | 100 |
bodyandsubject | 0 |
| 4 | NULL | 1130 | NULL | yellow | NULL | 100 |
bodyandsubject | 0 |
| 5 | NULL | 1130 | NULL | blue | NULL | 100 |
bodyandsubject | 0 |
+----+----------------+--------------------+-------------------+--------+-------+-------+----------------+------------------+
5 rows in set (0.03 sec)
mysql> SELECT * FROM PhraseListItems WHERE ParentPhraseListID=1130 AND SupersededDate IS
NULL
-> ORDER BY Phrase;
Empty set (0.01 sec)
[29 Aug 2007 12:40]
Timour Katchaounov
Martin, please test if this bug exists also in 5.0 and 5.1.
[29 Aug 2007 15:34]
Martin Hansson
Not repeatable in 5.0 (5.0.48) or 5.1 (5.1.23)
[30 Aug 2007 9:57]
Martin Hansson
Miminal example: CREATE TABLE t1 ( a INT, b INT, c TEXT, KEY (a) ); INSERT INTO t1 VALUES (1, 1, 'one'), (1, 2, 'two'); SELECT * FROM t1 WHERE a = 1 ORDER BY b; Empty set (0.00 sec) The mere fact that there's a TEXT column seems to render the result empty. The same goes for all eight BLOB and TEXT types, regardless of order of columns.
[3 Sep 2007 18:45]
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/33598 ChangeSet@1.2576, 2007-09-03 18:46:29+02:00, mhansson@linux-st28.site +6 -0 Bug #30622: Unexpected behavior using DELETE with AS and USING Since WL 2474, the handler uses a disk sweep multi-range read implementation, which is encapsulated in the handler, and coexists with the old (default) MRR implementation. But when positioning the pointer to the current row, the position is always fetched from the default implementaion. This leads to an incorrect position of the pointer when doing MRR access using index lookup to rows that contain one of the eight BLOB types, and hence no result. Fixed by: - moving the default implementation of handler::position to handler::position_default_impl, not part of public interface. - Modifying the public interface handler::position to position pointer according to which MMR implementation is used.
[26 Sep 2007 22:22]
Sergey Petrunya
The provided example produces correct result for InnoDB only because the table has a primary key. In this case ha_innobase::position() extracts rowid value from table->record[0], which is shared between the "primary" and "secondary" instances of ha_innobase. For InnoDB table without primary key, the result will be incorrect too.
[28 Sep 2007 15:20]
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/34641 ChangeSet@1.2597, 2007-09-28 17:19:51+04:00, sergefp@mysql.com +9 -0 BUG#30622: ORDER BY on a SELECT causes results to be missed - Added DsMrr_impl::call_position_for which tells whether the position() call is made to the primary or to the secondary table handler. - Made MyISAM and InnoDB to use call_position_for. - Removed deadcode and redundant DsMrr_impl members.
[10 Jan 2008 23:06]
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/40880 ChangeSet@1.2774, 2008-01-11 01:04:59+03:00, sergefp@mysql.com +17 -0 BUG#30622: ORDER BY on a SELECT causes results to be missed - Restore the original table condition if we've done index condition pushdown for some index but later decided not to use it - Remove outdated comments
[13 Mar 2008 20:28]
Bugs System
Pushed into 6.0.5-alpha
[10 Apr 2008 16:18]
Paul DuBois
Noted in 6.0.5 changelog. If the optimizer used a multi-read range access method for index lookups, incorrect results could occur for rows that contained any of the BLOB or TEXT data types.
Description: SELECT * FROM PhraseListItems WHERE ParentPhraseListID=1130 AND SupersededDate IS NULL; Returns the correct results. SELECT * FROM PhraseListItems WHERE ParentPhraseListID=1130 AND SupersededDate IS NULL ORDER BY Phrase; Returns no rows, despite the same WHERE clause. How to repeat: Load the following table: CREATE TABLE `PhraseListItems` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `SupersededDate` int(10) unsigned DEFAULT NULL, `ParentPhraseListID` int(10) unsigned NOT NULL DEFAULT '0', `ChildPhraseListID` int(10) unsigned DEFAULT NULL, `Phrase` text, `Regex` text, `Score` smallint(6) DEFAULT '100', `ApplyTestOn` enum('headers','bodyandsubject') NOT NULL DEFAULT 'bodyandsubject', `AdvancedViewUsed` tinyint(3) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `ParentPhraseListID` (`ParentPhraseListID`), KEY `ChildPhraseListID` (`ChildPhraseListID`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; INSERT INTO `PhraseListItems` VALUES (1,NULL,1130,NULL,'Hello',NULL,100,'bodyandsubject',0),(2,NULL,1130,NULL,'bye',NULL,100,'bodyandsubject',0),(3,NULL,1130,NULL,'red',NULL,100, 'bodyandsubject',0),(4,NULL,1130,NULL,'yellow',NULL,100,'bodyandsubject',0),(5,NULL,1130,NULL,'blue',NULL,100,'bodyandsubject',0); --- Perform the above queries.