Old ORDER BY enum, int incorrect sort order bug appears to be stillaround - or maybe new MEMORY BTREE bug.

This is a discussion on Old ORDER BY enum, int incorrect sort order bug appears to be stillaround - or maybe new MEMORY BTREE bug. within the MySQL Database forums, part of the Database Forums category; John Nagle wrote: > Several times since 2004, people have reported a bug in which > ORDER BY clauses involving ...


Go Back   Usenet Forums > Database Forums > MySQL Database

Reload this Page Old ORDER BY enum, int incorrect sort order bug appears to be stillaround - or maybe new MEMORY BTREE bug.

FAQ Members List Calendar Search Today's Posts Mark Forums Read

Reply
Page 1 of 2 1 2 >

 

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 06-16-2008
Paul Lautman
 
Posts: n/a
Default Re: Old ORDER BY enum, int incorrect sort order bug appears to be still around - or maybe new MEMORY BTREE bug.
John Nagle wrote:
> Several times since 2004, people have reported a bug in which
> ORDER BY clauses involving ENUMs returned incorrectly ordered results.
> I seem to have hit a similar bug. I'm making this query
> in the MySQL query browser:

I'm sure there must be some good reason for using an ENUM field, but I am
yet to find it. I always use a separate values table and a foreign key.


Reply With Quote
  #2 (permalink)  
Old 06-16-2008
John Nagle
 
Posts: n/a
Default Old ORDER BY enum, int incorrect sort order bug appears to be stillaround - or maybe new MEMORY BTREE bug.
Several times since 2004, people have reported a bug in which
ORDER BY clauses involving ENUMs returned incorrectly ordered results.
I seem to have hit a similar bug. I'm making this query
in the MySQL query browser:

SELECT * FROM ratingqueue ORDER BY rating_state, request_timestamp

The "rating_state" field isn't affecting the order; the output is
ordered entirely by "request_timestamp".

The table is:

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(domain),
INDEX(requestor_ip_hash), INDEX USING BTREE(rating_state, request_timestamp)
) ENGINE=MEMORY;

Previous reports:

http://bugs.mysql.com/967 (Status: No feedback)
http://bugs.mysql.com/7516 (Status: Can't repeat)
http://www.issociate.de/board/post/2...ENUM,_INT.html
(Status: not submitted as formal bug report.)

In all cases reported, there's an ORDER BY clause with an ENUM followed by some
numeric value. There's no indication of this ever being fixed.
That's similar to the case I have. I'll try to come up with a repeatable
test case. But if you read report #7516, this wasn't repeatable; repeating
the same sequence of MySQL commands produced different output orders.

I tried the test case in Bug 967 and can't reproduce that problem.
I'm seeing this using the MEMORY database and BTREE indices, but
#7516 was using MyISAM, the report from Germany in 2003 tried both
MyISAM and InnoDB, and #967 didn't say.

Anything further known about this?

I'm testing on "5.0.27-community-nt".

------

Update: it gets worse:

SELECT * FROM ratingqueue ORDER BY rating_state

in the MySQL query browser

is returning incorrectly ordered results. The order is just bogus;
the first entries are 'queued', then lots of 'hold' entries, then
one 'queued', lots more 'hold', then a bunch of 'queued'. In fact,
the order is the same as when I query

SELECT * FROM ratingqueue ORDER BY rating_state, request_timestamp

An EXPLAIN of this says

1, 'SIMPLE', 'ratingqueue', 'index', '', 'rating_state', '5', '', 1544, ''

So it's using the BTREE index, which seems to be ignoring the ENUM field.
I tried a CHECK TABLE, but on this MEMORY table, that just gets "The storage
engine for the table doesn't support check".

The table was created by a multithreaded, multiprocess program which was
making INSERT, DELETE, and UPDATE operations at a high rate. Two test
runs have produced similar errors. There are 1544 rows in the table.

This was working before I used BTREE indices. (Without BTREE indices,
some SELECTs are linear searches and are too slow. By default, the
MEMORY engine uses hashes, which are only useful for equality tests.)

I'll try to put a repeatable test case together.

John Nagle
Reply With Quote
  #3 (permalink)  
Old 06-16-2008
Luuk
 
Posts: n/a
Default Re: Old ORDER BY enum, int incorrect sort order bug appears to bestill around - or maybe new MEMORY BTREE bug.
John Nagle schreef:
> Several times since 2004, people have reported a bug in which
> ORDER BY clauses involving ENUMs returned incorrectly ordered results.
> I seem to have hit a similar bug. I'm making this query
> in the MySQL query browser:
>
> SELECT * FROM ratingqueue ORDER BY rating_state, request_timestamp
>
> The "rating_state" field isn't affecting the order; the output is
> ordered entirely by "request_timestamp".
>
> The table is:
>
> 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(domain),
> INDEX(requestor_ip_hash), INDEX USING
> BTREE(rating_state, request_timestamp)
> ) ENGINE=MEMORY;
>
> Previous reports:
>
> http://bugs.mysql.com/967 (Status: No feedback)
> http://bugs.mysql.com/7516 (Status: Can't repeat)
> http://www.issociate.de/board/post/2...ENUM,_INT.html
>
> (Status: not submitted as formal bug report.)
>
> In all cases reported, there's an ORDER BY clause with an ENUM followed
> by some numeric value. There's no indication of this ever being fixed.
> That's similar to the case I have. I'll try to come up with a repeatable
> test case. But if you read report #7516, this wasn't repeatable; repeating
> the same sequence of MySQL commands produced different output orders.
>
> I tried the test case in Bug 967 and can't reproduce that problem.
> I'm seeing this using the MEMORY database and BTREE indices, but
> #7516 was using MyISAM, the report from Germany in 2003 tried both
> MyISAM and InnoDB, and #967 didn't say.
>
> Anything further known about this?
>
> I'm testing on "5.0.27-community-nt".
>
> ------
>
> Update: it gets worse:
>
> SELECT * FROM ratingqueue ORDER BY rating_state
>
> in the MySQL query browser
>
> is returning incorrectly ordered results. The order is just bogus;
> the first entries are 'queued', then lots of 'hold' entries, then
> one 'queued', lots more 'hold', then a bunch of 'queued'. In fact,
> the order is the same as when I query
>
> SELECT * FROM ratingqueue ORDER BY rating_state, request_timestamp
>
> An EXPLAIN of this says
>
> 1, 'SIMPLE', 'ratingqueue', 'index', '', 'rating_state', '5', '', 1544, ''
>
> So it's using the BTREE index, which seems to be ignoring the ENUM field.
> I tried a CHECK TABLE, but on this MEMORY table, that just gets "The
> storage
> engine for the table doesn't support check".
>
> The table was created by a multithreaded, multiprocess program which was
> making INSERT, DELETE, and UPDATE operations at a high rate. Two test
> runs have produced similar errors. There are 1544 rows in the table.
>
> This was working before I used BTREE indices. (Without BTREE indices,
> some SELECTs are linear searches and are too slow. By default, the
> MEMORY engine uses hashes, which are only useful for equality tests.)
>
> I'll try to put a repeatable test case together.
>
> John Nagle

small resultset, but seems to be ok:

mysql> show index from ratingqueue;
+-------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null |
Index_type | Comment |
+-------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| ratingqueue | 0 | PRIMARY | 1 | domain
| NULL | 10 | NULL | NULL | | HASH
| |
| ratingqueue | 0 | domain | 1 | domain
| NULL | 10 | NULL | NULL | | HASH
| |
| ratingqueue | 1 | domain_2 | 1 | domain
| NULL | 5 | NULL | NULL | | HASH
| |
| ratingqueue | 1 | requestor_ip_hash | 1 |
requestor_ip_hash | NULL | 1 | NULL | NULL | YES |
HASH | |
| ratingqueue | 1 | rating_state | 1 |
rating_state | A | NULL | NULL | NULL | |
BTREE | |
| ratingqueue | 1 | rating_state | 2 |
request_timestamp | A | NULL | NULL | NULL | |
BTREE | |
+-------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)

mysql> explain SELECT * FROM ratingqueue ORDER BY rating_state;
+----+-------------+-------------+-------+---------------+--------------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+--------------+---------+------+------+-------+
| 1 | SIMPLE | ratingqueue | index | NULL | rating_state
| 5 | NULL | 10 | |
+----+-------------+-------------+-------+---------------+--------------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM ratingqueue ORDER BY rating_state;
+--------+-------------------+--------------+--------+----------+---------------------+---------------------+
| domain | requestor_ip_hash | rating_state | server | priority |
update_timestamp | request_timestamp |
+--------+-------------------+--------------+--------+----------+---------------------+---------------------+
| 5 | NULL | starting | NULL | 0 |
2008-06-16 22:30:40 | 2008-06-16 22:27:38 |
| 4 | NULL | starting | NULL | 0 |
2008-06-16 22:30:37 | 2008-06-16 22:27:38 |
| 1 | NULL | queued | NULL | 0 |
2008-06-16 22:28:35 | 2008-06-16 22:27:34 |
| 2 | NULL | queued | NULL | 0 |
2008-06-16 22:28:35 | 2008-06-16 22:27:36 |
| 3 | NULL | queued | NULL | 0 |
2008-06-16 22:28:35 | 2008-06-16 22:27:37 |
| 6 | NULL | queued | NULL | 0 |
2008-06-16 22:28:35 | 2008-06-16 22:27:38 |
| 9 | NULL | queued | NULL | 0 |
2008-06-16 22:28:35 | 2008-06-16 22:27:40 |
| 10 | NULL | queued | NULL | 0 |
2008-06-16 22:28:35 | 2008-06-16 22:27:43 |
| 8 | NULL | hold | NULL | 0 |
2008-06-16 22:30:45 | 2008-06-16 22:27:39 |
| 7 | NULL | hold | NULL | 0 |
2008-06-16 22:30:43 | 2008-06-16 22:27:39 |
+--------+-------------------+--------------+--------+----------+---------------------+---------------------+
10 rows in set (0.00 sec)

mysql>


mysqlversion 5.0.26 on opensuse



--
Luuk
Reply With Quote
  #4 (permalink)  
Old 06-17-2008
John Nagle
 
Posts: n/a
Default Re: Old ORDER BY enum, int incorrect sort order bug appears to bestill around - or maybe new MEMORY BTREE bug.
Luuk wrote:
> John Nagle schreef:

>>
>> Update: it gets worse:
>>
>> SELECT * FROM ratingqueue ORDER BY rating_state
>>
>> in the MySQL query browser
>>
>> is returning incorrectly ordered results. The order is just bogus;
>> the first entries are 'queued', then lots of 'hold' entries, then
>> one 'queued', lots more 'hold', then a bunch of 'queued'
>
> small resultset, but seems to be ok:

I'm testing various table sizes. It's failing at n=1124, failing
at n=100, and not failing at n=50 or n-25.

Please try reproducing with this:

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;

-- 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



John Nagle
Reply With Quote
  #5 (permalink)  
Old 06-17-2008
John Nagle
 
Posts: n/a
Default Request test of this bug on MySQL 5.0.51 - Bug #37439
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




Reply With Quote
  #6 (permalink)  
Old 06-17-2008
Gordon Burditt
 
Posts: n/a
Default Re: Request test of this bug on MySQL 5.0.51 - Bug #37439
> 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.

I have 5.0.51a, is that close enough? Running on FreeBSD 6.2, Intel
architecture. Quick results: it looks like it sorts correctly.

I modified the script a little (which shouldn't make any difference):

(a) drop table initially so I can re-run this multiple times.
(b) Use an absolute path name for the data file so it can reference
the temporary directory where I unpacked this stuff.
(c) show the created table at the end, just in case charset issues
or something else unexpected crept in here (like different
defaults on different systems).
(d) Show server stats so things like server version are visible.

run as:
% mysql -v -B test |& tee log
mysql> \. run.sql
mysql> quit


--------------
DROP TABLE IF EXISTS ratingqueue
--------------

--------------
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 '/tmp/my/testdata100' INTO TABLE ratingqueue
--------------

--------------
SELECT * FROM ratingqueue ORDER BY rating_state
--------------

domain requestor_ip_hash rating_state server priority update_timestamp request_timestamp
geocities.com 651586 running animats-5px372h 0 2008-06-16 12:33:27 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
imagevenue.com 651586 queued NULL 0 2008-06-16 12:33:19 2008-06-16 12:33:19
vnexpress.net 651586 queued NULL 0 2008-06-16 12:33:22 2008-06-16 12:33:22
onet.pl 651586 queued NULL 0 2008-06-16 12:33:22 2008-06-16 12:33:22
googlesyndication.com 651586 queued NULL 0 2008-06-16 12:33:23 2008-06-16 12:33:23
uol.com.br 651586 queued NULL 0 2008-06-16 12:33:24 2008-06-16 12:33:24
wordpress.com 651586 queued NULL 0 2008-06-16 12:33:24 2008-06-16 12:33:24
google.co.in 651586 queued NULL 0 2008-06-16 12:33:24 2008-06-16 12:33:24
sendspace.com 651586 queued NULL 0 2008-06-16 12:33:24 2008-06-16 12:33:24
google.nl 651586 queued NULL 0 2008-06-16 12:33:24 2008-06-16 12:33:24
passport.net 651586 queued NULL 0 2008-06-16 12:33:24 2008-06-16 12:33:24
photobucket.com 651586 queued NULL 0 2008-06-16 12:33:25 2008-06-16 12:33:25
yahoo.co.jp 651586 queued NULL 0 2008-06-16 12:33:25 2008-06-16 12:33:25
comcast.net 651586 queued NULL 0 2008-06-16 12:33:25 2008-06-16 12:33:25
bbc.co.uk 651586 queued NULL 0 2008-06-16 12:33:25 2008-06-16 12:33:25
rakuten.co.jp 651586 queued NULL 0 2008-06-16 12:33:25 2008-06-16 12:33:25
tagged.com 651586 queued NULL 0 2008-06-16 12:33:25 2008-06-16 12:33:25
ebay.com 651586 queued NULL 0 2008-06-16 12:33:25 2008-06-16 12:33:25
allegro.pl 651586 queued NULL 0 2008-06-16 12:33:26 2008-06-16 12:33:26
craigslist.org 651586 queued NULL 0 2008-06-16 12:33:26 2008-06-16 12:33:26
google.de 651586 queued NULL 0 2008-06-16 12:33:26 2008-06-16 12:33:26
xunlei.com 651586 queued NULL 0 2008-06-16 12:33:26 2008-06-16 12:33:26
wikipedia.org 651586 queued NULL 0 2008-06-16 12:33:27 2008-06-16 12:33:27
msn.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
dailymotion.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
livejournal.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.com.ar 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
rambler.ru 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
ebay.co.uk 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.com.mx 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
flickr.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
statcounter.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.pl 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
rapidshare.de 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.co.jp 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
ig.com.br 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
yahoo.com.cn 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.com.br 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
walla.co.il 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
mail.ru 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
deviantart.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
sohu.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
soso.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
megaupload.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
digg.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
bebo.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
orkut.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
wp.pl 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
uwants.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
seznam.cz 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.com.pe 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
cnn.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
mixi.jp 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
fc2.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
tom.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
goo.ne.jp 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
yandex.ru 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
kooora.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.es 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
about.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.fr 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
alibaba.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
fotolog.net 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
one.lt 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
rapidshare.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
vnet.cn 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
blogger.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
microsoft.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
livedoor.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
qq.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
discuss.com.hk 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
youtube.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
adultfriendfinder.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
xanga.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
ebay.de 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.it 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
aol.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
imageshack.us 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.cn 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.cl 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
skyblog.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
wretch.cc 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
sourceforge.net 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
pchome.com.tw 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
amazon.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.com.au 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.co.il 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
friendster.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
badongo.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
facebook.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
apple.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
sina.com.cn 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
google.co.ve 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
adobe.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
baidu.com 651586 hold NULL 0 2008-06-16 12:33:18 2008-06-16 12:33:18
amazon.co.uk 651586 hold NULL 0 2008-06-16 12:33:19 2008-06-16 12:33:19
www.safenames.net 651586 hold NULL 0 2008-06-16 12:33:22 2008-06-16 12:33:22
www.samoandomains.ws 651586 hold NULL 0 2008-06-16 12:33:22 2008-06-16 12:33:22
--------------
SHOW CREATE TABLE ratingqueue
--------------

Table Create Table
ratingqueue CREATE TABLE `ratingqueue` (\n `domain` varchar(255) NOT NULL,\n `requestor_ip_hash` int(11) default NULL,\n `rating_state` enum('running','starting','queued','hold') NOT NULL default 'queued',\n `server` varchar(63) default NULL,\n `priority` smallint(6) default '0',\n `update_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,\n `request_timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',\n PRIMARY KEY (`domain`),\n UNIQUE KEY `domain` (`domain`),\n KEY `requestor_ip_hash` (`requestor_ip_hash`),\n KEY `rating_state` USING BTREE (`rating_state`,`request_timestamp`)\n) ENGINE=MEMORY DEFAULT CHARSET=latin1
--------------
mysql Ver 14.12 Distrib 5.0.51a, for portbld-freebsd6.2 (i386) using 5.2

Connection id: 1200015
Current database: test
Current user: (censored)
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.51a-log FreeBSD port: mysql-server-5.0.51a
Protocol version: 10
Connection: (censored) via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 42 days 22 hours 55 min 50 sec

Threads: 5 Questions: 4403216 Slow queries: 86 Opens: 3027 Flush tables: 1 Open tables: 58 Queries per second avg: 1.186
--------------

Reply With Quote
  #7 (permalink)  
Old 06-17-2008
John Nagle
 
Posts: n/a
Default Re: Request test of this bug on MySQL 5.0.51 - Bug #37439
Gordon Burditt wrote:
>> 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.
>
> I have 5.0.51a, is that close enough? Running on FreeBSD 6.2, Intel
> architecture. Quick results: it looks like it sorts correctly.

Thanks. Mind doing an

EXPLAIN SELECT * FROM ratingqueue ORDER BY rating_state;

That will settle whether the newer version is using the
index.

I get an EXPLAIN result of

1, 'SIMPLE', 'ratingqueue', 'index', '', 'rating_state', '5', '', 1544, ''

If you get an "EXPLAIN" result with "filesort" in it, it didn't use the
index.

Can anyone else get this to fail? There are at least three
previous unconfirmed bug reports since 2003 of incorrect sorting in
cases involving multiple keys where one key is an ENUM, so there
may be a hard-to-reproduce bug in this area.

John Nagle
Reply With Quote
  #8 (permalink)  
Old 06-17-2008
Gordon Burditt
 
Posts: n/a
Default Re: Request test of this bug on MySQL 5.0.51 - Bug #37439
>>> 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.
>>
>> I have 5.0.51a, is that close enough? Running on FreeBSD 6.2, Intel
>> architecture. Quick results: it looks like it sorts correctly.
>
> Thanks. Mind doing an
>
>EXPLAIN SELECT * FROM ratingqueue ORDER BY rating_state;
>
>That will settle whether the newer version is using the
>index.
>
>I get an EXPLAIN result of
>
>1, 'SIMPLE', 'ratingqueue', 'index', '', 'rating_state', '5', '', 1544, ''

test> explain select * from ratingqueue order by rating_state;
+----+-------------+-------------+-------+---------------+--------------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+--------------+---------+------+------+-------+
| 1 | SIMPLE | ratingqueue | index | NULL | rating_state | 5 | NULL | 100 | |
+----+-------------+-------------+-------+---------------+--------------+---------+------+------+-------+
1 row in set (0.00 sec)

Looks like it's using the index.


>If you get an "EXPLAIN" result with "filesort" in it, it didn't use the
>index.
>
> Can anyone else get this to fail? There are at least three
>previous unconfirmed bug reports since 2003 of incorrect sorting in
>cases involving multiple keys where one key is an ENUM, so there
>may be a hard-to-reproduce bug in this area.

Incidentally, could this have anything to do with what fits in the
block size of the filesystem involved? For that test, block size
is 16384, frag size is 2048. Even on this version of FreeBSD, you
can have filesystems with different blocksizes, although setting
up a test of this is not particularly easy. Does MySQL use the
filesystem blocksize to determine what block size IT uses for things
like indexes, or does it always use the same sizes?


Another possible variable is the compiler used. I don't use a binary
distribution. The system compiler is gcc 3.4.6.

Reply With Quote
  #9 (permalink)  
Old 06-17-2008
John Nagle
 
Posts: n/a
Default Re: Request test of this bug on MySQL 5.0.51 - Bug #37439
Gordon Burditt wrote:
>>>> 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.
>>> I have 5.0.51a, is that close enough? Running on FreeBSD 6.2, Intel
>>> architecture. Quick results: it looks like it sorts correctly.
>> Thanks. Mind doing an
>>
>> EXPLAIN SELECT * FROM ratingqueue ORDER BY rating_state;
>>
>> That will settle whether the newer version is using the
>> index.
>>
>> I get an EXPLAIN result of
>>
>> 1, 'SIMPLE', 'ratingqueue', 'index', '', 'rating_state', '5', '', 1544, ''
>
> test> explain select * from ratingqueue order by rating_state;
> +----+-------------+-------------+-------+---------------+--------------+---------+------+------+-------+
> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +----+-------------+-------------+-------+---------------+--------------+---------+------+------+-------+
> | 1 | SIMPLE | ratingqueue | index | NULL | rating_state | 5 | NULL | 100 | |
> +----+-------------+-------------+-------+---------------+--------------+---------+------+------+-------+
> 1 row in set (0.00 sec)
>
> Looks like it's using the index.

Yes, it does. Thanks.

>> Can anyone else get this to fail? There are at least three
>> previous unconfirmed bug reports since 2003 of incorrect sorting in
>> cases involving multiple keys where one key is an ENUM, so there
>> may be a hard-to-reproduce bug in this area.
>
> Incidentally, could this have anything to do with what fits in the
> block size of the filesystem involved? For that test, block size
> is 16384, frag size is 2048. Even on this version of FreeBSD, you
> can have filesystems with different blocksizes, although setting
> up a test of this is not particularly easy. Does MySQL use the
> filesystem blocksize to determine what block size IT uses for things
> like indexes, or does it always use the same sizes?

This is the MEMORY engine. There's no disk access at all.
The data is in RAM.

The bug does seem to depend on the number of rows.
My experience so far on Windows is

n=25 works
n=50 works
n=100 broken
n=1113 broken

> Another possible variable is the compiler used. I don't use a binary
> distribution. The system compiler is gcc 3.4.6.

Can anyone try it on stock 5.0.27 on Red Hat Fedora 5? That's a very
common commercial server setup, and it fails there for me. I'd
like to see it fail for someone else with a similar configuration.
Thanks.

John Nagle
Reply With Quote
  #10 (permalink)  
Old 06-18-2008
John Nagle
 
Posts: n/a
Default Re: Request test of this bug on MySQL 5.0.51 - Bug #37439
Gordon Burditt wrote:
>>>> 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.

I run everything in UTF-8 mode, with

ALTER DATABASE testdb DEFAULT CHARACTER SET utf8;

That changes the size of some items. Mind trying with that setting?
This thing is definitely size-sensitive. Thanks.

John Nagle
Reply With Quote
Reply
Page 1 of 2 1 2 >
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are Off
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 04:07 AM.

Contact Us - Usenet Forums - Archive - Top

Powered by vBulletin® Version 3.7.3
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO 3.0.0