MySQL Bugs: #30622: ORDER BY on a SELECT causes results to be missed

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

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.

Table needed to reproduce.

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

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

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)

Martin, please test if this bug exists also in 5.0 and 5.1.

Not repeatable in 5.0 (5.0.48) or 5.1 (5.1.23)

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.

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.

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.

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.

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

Pushed into 6.0.5-alpha

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.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

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)

Noted in 5.6.1 changelog.