MySQL Forums
Forum List  »  Partitioning

BUG - ORDER BY when WHERE contains non partitioned index column
Posted by: duke hound
Date: June 22, 2008 11:35AM

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,

Options: ReplyQuote


Subject
Views
Written By
Posted
BUG - ORDER BY when WHERE contains non partitioned index column
4479
June 22, 2008 11:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.