MySQL Bugs: #21559: Sort with SQL_CALC_FOUND_ROWS SQL_BIG

Bug #21559	Sort with SQL_CALC_FOUND_ROWS SQL_BIG_RESULT DISTINCT
Submitted:	10 Aug 2006 7:16	Modified:	6 Sep 2006 14:32
Reporter:	long ahlong	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.21, 5.0 BK, 5.1BK	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Sort error

Description:
Sql string 1:
SELECT SQL_CALC_FOUND_ROWS SQL_BIG_RESULT DISTINCT * 
FROM `tb_game` 
WHERE 1 
ORDER BY `db_start` ASC 
LIMIT 0 , 30

it can not sort in 4.1.21,but can sort in 4.1.20

if i use this:
SELECT * 
FROM `tb_game` 
WHERE 1 
ORDER BY `db_start` ASC 
LIMIT 0 , 30

it can sort

How to repeat:
email or online

Thank you for the report.

Please, provide output of SHOW CREATE TABLE tb_game; statement

CREATE TABLE `tb_game` (
  `ID` bigint(20) NOT NULL auto_increment,
  `db_hostID` bigint(20) NOT NULL default '0',
  `db_awayID` bigint(20) NOT NULL default '0',
  `db_start` datetime default '0000-00-00 00:00:00',
  `db_end` char(1) default '0',
  PRIMARY KEY  (`ID`),
  KEY `db_start` (`db_start`),
  KEY `db_hostID` (`db_hostID`),
  KEY `db_awayID` (`db_awayID`)
  ) ENGINE=MyISAM DEFAULT CHARSET=gbk

Verified as described on Linux using last 4.1 and 5.0 BK sources.

You can use below php script to generate test data:

<?php

$t = array();

for ($i =1; $i <= 31; $i ++)
	$t[] = "insert into tb_game set db_start='2006-08-$i 00-00-00';";

shuffle($t);

for ($i =0; $i < 31; $i ++)
	echo $t[$i] . "\n";

?>

The test sql string is :

SELECT SQL_CALC_FOUND_ROWS SQL_BIG_RESULT DISTINCT * 
FROM `tb_game` 
WHERE 1 
ORDER BY `db_start` ASC 
LIMIT 0 , 30

Test DB Server:
1,Linux (AMD64 / Intel EM64T) Max 4.1.21 (http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-4.1/mysql-max-4.1.21-unknown-linux-gnu-x86_...) 
The sql can not sort on this server;

2,Linux (x86)
no error

wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-4.1/mysql-4.1.21.tar.gz
tar -zxvf mysql-4.1.21.tar.gz
CC=gcc CFLAGS="-O3 -m64 -fno-omit-frame-pointer" CXX=gcc CXXFLAGS="-O3 -m64 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/mysql --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --with-innodb
make;make test;make install;

then i run the sql string,it can not sort;
it can sort in 4.1.20

Notice this is flagged as non-critical. This has serious implications for anyone using the 'DISTINCT' and 'ORDER BY' clause to retrieve records in an expected order. If the records aren't in the order expected, and actions are taken based on the expected order, the data can be rendered invalid and useless with no hope of recovery.

Don't you think it should be raised in severity?

I can confirm this bug as well.

MySQL version: mysql  Ver 14.7 Distrib 4.1.21, for pc-linux-gnu (x86_64) using readline 5.1
OS: Linux jalapeno 2.6.14-hardened-r8 #1 SMP Wed Jun 21 15:39:18 CEST 2006 x86_64 Intel(R) Xeon(TM) CPU 2.80GHz GNU/Linux

Our Order-Management Software was broken after upgrade to 4.1.21. Orders where displayd unorderd, and some functions (Copying orders,..) where broken,..
This leaded to logical data corruptions, and we needed to downgrade to 4.1.20 and recover from a backup.

Our Bugtracking Software Mantis was broken, too. Ordering of the Reports was broken.
(http://www.mantisbt.org/)

Mike, thank you for the comment.

But there is not problem neither with

SELECT DISTINCT * 
FROM `tb_game` 
WHERE 1 
ORDER BY `db_start` ASC 
LIMIT 0 , 30;

nor with

SELECT SQL_CALC_FOUND_ROWS SQL_BIG_RESULT * 
FROM `tb_game` 
WHERE 1 
GROUP BY `db_start`
ORDER BY `db_start` ASC 
LIMIT 0 , 30

To be accurate problem only happens with

SELECT SQL_CALC_FOUND_ROWS DISTINCT *
FROM `tb_game`
WHERE 1
ORDER BY `db_start` ASC
LIMIT 0 , 30;

statement.

Hi Sveta -

I'm not sure what the difference is between the two statements you posted, but to be specific, we're seeing the problme with a statement such as:
SELECT DISTINCT t.field1, t.field2, t.field3 
FROM tablename t
WHERE t.field3 = '2' 
ORDER BY t.field2 ASC

mike

Mike, your query works fine for me.

Anyway it is other problem, so if you know how to repeat the issue, fill separate bug report.

Hi Sveta -

I'm unclear on why you are saying this is different. You have a post above ackowledging that this problem exists, and yet you keep saying the example I posted is fine. Can you describe why my example is different and I will gladly file a separate bug.

In any case, this is happening now on every installed version of Moodle using MySQL that has updated the MySQL version to 4.1.21. This did not happen in any earlier versions of MySQL. The common element is the version 4.1.21.

mike

Here is a specific example of what is happening, using a real table of data:

Table Info:
-- phpMyAdmin SQL Dump
-- version 2.7.0-pl2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Aug 21, 2006 at 09:52 AM
-- Server version: 4.1.21
-- PHP Version: 5.1.2
-- 
-- Database: `moodle`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `mdl_course`
-- 

CREATE TABLE `mdl_course` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `category` int(10) unsigned NOT NULL default '0',
  `sortorder` int(10) unsigned NOT NULL default '0',
  `password` varchar(50) NOT NULL default '',
  `fullname` varchar(254) NOT NULL default '',
  `shortname` varchar(15) NOT NULL default '',
  `idnumber` varchar(100) NOT NULL default '',
  `summary` text NOT NULL,
  `format` varchar(10) NOT NULL default 'topics',
  `showgrades` smallint(2) unsigned NOT NULL default '1',
  `modinfo` longtext NOT NULL,
  `newsitems` smallint(5) unsigned NOT NULL default '1',
  `teacher` varchar(100) NOT NULL default 'Teacher',
  `teachers` varchar(100) NOT NULL default 'Teachers',
  `student` varchar(100) NOT NULL default 'Student',
  `students` varchar(100) NOT NULL default 'Students',
  `guest` tinyint(2) unsigned NOT NULL default '0',
  `startdate` int(10) unsigned NOT NULL default '0',
  `enrolperiod` int(10) unsigned NOT NULL default '0',
  `numsections` smallint(5) unsigned NOT NULL default '1',
  `marker` int(10) unsigned NOT NULL default '0',
  `maxbytes` int(10) unsigned NOT NULL default '0',
  `showreports` int(4) unsigned NOT NULL default '0',
  `visible` int(1) unsigned NOT NULL default '1',
  `hiddensections` int(2) unsigned NOT NULL default '0',
  `groupmode` int(4) unsigned NOT NULL default '0',
  `groupmodeforce` int(4) unsigned NOT NULL default '0',
  `lang` varchar(10) NOT NULL default '',
  `theme` varchar(50) NOT NULL default '',
  `cost` varchar(10) NOT NULL default '',
  `timecreated` int(10) unsigned NOT NULL default '0',
  `timemodified` int(10) unsigned NOT NULL default '0',
  `metacourse` int(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `category` (`category`),
  KEY `idnumber` (`idnumber`),
  KEY `shortname` (`shortname`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

-- 
-- Dumping data for table `mdl_course`
-- 

INSERT INTO `mdl_course` VALUES (1, 0, 1000, '', 'Moodle Test', 'mt', '', '', 'site', 1, '', 3, 'Teacher', 'Teachers', 'Student', 'Students', 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, '', '', '', 0, 1156167771, 0);
INSERT INTO `mdl_course` VALUES (2, 1, 4000, '', 'Course One', 'CF101', '', '\r\nWrite a concise and interesting paragraph here that explains what this course is about', 'weeks', 1, '', 5, 'Teacher', 'Teachers', 'Student', 'Students', 0, 1156219200, 0, 10, 0, 2097152, 0, 1, 0, 0, 0, '', '', '', 1156167836, 1156167836, 0);
INSERT INTO `mdl_course` VALUES (3, 1, 3998, '', 'Course Two', 'CF102', '', '\r\nWrite a concise and interesting paragraph here that explains what this course is about', 'weeks', 1, '', 5, 'Teacher', 'Teachers', 'Student', 'Students', 0, 1156219200, 0, 10, 0, 2097152, 0, 1, 0, 0, 0, '', '', '', 1156167859, 1156167859, 0);
INSERT INTO `mdl_course` VALUES (4, 1, 3999, '', 'Course Three', 'CF103', '', '\r\nWrite a concise and interesting paragraph here that explains what this course is about', 'weeks', 1, '', 5, 'Teacher', 'Teachers', 'Student', 'Students', 0, 1156219200, 0, 10, 0, 2097152, 0, 1, 0, 0, 0, '', '', '', 1156167880, 1156167880, 0);
INSERT INTO `mdl_course` VALUES (5, 1, 3997, '', 'Course Four', 'CF104', '', '\r\nWrite a concise and interesting paragraph here that explains what this course is about', 'weeks', 1, '', 5, 'Teacher', 'Teachers', 'Student', 'Students', 0, 1156219200, 0, 10, 0, 2097152, 0, 1, 0, 0, 0, '', '', '', 1156167898, 1156167898, 0);

Query to run against it:
SELECT DISTINCT c.id, c.sortorder, c.shortname, c.fullname, c.summary, c.visible, c.teacher, c.guest, c.password
FROM mdl_course c
WHERE c.category = '1'
ORDER BY c.sortorder ASC
LIMIT 0 , 20

Result of this query:
SQL result

Host: localhost
Database: moodle
Generation Time: Aug 21, 2006 at 09:53 AM
Generated by: phpMyAdmin 2.7.0-pl2 / MySQL 4.1.21-community-nt
SQL query: SELECT DISTINCT c.id,c.sortorder,c.shortname,c.fullname,c.summary,c.visible,c.teacher,c.guest,c.password FROM mdl_course c WHERE c.category = '1' ORDER BY c.sortorder ASC LIMIT 0,20;
Rows: 4
id 	sortorder 	shortname 	fullname 	summary 	visible 	teacher 	guest 	password
2 	4000 	CF101 	Course One 	
Write a concise and interesting paragraph here t... 	1 	Teacher 	0 	 
3 	3998 	CF102 	Course Two 	
Write a concise and interesting paragraph here t... 	1 	Teacher 	0 	 
4 	3999 	CF103 	Course Three 	
Write a concise and interesting paragraph here t... 	1 	Teacher 	0 	 
5 	3997 	CF104 	Course Four 	
Write a concise and interesting paragraph here t... 	1 	Teacher 	0 	 

Hope this helps. And if this needs to be a new bug report, please let me know.

mike

Thank you, Mike, for additional information. I have simplified your test case:

CREATE DATABASE IF NOT EXISTS bug21559;

USE bug21559;

DROP TABLE IF EXISTS test2;

CREATE TABLE `test2` (
   `id` int(10) unsigned NOT NULL,
   `sortorder` int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
;

INSERT INTO test2 VALUES(1,2),(5,6),(3,4);

-- wrong order here

SELECT DISTINCT id, sortorder FROM test2 ORDER BY sortorder;

-- correct order with group by

SELECT id, sortorder FROM test2 GROUP BY id, sortorder ORDER BY sortorder;

ALTER TABLE test2 DROP PRIMARY KEY;

-- correct order

SELECT DISTINCT id, sortorder FROM test2 ORDER BY sortorder;

To correct order of values in the original test data is possible to drop primary key too:

mysql> alter table tb_game drop primary key;
Query OK, 31 rows affected (0.10 sec)
Records: 31  Duplicates: 0  Warnings: 0

mysql> SELECT SQL_CALC_FOUND_ROWS DISTINCT * FROM `tb_game` WHERE 1 ORDER BY `db_start` ASC LIMIT 0 , 30;
+----+-----------+-----------+---------------------+--------+
| ID | db_hostID | db_awayID | db_start            | db_end |
+----+-----------+-----------+---------------------+--------+
| 10 |         0 |         0 | 2006-08-01 00:00:00 | 0      |
|  1 |         0 |         0 | 2006-08-02 00:00:00 | 0      |
| 30 |         0 |         0 | 2006-08-03 00:00:00 | 0      |
| 11 |         0 |         0 | 2006-08-04 00:00:00 | 0      |
| 23 |         0 |         0 | 2006-08-05 00:00:00 | 0      |
| 12 |         0 |         0 | 2006-08-06 00:00:00 | 0      |
......

Hi Sveta -

Thanks for your analysis. So, is it a bug then?

You suggest dropping the primary key, which we can't do without breaking the application, or adding a 'GROUP BY' which can be done but it would require updating thousands of installations for us and not sure how many for others.

Do you anticipate this being fixed in MySQL anytime soon?

mike

Hi Mike,

bug has verified 12 days ago. It means it is repeatable bug and developers will fix it.

I do not sugggest you any workaround, I added this comment for developers to clearly identify the problem.

Thanks Sveta -

Does the severity code add to the importance of the bug? If so, I wonder if it shouldn't be increased to something more that 'non-critical'?

mike

This bug is a duplicate of #21456.

In the current development we don't see the reported behavior:

mysql> select * from test2;
+----+-----------+
| id | sortorder |
+----+-----------+
|  1 |         2 |
|  5 |         6 |
|  3 |         4 |
+----+-----------+
3 rows in set (0.01 sec)

mysql> SELECT DISTINCT id, sortorder FROM test2 ORDER BY sortorder;
+----+-----------+
| id | sortorder |
+----+-----------+
|  1 |         2 |
|  3 |         4 |
|  5 |         6 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> SELECT id, sortorder FROM test2 GROUP BY id, sortorder ORDER BY sortorder;
+----+-----------+
| id | sortorder |
+----+-----------+
|  1 |         2 |
|  3 |         4 |
|  5 |         6 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE test2 DROP PRIMARY KEY;
Query OK, 3 rows affected (0.21 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT DISTINCT id, sortorder FROM test2 ORDER BY sortorder;
+----+-----------+
| id | sortorder |
+----+-----------+
|  1 |         2 |
|  3 |         4 |
|  5 |         6 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 4.1.22-debug |
+--------------+
1 row in set (0.00 sec)

We also just recently had this problem on our site http://www.aoemedia.de together with TYPO3, but its solved now, thanks.