Bug #33697 "order by col DESC" in a query may give a result set in a wrong order

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
//

------------------------------------------------------------

Thank you for a bug report. Verified just as described. It may be something similar to bug #31001, but in 6.0.x.

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)

The problem can be observed on any platform.

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.

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.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Noted in 5.1.24, 6.0.5 changelogs.

ORDER BY ... DESC sorts could produce misordered results.

Bug#34708 is marked as a duplicate of this bug.

Also documented for 5.1.23-ndb-6.2.14.

Also documented fix in the 5.1.23-ndb-6.3.11 changelog.