BUG - ORDER BY when WHERE contains non partitioned index column

[duke hound]

BUG - ORDER BY when WHERE contains non partitioned index column

Hello,

I think I found a bug, but before I report it I wanted to check with the community.

I am using windows xp sp2, v6.0.4-alpha

CREATE DATABASE bug;
CREATE TABLE bug.t (t_a BIGINT,t_b BIGINT, PRIMARY KEY (t_a), INDEX (t_b) ) ENGINE = InnoDB PARTITION BY HASH( MOD(t_a,2) ) PARTITIONS 2;
INSERT INTO bug.t VALUES (0,0),(1,0);
SELECT * FROM bug.t WHERE t_b=0 ORDER BY t_a DESC;

+-----+------+
| t_a | t_b  |
+-----+------+
|   0 |    0 |
|   1 |    0 |
+-----+------+

2 rows in set (0.00 sec)

I expected:

+-----+------+
| t_a | t_b  |
+-----+------+
|   1 |    0 |
|   0 |    0 |
+-----+------+
2 rows in set (0.00 sec)

This is more than just a switch from DESC to ASC as described in this bug post for ndb tables:
http://bugs.mysql.com/bug.php?id=33061

To further show this distinction execute the following:
[Continuing with the above SQL statements]

INSERT IGNORE INTO bug.t VALUES (0,0),(1,0),(2,0),(3,0);
SELECT * FROM bug.t WHERE t_b=0 ORDER BY t_a DESC;

+-----+------+
| t_a | t_b  |
+-----+------+
|   2 |    0 |
|   0 |    0 |
|   3 |    0 |
|   1 |    0 |
+-----+------+
4 rows in set (0.00 sec)

I expected:

+-----+------+
| t_a | t_b  |
+-----+------+
|   3 |    0 |
|   2 |    0 |
|   1 |    0 |
|   0 |    0 |
+-----+------+
4 rows in set (0.00 sec)

Note: It does order by correctly on a per partition basis rather than on a whole table basis.

This is more than just an issue with the DESC flag as seen by,

SELECT * FROM bug.t WHERE t_b=0 ORDER BY t_a;
+-----+------+
| t_a | t_b  |
+-----+------+
|   0 |    0 |
|   2 |    0 |
|   1 |    0 |
|   3 |    0 |
+-----+------+
4 rows in set (0.00 sec)

SELECT * FROM bug.t WHERE t_b=0 ORDER BY t_a ASC;
+-----+------+
| t_a | t_b  |
+-----+------+
|   0 |    0 |
|   2 |    0 |
|   1 |    0 |
|   3 |    0 |
+-----+------+

I expected from both of the above:

+-----+------+
| t_a | t_b  |
+-----+------+
|   0 |    0 |
|   1 |    0 |
|   2 |    0 |
|   3 |    0 |
+-----+------+

Note: I do not experience this issue if I remove the WHERE clause;

Note: I do not experience this issue if I remove the INDEX (t_b) and retain the where clause;

ALTER TABLE bug.t DROP INDEX t_b;
SELECT * FROM bug.t WHERE t_b=0 ORDER BY t_a;
+-----+------+
| t_a | t_b  |
+-----+------+
|   0 |    0 |
|   1 |    0 |
|   2 |    0 |
|   3 |    0 |
+-----+------+
4 rows in set (0.00 sec)

I expected the above result.

SELECT * FROM bug.t WHERE t_b=0 ORDER BY t_a DESC;
+-----+------+
| t_a | t_b  |
+-----+------+
|   3 |    0 |
|   2 |    0 |
|   1 |    0 |
|   0 |    0 |
+-----+------+
4 rows in set (0.00 sec)

I expected the above result.

Note: I have not tested with other partition types other than HASH

Requested Change:
When one partitions a table and searches on an index not in the partition expression, that ORDER BY searches work on the entire table, rather than on a per partition basis. (in effect merging the query results of the individual partitions).

I will give this post a few days before I report it as a bug.

Thanks,