Bug #30622 ORDER BY on a SELECT causes results to be missed
Submitted: 24 Aug 2007 14:16 Modified: 20 Nov 2010 19:47
Reporter: Mark Robson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S1 (Critical)
Version:5.2.6-alpha OS:Linux
Assigned to: Sergey Petrunya CPU Architecture:Any

[24 Aug 2007 14:16] Mark Robson
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.
[24 Aug 2007 14:16] Mark Robson
Table needed to reproduce.

Attachment: phraselistitems.sql (text/x-sql), 2.42 KiB.

[24 Aug 2007 14: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 15:06] MySQL Verification Team
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 10:40] Timour Katchaounov
Martin, please test if this bug exists also in 5.0 and 5.1.
[29 Aug 2007 13:34] Martin Hansson
Not repeatable in 5.0 (5.0.48) or 5.1 (5.1.23)
[30 Aug 2007 7: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 16: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 20: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 13: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 22: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 19:28] Bugs System
Pushed into 6.0.5-alpha
[10 Apr 2008 14: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.
[16 Aug 2010 6:41] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:15] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[20 Nov 2010 19:47] Paul DuBois
Noted in 5.6.1 changelog.