The mysql manual claims that the archive engine supports "order by" operations, but queries using order by seem to return invalid results. The same query with other storage engines seem to work fine.
For example, I created these two test tables, one using the archive engine...
create table test (list varchar(255) not null, email varchar(255) not null, status enum ("subscribed", "unsubscribed") not null, occured timestamp not null) engine=archive;
create table test2 (list varchar(255) not null, email varchar(255) not null, status enum ("subscribed", "unsubscribed") not null, occured timestamp not null);
Then inserting the same test data into both...
insert into test values ("test@foo.com", "mt@foo.com", "subscribed", "2006-01-09 16:14:41");
insert into test values ("test@foo.com", "mt2@foo.com", "subscribed", "2006-01-09 16:14:57");
insert into test values ("test2@foo.com", "mt@foo.com", "subscribed", "2006-01-09 16:15:05");
insert into test values ("test2@foo.com", "mt2@foo.com", "subscribed", "2006-01-09 16:15:09");
insert into test values ("test@foo.com", "mt2@foo.com", "unsubscribed", "2006-01-09 16:15:28");
insert into test values ("test2@foo.com", "mt2@foo.com", "unsubscribed", "2006-01-09 16:15:45");
insert into test values ("test2@foo.com", "mt2@foo.com", "subscribed", "2006-01-09 16:15:51");
insert into test values ("test@foo.com", "mt3@foo.com", "subscribed", "2006-01-11 11:28:13");
insert into test values ("test2@foo.com", "mt3@foo.com", "subscribed", "2006-01-11 11:32:32");
insert into test values ("test2@foo.com", "mt4@foo.com", "subscribed", "2006-01-10 11:32:32");
insert into test2 values ("test@foo.com", "mt@foo.com", "subscribed", "2006-01-09 16:14:41");
insert into test2 values ("test@foo.com", "mt2@foo.com", "subscribed", "2006-01-09 16:14:57");
insert into test2 values ("test2@foo.com", "mt@foo.com", "subscribed", "2006-01-09 16:15:05");
insert into test2 values ("test2@foo.com", "mt2@foo.com", "subscribed", "2006-01-09 16:15:09");
insert into test2 values ("test@foo.com", "mt2@foo.com", "unsubscribed", "2006-01-09 16:15:28");
insert into test2 values ("test2@foo.com", "mt2@foo.com", "unsubscribed", "2006-01-09 16:15:45");
insert into test2 values ("test2@foo.com", "mt2@foo.com", "subscribed", "2006-01-09 16:15:51");
insert into test2 values ("test@foo.com", "mt3@foo.com", "subscribed", "2006-01-11 11:28:13");
insert into test2 values ("test2@foo.com", "mt3@foo.com", "subscribed", "2006-01-11 11:32:32");
insert into test2 values ("test2@foo.com", "mt4@foo.com", "subscribed", "2006-01-10 11:32:32");
Now trying to query them, I get some strange results...
mysql> select * from test order by occured;
Empty set (0.00 sec)
mysql> select * from test order by occured desc;
+---------------+-------------+--------------+---------------------+
| list | email | status | occured |
+---------------+-------------+--------------+---------------------+
|
test2@foo.com |
mt3@foo.com | subscribed | 2006-01-11 11:32:32 |
|
test@foo.com |
mt3@foo.com | subscribed | 2006-01-11 11:28:13 |
|
test2@foo.com |
mt4@foo.com | subscribed | 2006-01-10 11:32:32 |
|
test2@foo.com |
mt2@foo.com | subscribed | 2006-01-09 16:15:51 |
|
test2@foo.com |
mt2@foo.com | unsubscribed | 2006-01-09 16:15:45 |
|
test@foo.com |
mt2@foo.com | unsubscribed | 2006-01-09 16:15:28 |
|
test2@foo.com |
mt2@foo.com | subscribed | 2006-01-09 16:15:09 |
|
test2@foo.com |
mt@foo.com | subscribed | 2006-01-09 16:15:05 |
|
test@foo.com |
mt2@foo.com | subscribed | 2006-01-09 16:14:57 |
|
test2@foo.com |
mt@foo.com | subscribed | 2006-01-09 16:15:05 |
+---------------+-------------+--------------+---------------------+
10 rows in set (0.00 sec)
mysql> select * from test order by occured asc;
Empty set (0.00 sec)
Repeating the same query on test2 provides exactly the results one would expect (I won't bother to paste here).
Even stranger is that it seems to break where clauses...
Without the order by:
mysql> select * from test where list like "test@foo.com";
+--------------+-------------+--------------+---------------------+
| list | email | status | occured |
+--------------+-------------+--------------+---------------------+
|
test@foo.com |
mt@foo.com | subscribed | 2006-01-09 16:14:41 |
|
test@foo.com |
mt2@foo.com | subscribed | 2006-01-09 16:14:57 |
|
test@foo.com |
mt2@foo.com | unsubscribed | 2006-01-09 16:15:28 |
|
test@foo.com |
mt3@foo.com | subscribed | 2006-01-11 11:28:13 |
+--------------+-------------+--------------+---------------------+
4 rows in set (0.00 sec)
Using the order by:
mysql> select * from test where list like "test@foo.com" order by occured desc;
+---------------+-------------+--------------+---------------------+
| list | email | status | occured |
+---------------+-------------+--------------+---------------------+
|
test@foo.com |
mt3@foo.com | subscribed | 2006-01-11 11:28:13 |
|
test@foo.com |
mt2@foo.com | unsubscribed | 2006-01-09 16:15:28 |
|
test@foo.com |
mt2@foo.com | subscribed | 2006-01-09 16:14:57 |
|
test2@foo.com |
mt@foo.com | subscribed | 2006-01-09 16:15:05 |
+---------------+-------------+--------------+---------------------+
4 rows in set (0.00 sec)
Notice the "list" entry for the fourth result. The same thing happens using = instead of like.
Again using the same query against test2 returns valid results.
It dosen't seem to matter which column I choose to order by either.
For the record, this is mysql 5.0.15 on red hat enterprise linux 3
Any idea what's going on here?