MySQL Bugs: #26962: No rows returned with ORDER BY column DESC

Bug #26962	No rows returned with ORDER BY column DESC
Submitted:	8 Mar 2007 15:57	Modified:	13 Apr 2007 14:13
Reporter:	RAHARINJATO Voara Rado	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.27-standard-log	OS:	Linux (RED HAT ES4)
Assigned to:		CPU Architecture:	Any
Tags:	DESC, order by

Description:
This bug is exactly identical to closed bug 24067 reported by David Levner.

A SQL query with DESC should return some rows but returns none. Omit DESC and
the query returns the correct rows.
Sometimes, it works fine and correct rows are returned.

David Levner have seen this bug with the 5.0.22.He fixed it by uppgrading to 5.0.27.

This bug occurred on my server (version 5.0.22 too) and I upgraded to 5.0.27-standard-log but I still get the same issue on my system.

How to repeat:
# The following query returns zero row

mysql> select * from CPT_MVT where id_cpt=15441 order by date_ope desc;
Empty set (0.00 sec)

# By removing DESC, correct rows are returned

mysql> select date_ope from CPT_MVT where id_cpt=15441 order by date_ope;
+---------------------+
| date_ope            |
+---------------------+
| 2006-12-06 00:00:00 |
| 2006-12-19 00:00:00 |
| 2006-12-21 00:00:00 |
| 2006-12-21 00:00:00 |
| 2007-01-05 00:00:00 |
| 2007-01-10 00:00:00 |
| 2007-01-16 00:00:00 |
| 2007-01-17 00:00:00 |
| 2007-01-19 00:00:00 |
| 2007-01-22 00:00:00 |
| 2007-01-29 00:00:00 |
| 2007-01-31 00:00:00 |
| 2007-02-05 00:00:00 |
| 2007-02-09 00:00:00 |
| 2007-02-12 00:00:00 |
| 2007-02-13 00:00:00 |
| 2007-02-23 00:00:00 |
| 2007-02-26 00:00:00 |
| 2007-02-27 00:00:00 |
| 2007-02-27 00:00:00 |
+---------------------+
20 rows in set (0.00 sec)

# By ignoring index ID_CPT_DATEOPE, correct rows are returned

mysql> select date_ope from CPT_MVT ignore index (ID_CPT_DATEOPE) where id_cpt=15441 order by date_ope desc;
+---------------------+
| date_ope            |
+---------------------+
| 2007-02-27 00:00:00 |
| 2007-02-27 00:00:00 |
| 2007-02-26 00:00:00 |
| 2007-02-23 00:00:00 |
| 2007-02-13 00:00:00 |
| 2007-02-12 00:00:00 |
| 2007-02-09 00:00:00 |
| 2007-02-05 00:00:00 |
| 2007-01-31 00:00:00 |
| 2007-01-29 00:00:00 |
| 2007-01-22 00:00:00 |
| 2007-01-19 00:00:00 |
| 2007-01-17 00:00:00 |
| 2007-01-16 00:00:00 |
| 2007-01-10 00:00:00 |
| 2007-01-05 00:00:00 |
| 2006-12-21 00:00:00 |
| 2006-12-21 00:00:00 |
| 2006-12-19 00:00:00 |
| 2006-12-06 00:00:00 |
+---------------------+
20 rows in set (0.00 sec)

# Here are indexes created for CPT_MVT table

mysql> show index from CPT_MVT;
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| CPT_MVT |          0 | PRIMARY        |            1 | ID_MVT      | A         |      464612 |     NULL | NULL   |      | BTREE      |         |
| CPT_MVT |          1 | ID_DEVISE      |            1 | ID_DEVISE   | A         |           9 |     NULL | NULL   | YES  | BTREE      |         |
| CPT_MVT |          1 | ID_CPT         |            1 | ID_CPT      | A         |        9885 |     NULL | NULL   | YES  | BTREE      |         |
| CPT_MVT |          1 | ID_CPT_DATEOPE |            1 | ID_CPT      | A         |        9885 |     NULL | NULL   | YES  | BTREE      |         |
| CPT_MVT |          1 | ID_CPT_DATEOPE |            2 | DATE_OPE    | A         |      154870 |     NULL | NULL   | YES  | BTREE      |         |
| CPT_MVT |          1 | ID_CPT_DATEVAL |            1 | ID_CPT      | A         |        9885 |     NULL | NULL   | YES  | BTREE      |         |
| CPT_MVT |          1 | ID_CPT_DATEVAL |            2 | DATE_VALEUR | A         |      232306 |     NULL | NULL   | YES  | BTREE      |         |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.00 sec)

# Here is the CPT_MVT table description;

mysql> desc CPT_MVT;
+----------------+---------------+------+-----+---------+----------------+
| Field          | Type          | Null | Key | Default | Extra          |
+----------------+---------------+------+-----+---------+----------------+
| ID_CPT         | int(11)       | YES  | MUL | NULL    |                |
| ID_MVT         | int(11)       | NO   | PRI | NULL    | auto_increment |
| ID_DEVISE      | int(11)       | YES  | MUL | NULL    |                |
| CODE_OPE       | varchar(4)    | YES  |     | NULL    |                |
| CODE_FAMILLE   | varchar(4)    | YES  |     | NULL    |                |
| MNT_DEV        | decimal(28,6) | YES  |     | NULL    |                |
| MNT_EUR        | decimal(28,6) | YES  |     | NULL    |                |
| DATE_OPE       | datetime      | YES  |     | NULL    |                |
| DATE_VALEUR    | datetime      | YES  |     | NULL    |                |
| NUM_ECR        | varchar(7)    | YES  |     | NULL    |                |
| LIBELLE        | varchar(200)  | YES  |     | NULL    |                |
| CODE_EXO       | char(1)       | YES  |     | NULL    |                |
| DEVISE_ORIGINE | char(1)       | YES  |     | NULL    |                |
+----------------+---------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)

mysql>

Thank you for a problem report. Please, send the results of SHOW CREATE TABLE and SHOW TABLE STATUS for that CPT_MVT table.

results of show create table and show status for CPT_MVT table

Attachment: bugs-sql-2.txt (text/plain), 2.29 KiB.

# Results of SHOW CREATE TABLE for CPT_MVT table

-------------------------------------------
TABLE   | Create Table
-------------------------------------------
CPT_MVT | CREATE TABLE `CPT_MVT` (
  `ID_CPT` int(11) default NULL,
  `ID_MVT` int(11) NOT NULL auto_increment,
  `ID_DEVISE` int(11) default NULL,
  `CODE_OPE` varchar(4) default NULL,
  `CODE_FAMILLE` varchar(4) default NULL,
  `MNT_DEV` decimal(28,6) default NULL,
  `MNT_EUR` decimal(28,6) default NULL,
  `DATE_OPE` datetime default NULL,
  `DATE_VALEUR` datetime default NULL,
  `NUM_ECR` varchar(7) default NULL,
  `LIBELLE` varchar(200) default NULL,
  `CODE_EXO` char(1) default NULL,
  `DEVISE_ORIGINE` char(1) default NULL,
  PRIMARY KEY  (`ID_MVT`),
  KEY `ID_DEVISE` (`ID_DEVISE`),
  KEY `ID_CPT` (`ID_CPT`),
  KEY `ID_CPT_DATEOPE` (`ID_CPT`,`DATE_OPE`),
  KEY `ID_CPT_DATEVAL` USING BTREE (`ID_CPT`,`DATE_VALEUR`)
) ENGINE=MyISAM AUTO_INCREMENT=6414155 DEFAULT CHARSET=latin1 

# Results of SHOW TABLE STATUS from the database for CPT_MVT table 

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Name              | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CPT_MVT           | MyISAM |      10 | Dynamic    | 463996 |            121 |    57774296 |   281474976710655 |     55699456 |   1403256 |        6414155 | 2006-10-03 13:58:47 | 2007-03-09 04:47:05 | 2006-10-03 13:59:11 | latin1_swedish_ci |     NULL |                |         |         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sorry for the results format ... you can see the attached file to read these results on right format.

Thanks in advance.

Please, try to repeat with a newer version, 5.0.37, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".