| Bug #33697 | "order by col DESC" in a query may give a result set in a wrong order | ||
|---|---|---|---|
| Submitted: | 4 Jan 2008 21:27 | Modified: | 1 Feb 2008 18:23 |
| Reporter: | John Water | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | MySQL 6.0.3 Alpha | OS: | Any (Win-XP) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
| Tags: | DESC, order by, query | ||
[5 Jan 2008 15:07]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described. It may be something similar to bug #31001, but in 6.0.x.
[10 Jan 2008 0:37]
Igor Babaev
The same problem can be observed for 5.1 (but not for 5.0):
mysql> select version() //
+-----------------+
| version() |
+-----------------+
| 5.1.23-rc-debug |
+-----------------+
1 row in set (0.01 sec)
mysql> select * from test3 where tid = 1 and vid = 3 order by idx desc
-> //
+-----+-----+-----+---------+------+
| vid | tid | idx | name | type |
+-----+-----+-----+---------+------+
| 3 | 1 | 1 | pk | NULL |
| 3 | 1 | 4 | c_extra | NULL |
| 3 | 1 | 3 | c2 | NULL |
| 3 | 1 | 2 | c1 | NULL |
+-----+-----+-----+---------+------+
4 rows in set (0.00 sec)
[10 Jan 2008 0:39]
Igor Babaev
The problem can be observed on any platform.
[11 Jan 2008 8:00]
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/40894 ChangeSet@1.2656, 2008-01-11 00:03:39-08:00, igor@olga.mysql.com +4 -0 Fixed bug #33697. When the function test_if_skip_sort_order prefers index backward scan to ref access the corresponding access functions must be set accordingly.
[11 Jan 2008 18:02]
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/40928 ChangeSet@1.2656, 2008-01-11 10:05:34-08:00, igor@olga.mysql.com +4 -0 Fixed bug #33697. When the function test_if_skip_sort_order prefers index backward scan to ref access the corresponding access functions must be set accordingly.
[24 Jan 2008 11:56]
Bugs System
Pushed into 6.0.5-alpha
[24 Jan 2008 12:01]
Bugs System
Pushed into 5.1.24-rc
[1 Feb 2008 18:23]
Paul DuBois
Noted in 5.1.24, 6.0.5 changelogs. ORDER BY ... DESC sorts could produce misordered results.
[4 Mar 2008 12:30]
Igor Babaev
Bug#34708 is marked as a duplicate of this bug.
[6 Mar 2008 15:49]
Jon Stephens
Also documented for 5.1.23-ndb-6.2.14.
[2 Apr 2008 16:51]
Jon Stephens
Also documented fix in the 5.1.23-ndb-6.3.11 changelog.
Description: If the "order by col DESC" is used in the query, the MySQL server may give a result set in a wrong order. In my repro, a query, select * from test3 where tid = 1 and vid = 3 order by idx desc gives the following output ------------------------------------- vid tid idx name type 3 1 1 pk NULL 3 1 4 c_extra NULL 3 1 3 c2 NULL 3 1 2 c1 NULL ------------------------------------- How to repeat: Please cut and paste the following commands into a file and then apply the contents of this file to a 6.0.3 MySQL database with mysql. Then you'll see the result set is in a wrong order. ------------------------------------------------------------ delimiter // CREATE TABLE test ( pk integer primary key not null, c1 integer, c2 integer ) engine=InnoDB // create table test1 ( tid integer not null, name varchar( 128 ) not null unique, primary key( tid ) ) engine = InnoDB // create table test2 ( vid integer not null, name varchar( 128 ) not null unique, description mediumtext null, primary key( vid ) ) engine = InnoDB // create table test3 ( vid integer not null, tid integer not null, idx integer not null, name varchar( 128 ) not null, type varchar( 128 ) null, primary key( idx, vid, tid ), unique( vid, tid, name ), foreign key( vid ) references test2 ( vid ), foreign key( tid ) references test1 ( tid ) ) engine = InnoDB // create procedure my_add_test3 ( p_version varchar( 128 ), p_table varchar( 128 ), p_column varchar( 128 ), p_type varchar( 128 ) ) begin declare v_vid integer; declare v_tid integer; declare v_idx integer; select vid into v_vid from test2 where name = p_version; select tid into v_tid from test1 where name = p_table; if p_column is not null then if v_vid is null then -- Insert to the test2 table select coalesce( max( vid )+1, 1 ) into v_vid from test2; insert into test2 ( vid, name ) values ( v_vid, p_version ); end if; if v_tid is null then -- Insert to the test1 table select coalesce( max( tid )+1, 1 ) into v_tid from test1; insert into test1 ( tid, name ) values ( v_tid, p_table ); end if; select coalesce( max( idx )+1, 1 ) into v_idx from test3 where vid = v_vid and tid = v_tid; insert into test3 ( vid, tid, idx, name, type ) values ( v_vid, v_tid, v_idx, p_column, p_type ); end if; end; // commit // call my_add_test3( 'test_V2','test','pk',NULL ) // call my_add_test3( 'test_V2','test','c1',NULL ) // call my_add_test3( 'test_V3','test','pk',NULL ) // call my_add_test3( 'test_V3','test','c1',NULL ) // call my_add_test3( 'test_V3','test','c2',NULL ) // call my_add_test3( 'test_V3','test','c_extra',NULL ) // call my_add_test3( 'test_V4','test','pk',NULL ) // call my_add_test3( 'test_V4','test','c1',NULL ) // call my_add_test3( 'test_V4','test','c2',NULL ) // call my_add_test3( 'test_V4','test','c_extra',NULL ) // call my_add_test3( 'test_V5','test','c1',NULL ) // call my_add_test3( 'test_V5','test','c2',NULL ) // call my_add_test3( 'test_V5','test','pk',NULL ) // call my_add_test3( 'test_V6','test','pk',NULL ) // call my_add_test3( 'test_V6','test','c1',NULL ) // call my_add_test3( 'test_V6','test','c2',NULL ) // commit // select * from test3 where tid = 1 and vid = 3 order by idx desc // ------------------------------------------------------------