Bug #515 Bug with ORDER BY .. DESC MERGE and index optimization

• Submitted: 27 May 2003 3:37
• Modified: 4 Jul 2003 6:58
• Reporter: Alexander Keremidarski
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.13
• Assigned to: Sergei Golubchik
• CPU Architecture: Any

[27 May 2003 3:37] Alexander Keremidarski

Description:
ORDER BY ... DESC optimization fails when using a merged table and selecting rows from that table using a composite index.

If WHERE clause consist of Exact match + Range match on 2 columns index when and results are sorted descendingly, then an incomplete or zero set of results are returned.

With using IGNORE INDEX query returns correct result.

How to repeat:
create table a (x int, y int, index xy(x, y));
create table b (x int, y int, index xy(x, y));
create table c (x int, y int, index xy(x, y)) type=merge union(a,b);

insert into a values(1, 2);
insert into b values(1, 3);

select * from c where x = 1 and y < 5 order by y;
+------+------+
| x    | y    |
+------+------+
|    1 |    2 |
|    1 |    3 |
+------+------+

select * from c where x = 1 and y < 5 order by y desc;
Empty set (0.00 sec)

mysql> select * from c ignore index (xy) where x = 1 and y < 5 order by y desc;
  
+------+------+
| x    | y    |
+------+------+
|    1 |    3 |
|    1 |    2 |
+------+------+

[4 Jul 2003 6:58] Sergei Golubchik

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

fixed in 4.0.14