CODEWITHSUNDEEP
mysql
Johannes Gerer
Johannes Gerer

Reputation: 25818

Weird sorting bug in MySQL?

my MySQL Server (version: 5.0.51a-3ubuntu5.8) I have the following InnoDB table: 

CREATE TABLE `kontostaende` (
  `id` int(11) NOT NULL auto_increment,
  `zeit` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `kontostand` decimal(8,3) NOT NULL,
  `kontostand_zukunft` decimal(8,3) NOT NULL,
  `konto` int(11) NOT NULL,
  `protokoll` int(11) NOT NULL,
  `bemerkung` text NOT NULL,
  `veraenderung` decimal(8,3) NOT NULL,
  `deleted` tinyint(1) NOT NULL default '0',
  `monat_jahr` int(11) NOT NULL,
  `offen` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `konto` (`konto`),
  KEY `protokoll` (`protokoll`),
  KEY `monat_jahr` (`monat_jahr`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

See http://pastebin.com/8zXh43Zg for sample data.

The following query produces wrong (wrongly sorted results): 

SELECT id,kontostand 
FROM `kontostaende` 
WHERE konto = '1' 
ORDER BY id DESC 
LIMIT 5

It will show rows sorted ascending by id from id=1 to id=7.

If I change the query by doing any of the folloginw changes:

  1. "LIMIT 5" -> "LIMIT 6"
  2. remove column "kontostand" from query
  3. remove "WHERE konto = '1'"

the result will be rows sorted in descending order starting from "id=8".

What is wrong here?

Upvotes: 0

Views: 181

Answers (1)

Frederick Cheung
Frederick Cheung

Reputation: 84114

Sounds like you've hit this bug in mysql, the fix for which was in mysql 5.0.68

Upvotes: 3

Related Questions