I've submitted a bug report (Bug #37439) titled
"Incorrect output order from ORDER BY using 2 field index in MEMORY BTREE".
I'm getting incorrectly ordered records out from a MEMORY database with
a two-field BTREE index. This fails in MySQL 5.0.27 on both Windows 2000 and
Fedora Core 5, two rather dissimilar systems.
MySQL support requests that it be tested on MySQL 5.0.51. So
if anyone has 5.0.51 handy, I'd appreciate a test as below.
If if fails, please post here, or update the MySQL
bug report Bug #37439.
Thanks.
John Nagle
Reproduce as follows:
CREATE TABLE ratingqueue
(
domain VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY,
requestor_ip_hash INT,
rating_state ENUM ('running','starting','queued','hold')
NOT NULL DEFAULT 'queued',
server VARCHAR(63) NULL,
priority SMALLINT DEFAULT 0,
update_timestamp TIMESTAMP NOT NULL,
request_timestamp TIMESTAMP NOT NULL,
INDEX(requestor_ip_hash),
INDEX USING BTREE(rating_state, request_timestamp)
) ENGINE=MEMORY;
LOAD DATA INFILE 'testdata100' INTO TABLE ratingqueue;
SELECT * FROM ratingqueue ORDER BY rating_state;
Output should 100 rows in the order 'running','starting','queued','hold'.
-- Data set "testdata100" START
msn.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
youtube.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
baidu.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
orkut.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
qq.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
wikipedia.org 651586 queued \N 0 2008-06-16 12:33:27 2008-06-16 12:33:27
yahoo.co.jp 651586 queued \N 0 2008-06-16 12:33:25 2008-06-16 12:33:25
microsoft.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
sina.com.cn 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
megaupload.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
blogger.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
ebay.com 651586 queued \N 0 2008-06-16 12:33:25 2008-06-16 12:33:25
amazon.co.uk 651586 hold \N 0 2008-06-16 12:33:19 2008-06-16 12:33:19
rapidshare.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
facebook.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
sohu.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
fotolog.net 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
friendster.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
mail.ru 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.de 651586 queued \N 0 2008-06-16 12:33:26 2008-06-16 12:33:26
passport.net 651586 queued \N 0 2008-06-16 12:33:24 2008-06-16 12:33:24
google.com.br 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.fr 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
amazon.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
yahoo.com.cn 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
bbc.co.uk 651586 queued \N 0 2008-06-16 12:33:25 2008-06-16 12:33:25
www.samoandomains.ws 651586 hold \N 0 2008-06-16 12:33:22 2008-06-16 12:33:22
google.co.jp 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.es 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
wretch.cc 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
uol.com.br 651586 queued \N 0 2008-06-16 12:33:24 2008-06-16 12:33:24
yandex.ru 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
skyblog.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.pl 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
craigslist.org 651586 queued \N 0 2008-06-16 12:33:26 2008-06-16 12:33:26
google.cl 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
flickr.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
tom.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.cn 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
photobucket.com 651586 queued \N 0 2008-06-16 12:33:25 2008-06-16 12:33:25
onet.pl 651586 queued \N 0 2008-06-16 12:33:22 2008-06-16 12:33:22
imageshack.us 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.com.mx 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
fc2.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
aol.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
ebay.co.uk 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
mixi.jp 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
xunlei.com 651586 queued \N 0 2008-06-16 12:33:26 2008-06-16 12:33:26
rambler.ru 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
cnn.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.it 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
allegro.pl 651586 queued \N 0 2008-06-16 12:33:26 2008-06-16 12:33:26
google.co.in 651586 queued \N 0 2008-06-16 12:33:24 2008-06-16 12:33:24
ebay.de 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.com.ar 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.com.pe 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
terra.com.br 651586 running animats-5px372h 0 2008-06-16 12:33:24 2008-06-16 12:33:18
livejournal.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
seznam.cz 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
xanga.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
geocities.com 651586 running animats-5px372h 0 2008-06-16 12:33:27 2008-06-16 12:33:18
imagevenue.com 651586 queued \N 0 2008-06-16 12:33:19 2008-06-16 12:33:19
adultfriendfinder.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
dailymotion.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
uwants.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
rakuten.co.jp 651586 queued \N 0 2008-06-16 12:33:25 2008-06-16 12:33:25
googlesyndication.com 651586 queued \N 0 2008-06-16 12:33:23 2008-06-16 12:33:23
wp.pl 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
discuss.com.hk 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
adobe.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
bebo.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
livedoor.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.co.ve 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
digg.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
www.safenames.net 651586 hold \N 0 2008-06-16 12:33:22 2008-06-16 12:33:22
apple.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
soso.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
vnet.cn 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
tagged.com 651586 queued \N 0 2008-06-16 12:33:25 2008-06-16 12:33:25
wordpress.com 651586 queued \N 0 2008-06-16 12:33:24 2008-06-16 12:33:24
one.lt 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
badongo.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
deviantart.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
sendspace.com 651586 queued \N 0 2008-06-16 12:33:24 2008-06-16 12:33:24
google.co.il 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
walla.co.il 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
alibaba.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.com.au 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
vnexpress.net 651586 queued \N 0 2008-06-16 12:33:22 2008-06-16 12:33:22
about.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
pchome.com.tw 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
ig.com.br 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
comcast.net 651586 queued \N 0 2008-06-16 12:33:25 2008-06-16 12:33:25
sourceforge.net 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
rapidshare.de 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
kooora.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.nl 651586 queued \N 0 2008-06-16 12:33:24 2008-06-16 12:33:24
statcounter.com 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
goo.ne.jp 651586 hold \N 0 2008-06-16 12:33:18 2008-06-16 12:33:18
-- Data set "testdata100" END