MySQL Bugs: #28658: order by ... desc sometimes returns incorrect result

Bug #28658	order by ... desc sometimes returns incorrect result
Submitted:	24 May 2007 15:12	Modified:	1 Jun 2007 19:48
Reporter:	Muyu Guo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.41-community-nt, 4.1	OS:	Any (Windows XP, Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
For a table with 2MM records, if I ran something like
select * from mytable where ticker='AA' and period='ANN' order by dt;
I get about 100 lines of records:
dt           ticker         period          value
2005-03-14   AA             ANN             612.77
2005-03-15   AA             ANN             598.12
............
2007-04-24   AA             ANN             712.20

If I ran the query with "order by desc" like
select * from mytable where ticker='AA' and period='ANN' order by dt DESC;
I get a single line of records:
dt           ticker         period          value
2007-04-24   AA             ANN             712.20
Instead of the prior out put reversed as I expected.

How to repeat:
I tried the following: create an empty table with identical structure as "mytable", and just "insert into newtable select * from mytable ..." in order to have the results I had without "DESC" into it. Then do the same thing with the newtable, the problem is no long there with the new table, in other words, I can't duplicate the problem with a smaller set of original table output.

The problem can be avoided with ignore index:
select * from mytable ignore index (primary) where period='ANN' order by dt DESC

Thank you for the bug report. Your server version 5.0.26 is pretty
older could you please upgrade to latest released version and let
know the result you got. Thanks in advance.

The problem still persists after I upgrade to 5.0.41 the most current version.

I was able to create a table with only 36 records and still able to reproduce the problem. It is on myISAM engine. I can provide the three files (.MYI, .MYD, .FRM) for people to reproduce the sympton. Is there a way I can upload the files to the bug report?

Thank you for the feedback. Please create a dumpy of the table using
the mysqldump tool, then zip the file and attach here using the files
tab, please also print here the results you got and the my.ini file
as well. Thanks in advance.

It turns out if I use mysqldump to get a dump of the table and use the dump to create a new table, the new table will not have the original problem. The problem can be reproduced in two ways on my system:
1. Just copy over the three files  (.myi,.myd,.frm) to another mysql server and running the query still get the problem.
2. First create an empty table with identical structure, then run 
"insert into newtable select * from oldtable where conditions". The newly created table still have the original problem.

So it may be related to certain corruption of the orignal table, however, "check table" is OK and does not give any hints of table corruption.

It turns out if I use mysqldump to get a dump of the table and use the dump to create a new table, the new table will not have the original problem. The problem can be reproduced in two ways on my system:
1. Just copy over the three files  (.myi,.myd,.frm) to another mysql server and running the query still get the problem.
2. First create an empty table with identical structure, then run 
"insert into newtable select * from oldtable where conditions". The newly created table still have the original problem.

So it may be related to certain corruption of the orignal table, however, "check table" is OK and does not give any hints of table corruption. The original table was created since 2005, when the server was still running 4.* version.

Thank you for the feedback.

"1. Just copy over the three files  (.myi,.myd,.frm) to another mysql server and
running the query still get the problem."

I assume the three files are the already corrupted table in this case
isn't the bad result the bug but the table corruption since you said
after a check table the result data is ok.

"2. First create an empty table with identical structure, then run 
"insert into newtable select * from oldtable where conditions". The newly
created table still have the original problem."

It is the oldtable above the corrupted one? or you meant the bug is
when doing insert into ok_empty_table...select..from ok_table the bad
result is present. In short I want to know if the bad result only can be
repeatable in both cases when a corrupted table is involved? 

Thanks in advance.

The bad result only appears when the old table (suspect corrupted) is involved. The old talbe was created long time ago and was being updated on daily basis. After a dump and reload to a new table it is okay.

In all cases the oldtable is the one producing bad results. What I wanted to say was that oldtable appears corrupt, but a "check table" can't tell me it is. In other words I can suspect the tables are corrupt but I can't prove it with "check table".

Thank you for the feedback. Could you please attach the table's files
using a zip file?. Thanks in advance.

Sorry I forgot to ask you which server version was used when the corrupted
table was created. Thanks in advance.

I am uploading the corrupt table to the bug report. To reproduce the bad result query must be run like the following:

mysql> select * from ibesi.summary_debug  where ibes_ticker='@NNT' and periodicity='ANN' and period_end=0703 order by dt ASC ;
+------------+-------------+---------+-------------+------------+-------------------+--------------------+---------------------+
| dt         | ibes_ticker | measure | periodicity | period_end | fcast_period_code | no_ests_raised_4wk | no_ests_lowered_4wk |
+------------+-------------+---------+-------------+------------+-------------------+--------------------+---------------------+
| 2007-04-18 | @NNT        | EPS     | ANN         |       0703 | 1                 |                 12 |                   2 |
| 2007-04-24 | @NNT        | EPS     | ANN         |       0703 | 1                 |                 13 |                   1 |
+------------+-------------+---------+-------------+------------+-------------------+--------------------+---------------------+
2 rows in set (0.00 sec)

mysql> select * from ibesi.summary_debug  where ibes_ticker='@NNT' and periodicity='ANN' and period_end=0703 order by dt DESC ;
+------------+-------------+---------+-------------+------------+-------------------+--------------------+---------------------+
| dt         | ibes_ticker | measure | periodicity | period_end | fcast_period_code | no_ests_raised_4wk | no_ests_lowered_4wk |
+------------+-------------+---------+-------------+------------+-------------------+--------------------+---------------------+
| 2007-04-24 | @NNT        | EPS     | ANN         |       0703 | 1                 |                 13 |                   1 |
+------------+-------------+---------+-------------+------------+-------------------+--------------------+---------------------+
1 row in set (0.00 sec)

The table was generated using some 4.0.* (or perhaps 3.*) version which we no longer have access to.

Thank you for the report.

Verified as described.

Table created as CREATE TABLE LIKE summary_debug has same problems as original one.

Table restored from dump in version 5.0 or 5.1 has no problems.

Table restored from dump in version 4.1 has same bug as original table.

The problem here is with handling leading zeroes with the pre-5.x DECIMAL type in the period_end columns (that is a part of the primary key).
The summary_debug table has values with leading zeroes in the 'period_end' column and doesn't have the ZERO_FILL flag on for it.

Because there's no ZERO_FILL flag for the field when converting the integer value '0703' from the query (summary_debug = 0703) it converts is to '703' and fails to find it in the index.

The table is easily "fixed" by executing the following update :
update summary_debug set period_end = cast(period_end as signed);

There's one additional problem in 4.1 (that causes the dump/restore in 4.1 to preserve the problem instead of fix it as in 5.x) : you can insert such zero-prefixed values in a NUMERIC column that doesn't have ZERO_FILL on by inserting using string values :
create table t1 (a numeric (4,0));
insert into t1 values (0703);
insert into t1 values ('0704');
select * from t1;

That select returns :

703
0704

This demonstrates how the problem persists through dump/restore.

In 5.x (with it's precise NUMERIC type) dump/restore changes the type of the column and no such problem occurs with the new type.
However the table created with a 4.x server still exhibits the same problem (and can be fixed by the same update).

A quick comment - I don't want to open a full bug report as I don't have the corrupt tables any more (fixed them with REPAIR TABLE xxx EXTENDED), but I want the MySQL team to know about this issue, because it might be an indication of something deeper and possible serious.

Yesterday I got the exact same problem: Using ORDER DESC would return ZERO rows, but ORDER ASC and no ORDER would return the correct rows.

Server is "MySQL 5.0.22-Debian_0ubuntu6.06.2-log"

Okay, so far the same thing, but here's the catch:

I've got the MySQL server replicating to TWO other servers, both of them "5.0.24a-Debian_9-log" and both had the exact same problem as the main server.

If I understood correctly, the servers replicate SQL statements, not the data itself, thus, if that is true, a sequence of SQL statements managed to corrupt the table on all 3 servers (and 2 versions of MySQL)... thus I think it is safe to asume that the corruption was caused by the MySQL server, and not a Hard drive crash or other external cause.

Maybe it has been fixed on newer servers, but I figured I had to let you know about this, just in case.