Bug #2865 Error with Order By and Limit 1

• Submitted: 18 Feb 2004 5:43
• Modified: 18 Feb 2004 8:09
• Reporter: Andre Justus
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.1
• OS: Linux (Linux Debian 3)
• CPU Architecture: Any

[18 Feb 2004 5:43] Andre Justus

Description:
Mysql puts out wrong data using Order By and Limit 1

How to repeat:
CREATE TABLE `arenenregulator` (
  `arena` tinyint(3) unsigned NOT NULL default '0',
  `startwert` smallint(5) unsigned NOT NULL default '0',
  `offen` tinyint(3) unsigned NOT NULL default '0'
) TYPE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `arenenregulator` VALUES (1, 0, 6);
INSERT INTO `arenenregulator` VALUES (2, 0, 0);
INSERT INTO `arenenregulator` VALUES (3, 0, 0);
INSERT INTO `arenenregulator` VALUES (4, 0, 0);
INSERT INTO `arenenregulator` VALUES (5, 0, 0);
INSERT INTO `arenenregulator` VALUES (6, 0, 0);

SELECT * 
FROM `arenenregulator` 
WHERE offen <6
ORDER BY arena ASC 
LIMIT 1 

Output : 
arena   startwert  offen  
    0           0      4 

With no Limit or Limit=2 the correct Data has been put out.
Leaving out ORDER BY ... the output is correc, too
Leaving Out ASC or replacing it with DESC, the same wrong output described above occures.

[18 Feb 2004 8:09] Alexander Keremidarski

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

mysql> SELECT *
    -> FROM `arenenregulator`
    -> WHERE offen <6
    -> ORDER BY arena ASC
    -> LIMIT 1;
+-------+-----------+-------+
| arena | startwert | offen |
+-------+-----------+-------+
|     2 |         0 |     0 |
+-------+-----------+-------+
1 row in set (0.00 sec)
 
mysql> SELECT *
    -> FROM `arenenregulator`
    -> WHERE offen <6
    -> ORDER BY arena ASC;
+-------+-----------+-------+
| arena | startwert | offen |
+-------+-----------+-------+
|     2 |         0 |     0 |
|     3 |         0 |     0 |
|     4 |         0 |     0 |
|     5 |         0 |     0 |
|     6 |         0 |     0 |
+-------+-----------+-------+
5 rows in set (0.00 sec)

mysql> SELECT *
    -> FROM `arenenregulator`
    -> WHERE offen <6
    -> ORDER BY arena DESC;
+-------+-----------+-------+
| arena | startwert | offen |
+-------+-----------+-------+
|     6 |         0 |     0 |
|     5 |         0 |     0 |
|     4 |         0 |     0 |
|     3 |         0 |     0 |
|     2 |         0 |     0 |
+-------+-----------+-------+
5 rows in set (0.00 sec)

Everything looks correct.