MySQL Bugs: #35260: Missing rows in results, also wrong sorting

Bug #35260	Missing rows in results, also wrong sorting
Submitted:	13 Mar 2008 3:24	Modified:	8 Apr 2008 10:26
Reporter:	Nicolae Namolovan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.23	OS:	FreeBSD (7)
Assigned to:		CPU Architecture:	Any

Description:
I can't exactly describe the bug.. I only can say there's missing rows.. And incorrect sorting..

There's rows what should appear in the results, but doesn't appear

mysql torrent1> SELECT id, unread FROM messages WHERE receiver=60505 ORDER BY id DESC LIMIT 0,10;
+---------+--------+
| id      | unread |
+---------+--------+
| 3144053 | no     |
| 3143147 | no     | - 3143803 missing
| 3142989 | no     | - 3142237 missing - 3142171 missing..etc
| 3141997 | no     |
| 3141945 | no     |
| 3141795 | no     |
| 3141053 | no     |
| 3141035 | no     |
| 3140863 | no     |
| 3140629 | no     |
+---------+--------+
10 rows in set (0.00 sec)

mysql torrent1> SELECT id, unread FROM messages WHERE receiver=60505 AND unread='yes' ORDER BY id DESC LIMIT 0,10;
+---------+--------+
| id      | unread |
+---------+--------+
| 3141863 | yes    | - why it doesn't appear at first q. answers ?
| 3142237 | yes    | - why it doesn't appear at first q. answers ?
| 3143803 | yes    | - why it doesn't appear at first q. answers ?
| 3142171 | yes    | - why it doesn't appear at first q. answers ?
| 3142167 | yes    | - why it doesn't appear at first q. answers ?..
| 3141889 | yes    |
| 3140939 | yes    |
| 3141925 | yes    |
+---------+--------+
8 rows in set (0.01 sec)

Second bug, look at sorting of second q. results..

Also for some reason it depends on LIMIT option too..

mysql testings> SELECT id, unread FROM messages_testing_small WHERE receiver=60505 ORDER BY id DESC limit 0,17;
+---------+--------+
| id      | unread |
+---------+--------+
| 3144053 | no     |
| 3143147 | no     |
| 3142989 | no     |
| 3141997 | no     |
| 3141945 | no     |
| 3141795 | no     |
| 3141053 | no     |
| 3141035 | no     |
| 3140863 | no     |
| 3140629 | no     |
| 3140523 | no     |
| 3140521 | no     |
| 3140517 | no     |
| 3140501 | no     |
| 3140481 | no     |
| 3140455 | no     |
| 3140331 | no     |
+---------+--------+
17 rows in set (0.00 sec)

mysql testings> SELECT id, unread FROM messages_testing_small WHERE receiver=60505 ORDER BY id DESC limit 0,18;
+---------+--------+
| id      | unread |
+---------+--------+
| 3144053 | no     |
| 3143803 | yes    |
| 3143147 | no     |
| 3142989 | no     |
| 3142237 | yes    |
| 3142171 | yes    |
| 3142167 | yes    |
| 3141997 | no     |
| 3141945 | no     |
| 3141925 | yes    |
| 3141889 | yes    |
| 3141863 | yes    |
| 3141795 | no     |
| 3141053 | no     |
| 3141035 | no     |
| 3140939 | yes    |
| 3140863 | no     |
| 3140629 | no     |
+---------+--------+
18 rows in set (0.00 sec)

Only limit param was changed between these two q.

How to repeat:
I finded out what the bug disappear at removing of the "receiver" key or at removing of the "`msg` text" column. Also the bug is "data" specific, I mean if I remove rows, the bug disapear.. I know this sound strange, and more strange to me..

 CREATE TABLE `messages_testing_small` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `receiver` int(10) unsigned NOT NULL DEFAULT '0',
  `msg` text,
  `unread` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'yes',
  KEY `receiver` (`receiver`),
  KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3145420 DEFAULT CHARSET=utf8

You must reproduce with this data inside.. (I'll try to attache)
By the way, I can't reproduce this bug under mysql 5.1.22, so it must be 5.1.23 only

Suggested fix:
Unknow

Thank you for the bug report. Could you please print here the results you
got with 5.1.22?. Thanks in advance.

As I said, I can't reproduce the bug with 5.1.22.. But I was able to reproduce with another 5.1.23

Here is the res. from 5.1.22, just as expected..

mysql test> SELECT id, unread FROM messages_testing_small WHERE receiver=60505 ORDER BY id DESC LIMIT 0,10;
+---------+--------+
| id      | unread |
+---------+--------+
| 3144053 | no     |
| 3143803 | yes    |
| 3143147 | no     |
| 3142989 | no     |
| 3142237 | yes    |
| 3142171 | yes    |
| 3142167 | yes    |
| 3141997 | no     |
| 3141945 | no     |
| 3141925 | yes    |
+---------+--------+
10 rows in set (0.24 sec)

mysql test> SELECT id, unread FROM messages_testing_small WHERE receiver=60505 A    ND unread='yes' ORDER BY id DESC LIMIT 0,10;
+---------+--------+
| id      | unread |
+---------+--------+
| 3143803 | yes    |
| 3142237 | yes    |
| 3142171 | yes    |
| 3142167 | yes    |
| 3141925 | yes    |
| 3141889 | yes    |
| 3141863 | yes    |
| 3140939 | yes    |
+---------+--------+
8 rows in set (0.01 sec)

mysql test> SELECT id, unread FROM messages_testing_small WHERE receiver=60505 ORDER
    -> BY id DESC limit 0,17;
+---------+--------+
| id      | unread |
+---------+--------+
| 3144053 | no     |
| 3143803 | yes    |
| 3143147 | no     |
| 3142989 | no     |
| 3142237 | yes    |
| 3142171 | yes    |
| 3142167 | yes    |
| 3141997 | no     |
| 3141945 | no     |
| 3141925 | yes    |
| 3141889 | yes    |
| 3141863 | yes    |
| 3141795 | no     |
| 3141053 | no     |
| 3141035 | no     |
| 3140939 | yes    |
| 3140863 | no     |
+---------+--------+
17 rows in set (0.01 sec)

mysql test> SELECT id, unread FROM messages_testing_small WHERE receiver=60505 ORDER
    -> BY id DESC limit 0,18;
+---------+--------+
| id      | unread |
+---------+--------+
| 3144053 | no     |
| 3143803 | yes    |
| 3143147 | no     |
| 3142989 | no     |
| 3142237 | yes    |
| 3142171 | yes    |
| 3142167 | yes    |
| 3141997 | no     |
| 3141945 | no     |
| 3141925 | yes    |
| 3141889 | yes    |
| 3141863 | yes    |
| 3141795 | no     |
| 3141053 | no     |
| 3141035 | no     |
| 3140939 | yes    |
| 3140863 | no     |
| 3140629 | no     |
+---------+--------+
18 rows in set (0.01 sec)

Thank  you for the feedback.

I can not repeat described behavior. Please indicate accurate version of MySQL package which you use. Also please confirm if bug is repeatable if reload your dump on clean system: I want to be sure this is not corrupted table.

>Please indicate accurate version of MySQL package
which you use.
5.1.23

>Also please confirm if bug is repeatable if reload your dump on clean
system: I want to be sure this is not corrupted table.
Yeh, this was my first thought too, unfortunatly any analize, repaire, optimize command didn't help. Also I managed to reproduce into another table by copying insert into select..

Yes, I was able to reproduce on a clean mysql 5.1.23 installation with default conf. I'm not sure, maybe OS counts too, it's freebsd, one is 7 beta, another 6.3.

In fact I was shocked myself seeing this kind of bug..

Downgraded our 5.1.23 to 5.1.22, (since workaround what seemed to work, didn't and hit me back today) and the bug went away. Mysql definitely need more testing until it reach release..

Reproduced on 5.1.23 on windows, so the bug is platform independent.

Can't repeat with MySQL 5.1-bk and FreeBSD 7.0 64bit and will test with released version to make sure that this is fixed. Also I will test with maria version to make sure that our reporter here didn't download maria by accident.

Many thanks for trusting MySQL.
Unfortunately I can't repeat this behaviour by using your tests.
I tested with our source tree and also I tested with our newest version MySQL 5.1.23-rc.

My system is FreeBSD 7.0 amd64.

I installed MySQL by compiling from source and not using ports.

I ran into this problem as well on a Centos 5 box using MySQL 5.1.23, Linux kernel 2.6.18 x86_64. We were using 5.1.22 (tgz downloaded from website), and we had a particular query that looked like this:

select * from my_table where is_live = true order by date desc limit 10

which was returning results correctly. However, when we upgraded to 5.1.23 (also using the tgz download available from mysql.com site), the query was ordered by the primary key ID, not by date. 

Curiously, when I removed the limit from the query like this on 5.1.23:

select * from my_table where is_live = true order by date desc

the query was ordered properly. Also, this ordered correctly on 5.1.23:

select * from my_table order by date desc limit 10

(though obviously not fully correct without "is_live" clause).

I then dropped an index that we had on the "is_live" column:

alter table my_table drop index idx_is_live;

and ran the original query:

select * from my_table where is_live = true order by date desc limit 10

and the results were correct! Still, on 5.1.22 with an index on is_live, the query ran correctly. I'm wondering if MySQL changed the way it follows its query plan. 

I'll try and upload some files that will help reproduce this problem.

I am having a simliar issue with 5.1.23 from the ports on FreeBSD 7-STABLE. I'm running dbmail and finding that a large result set with a order by is returning no rows, but removing the order by results in 17000+ rows.

mysql> SELECT seen_flag, answered_flag, deleted_flag, flagged_flag, draft_flag, recent_flag, DATE_FORMAT(internal_date, '%Y-%m-%d %T'), rfcsize, message_idnr FROM dbmail_messages msg, dbmail_physmessage pm WHERE pm.id = msg.physmessage_id AND message_idnr BETWEEN 1 AND 1008512 AND mailbox_idnr = 2043 AND status IN (0,1,2) ORDER BY message_idnr ASC;
Empty set (0.00 sec)

mysql> SELECT seen_flag, answered_flag, deleted_flag, flagged_flag, draft_flag, recent_flag, DATE_FORMAT(internal_date, '%Y-%m-%d %T'), rfcsize, message_idnr FROM dbmail_messages msg, dbmail_physmessage pm WHERE pm.id = msg.physmessage_id AND message_idnr BETWEEN 1 AND 1008512 AND mailbox_idnr = 2043 AND status IN (0,1,2);
*** Result Rows Not Shown In This Comment but are present***
17729 rows in set (0.02 sec)

I hope a patch is found and put into the ports tree soon before too many people upgrade their servers.

-Jon

Thank you all for the feedback.

But we still can not repeat the problem on our side.

All reporters who meet the problem with binaries provided by MySQL please:

- indicate accurate package you use (file name)
- provide dump and query we can repeat the problem with

Sveta Smirnova, did you try with this http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-essential-5.1.23-rc-win32.msi/from/pick with mine "testings.messages_testing_small.sql.gz" ?

"[15 Mar 19:26] Nicolae Namolovan

Reproduced on 5.1.23 on windows, so the bug is platform independent."

Thank you for the feedback.

I finally could repeat first part of the problem on my side with indicated version, but could not with current development sources. Please wait next release.