Bug #6699 ORDER BY fails on MERGE table when reading with index
Submitted: 18 Nov 2004 17:03 Modified: 21 Dec 2004 0:27
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.7 OS:Windows (Windows 2000)
Assigned to: Sergey Petrunya CPU Architecture:Any

[18 Nov 2004 17:03] Dave Pullin
Description:
rows are returned in the wrong order when selecting from a merge table when the compound index is used with a partial match.

Looks like bug http://bugs.mysql.com/bug.php?id=515 but that is supposed to have been fixed.

The 3 selects below  should return they same rows in the same order, by the first one is in the wrong order.

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.7-nt-log |
+--------------+
1 row in set (0.00 sec)

mysql> drop TABLE if exists tableC, tableB, tableA ;
Query OK, 0 rows affected, 3 warnings (0.02 sec)

mysql> CREATE TABLE tableA (A int,B int,C int, index (A,B,C));
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE tableB (A int,B int,C int, index (A,B,C));
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE tableC (A int,B int,C int, index (A,B,C))
    ->   engine=merge union=(tableA ,tableB );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tableA (A,B,C) values  (1,1,0),(1,2,0);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into tableB (A,B,C) values  (1,1,1),(1,2,1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select  A,B,C from tableC where a=1 order by A,B,C;
+------+------+------+
| A    | B    | C    |
+------+------+------+
|    1 |    1 |    0 |
|    1 |    2 |    0 |
|    1 |    1 |    1 |
|    1 |    2 |    1 |
+------+------+------+
4 rows in set (0.00 sec)

mysql> select  A,B,C from tableC ignore index(A) where a=1 order by A,B,C;
+------+------+------+
| A    | B    | C    |
+------+------+------+
|    1 |    1 |    0 |
|    1 |    1 |    1 |
|    1 |    2 |    0 |
|    1 |    2 |    1 |
+------+------+------+
4 rows in set (0.01 sec)

mysql> select  A,B,C from tableC order by A,B,C;
+------+------+------+
| A    | B    | C    |
+------+------+------+
|    1 |    1 |    0 |
|    1 |    1 |    1 |
|    1 |    2 |    0 |
|    1 |    2 |    1 |
+------+------+------+
4 rows in set (0.00 sec)

mysql>

How to repeat:
select version();
drop TABLE if exists tableC, tableB, tableA ;
CREATE TABLE tableA (A int,B int,C int, index (A,B,C));
CREATE TABLE tableB (A int,B int,C int, index (A,B,C));
CREATE TABLE tableC (A int,B int,C int, index (A,B,C))
  engine=merge union=(tableA ,tableB );
insert into tableA (A,B,C) values  (1,1,0),(1,2,0);
insert into tableB (A,B,C) values  (1,1,1),(1,2,1);
select  A,B,C from tableC where a=1 order by A,B,C;
select  A,B,C from tableC ignore index(A) where a=1 order by A,B,C;
select  A,B,C from tableC order by A,B,C;

/* these 3 selects should return they same rows in the same order, by the first one is in the wrong order */
[18 Nov 2004 18:32] MySQL Verification Team
Verified also with source tree on Linux.
[20 Nov 2004 0:09] Daniel Grace
This might be related to Bug #6728 (which I just submitted) which seems to be the same problem on queries with UNION.

I say this because it seems feasible to me that MySQL might treat MERGE tables like UNIONs internally.
[29 Nov 2004 3:54] Sergey Petrunya
ChangeSet@1.2168, 2004-11-29 06:51:30+03:00, sergefp@mysql.com
[21 Dec 2004 0:27] Sergey Petrunya
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html