Bug #3563 Result buffer not flush after previous query with ORDER BY clause

• Submitted: 26 Apr 2004 5:13
• Modified: 12 May 2005 14:17
• Reporter: Mike Izydorski
• Status: Can't repeat
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.0.18
• OS: Windows (Windows XP pro)
• CPU Architecture: Any

[26 Apr 2004 5:13] Mike Izydorski

Description:
If query was executed a feew times, and with the same database (without any modifications) the result set increases unexpectedly.

How to repeat:
In my case this occurs when I want to select data from one table, connected to other and with ORDER given by this other table:

 select d.* from user u, db d where u.Host=d.Host order by u.User;

each time number of returned rows increases about real number of records that should be returned.

Results vary when you use:
 select * from user u, db d where u.Host=d.Host order by u.User;

[26 Apr 2004 5:16] Mike Izydorski

The example was executed on 'mysql' database;

[26 Apr 2004 5:31] Mike Izydorski

This occurs on various .exe server files: mysqld-nt, mysqld-max-nt and mysqld-max. I didn't check out others.

reset/flush query cache

doesn't help with this.

[26 Apr 2004 5:48] Mike Izydorski

If you delete ORDER BY ... part of query, everything is ok.

[26 Apr 2004 9:16] MySQL Verification Team

I wasn't able for to repeat your query sample using the mysql
database, then I am assuming that some my.ini key set does it
happens, like the bug:

http://bugs.mysql.com/bug.php?id=2858

Could you please can provide your my.ini file sets.

Thanks

[27 Apr 2004 6:00] Mike Izydorski

I changed only 'lover_case_table_name' Here you are:

#This File was made using the WinMySQLAdmin 1.4 Tool
#2004-03-09 16:23:56

#Uncomment or Add only the keys that you know how works.
#Read the MySQL Manual for instructions

[mysqld]
basedir=C:/Program Files/MySQL
#bind-address=168.92.229.66
datadir=C:/Program Files/MySQL/data
#language=C:/Program Files/MySQL/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
#set-variable=key_buffer=16M
set-variable=lower_case_table_name=0
[WinMySQLadmin]
Server=C:/Program Files/mysql/bin/mysqld-nt.exe
user=root
password=root
QueryInterval=10

[27 Apr 2004 7:56] Sergei Golubchik

then it must be the same bug as 2858.
It should be fixed in 4.0.19 (fixed in the source tree already)

[27 Apr 2004 18:08] MySQL Verification Team

I tested again with version 4.0.18 and still I wasn't able for to repeat
then I can't assume it is the same case as bug 2858. For to eliminate
that doubt I would appreciate if you can give for us: table schema,
some insert commands and the query statement different than the mysql
database example. I will test against 4.0.18 and 4.0.19.

Thank you in advance for your help.

[14 Jul 2004 18:03] Steven Hood

I have just recently experienced the same type of bug.  I looked at #2858 also, and it was not quite what is happening with my queries.

I am using a join (implicit) with an ORDER BY, and depending on which column the ORDER BY uses determines the behavior I see in the result set.

I have attached the output and the dump so that these queries can be tested.

[14 Jul 2004 18:06] Steven Hood

-- MySQL dump 9.10
--
-- Host: localhost    Database: demo
-- ------------------------------------------------------
-- Server version	4.0.18-max-debug

--
-- Table structure for table `classlist2`
--

CREATE TABLE classlist2 (
  id int(10) unsigned NOT NULL auto_increment,
  name varchar(255) default NULL,
  courseNumber int(10) unsigned default NULL,
  presentation enum('Classroom','Online','Classroom or Online') NOT NULL default 'Classroom',
  PRIMARY KEY  (id)
) TYPE=MyISAM;

--
-- Dumping data for table `classlist2`
--

INSERT INTO classlist2 VALUES (27,'Course 7',16532,'Classroom');
INSERT INTO classlist2 VALUES (26,'Course 1',0,'Online');
INSERT INTO classlist2 VALUES (30,'Course 2',2,'Online');
INSERT INTO classlist2 VALUES (31,'Course 3',0,'Classroom');
INSERT INTO classlist2 VALUES (37,'Course 4',5,'Classroom');
INSERT INTO classlist2 VALUES (38,'Course 5',0,'Classroom');
INSERT INTO classlist2 VALUES (39,'Course 6',3,'Classroom');
INSERT INTO classlist2 VALUES (40,'Course 8',0,'Classroom');

--
-- Table structure for table `classfiles2`
--

CREATE TABLE classfiles2 (
  id int(10) unsigned NOT NULL auto_increment,
  classID int(11) NOT NULL default '0',
  name varchar(255) default NULL,
  link varchar(255) default NULL,
  description text,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

--
-- Dumping data for table `classfiles2`
--

INSERT INTO classfiles2 VALUES (66,26,'file1.ppt','/documents/file1.ppt','');
INSERT INTO classfiles2 VALUES (67,31,'file2.ppt','/documents/file2.ppt','');
INSERT INTO classfiles2 VALUES (68,31,'file3.doc','/documents/file3.doc','');
INSERT INTO classfiles2 VALUES (78,40,'file3.ppt','/documents/file3.ppt','');
INSERT INTO classfiles2 VALUES (79,40,'file4.xls','/documents/file4.xls','');
INSERT INTO classfiles2 VALUES (80,40,'file5.ppt','/documents/file5.ppt','');

--------------
Output
--------------

mysql> describe classlist2;
+--------------+--------------------------------------------------+------+-----+-----------+----------------+
| Field        | Type                                             | Null | Key | Default   | Extra          |
+--------------+--------------------------------------------------+------+-----+-----------+----------------+
| id           | int(10) unsigned                                 |      | PRI | NULL      | auto_increment |
| name         | varchar(255)                                     | YES  |     | NULL      |                |
| courseNumber | int(10) unsigned                                 | YES  |     | NULL      |                |
| presentation | enum('Classroom','Online','Classroom or Online') |      |     | Classroom |                |
+--------------+--------------------------------------------------+------+-----+-----------+----------------+
4 rows in set (0.00 sec)

mysql> describe classfiles2;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned |      | PRI | NULL    | auto_increment |
| classID     | int(11)          |      |     | 0       |                |
| name        | varchar(255)     | YES  |     | NULL    |                |
| link        | varchar(255)     | YES  |     | NULL    |                |
| description | text             | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid order by classlist2.name;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid order by classlist2.name;
+----------+
| COUNT(*) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid order by classlist2.name;
+----------+
| COUNT(*) |
+----------+
|       18 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid order by classfiles2.name;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid order by classfiles2.name;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid order by classfiles2.name;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.01 sec)

[14 Jul 2004 18:07] Steven Hood

-- MySQL dump 9.10
--
-- Host: localhost    Database: demo
-- ------------------------------------------------------
-- Server version	4.0.18-max-debug

--
-- Table structure for table `classlist2`
--

CREATE TABLE classlist2 (
  id int(10) unsigned NOT NULL auto_increment,
  name varchar(255) default NULL,
  courseNumber int(10) unsigned default NULL,
  presentation enum('Classroom','Online','Classroom or Online') NOT NULL default 'Classroom',
  PRIMARY KEY  (id)
) TYPE=MyISAM;

--
-- Dumping data for table `classlist2`
--

INSERT INTO classlist2 VALUES (27,'Course 7',16532,'Classroom');
INSERT INTO classlist2 VALUES (26,'Course 1',0,'Online');
INSERT INTO classlist2 VALUES (30,'Course 2',2,'Online');
INSERT INTO classlist2 VALUES (31,'Course 3',0,'Classroom');
INSERT INTO classlist2 VALUES (37,'Course 4',5,'Classroom');
INSERT INTO classlist2 VALUES (38,'Course 5',0,'Classroom');
INSERT INTO classlist2 VALUES (39,'Course 6',3,'Classroom');
INSERT INTO classlist2 VALUES (40,'Course 8',0,'Classroom');

--
-- Table structure for table `classfiles2`
--

CREATE TABLE classfiles2 (
  id int(10) unsigned NOT NULL auto_increment,
  classID int(11) NOT NULL default '0',
  name varchar(255) default NULL,
  link varchar(255) default NULL,
  description text,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

--
-- Dumping data for table `classfiles2`
--

INSERT INTO classfiles2 VALUES (66,26,'file1.ppt','/documents/file1.ppt','');
INSERT INTO classfiles2 VALUES (67,31,'file2.ppt','/documents/file2.ppt','');
INSERT INTO classfiles2 VALUES (68,31,'file3.doc','/documents/file3.doc','');
INSERT INTO classfiles2 VALUES (78,40,'file3.ppt','/documents/file3.ppt','');
INSERT INTO classfiles2 VALUES (79,40,'file4.xls','/documents/file4.xls','');
INSERT INTO classfiles2 VALUES (80,40,'file5.ppt','/documents/file5.ppt','');

--------------
Output
--------------

mysql> describe classlist2;
+--------------+--------------------------------------------------+------+-----+-----------+----------------+
| Field        | Type                                             | Null | Key | Default   | Extra          |
+--------------+--------------------------------------------------+------+-----+-----------+----------------+
| id           | int(10) unsigned                                 |      | PRI | NULL      | auto_increment |
| name         | varchar(255)                                     | YES  |     | NULL      |                |
| courseNumber | int(10) unsigned                                 | YES  |     | NULL      |                |
| presentation | enum('Classroom','Online','Classroom or Online') |      |     | Classroom |                |
+--------------+--------------------------------------------------+------+-----+-----------+----------------+
4 rows in set (0.00 sec)

mysql> describe classfiles2;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned |      | PRI | NULL    | auto_increment |
| classID     | int(11)          |      |     | 0       |                |
| name        | varchar(255)     | YES  |     | NULL    |                |
| link        | varchar(255)     | YES  |     | NULL    |                |
| description | text             | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid order by classlist2.name;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid order by classlist2.name;
+----------+
| COUNT(*) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid order by classlist2.name;
+----------+
| COUNT(*) |
+----------+
|       18 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid order by classfiles2.name;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid order by classfiles2.name;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(*) from classlist2, classfiles2 where classlist2.id=classfiles2.classid order by classfiles2.name;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.01 sec)

As you can see, if I ORDER BY the left table, it has a problem of adding to the previous result set, but if I use the right table, there isn't a problem.  Also, without the ORDER BY, there is no problem.  The error also happens even if the queries are mixed together in any order.

[18 Apr 2005 16:14] Trevor Marshall

I see the same problem with MySQL 4.0.22 and 4.0.24 on Windows 2000. The problem showed with PHPBB in the last two weeks after running for some months with no problems.  No changes to MySQL that I was aware of.  I have extracted a pair of tables and some data that show the problem as a minimal set.

I've tried a clean install of MySQL 4.0.24 on a different Windows PC, and then ran in the following file using

mysql -u root -p < tmtest3.sql

Here's the contents of tmtest3.sql:

---------------------------------%<------------------------------------

--
DROP DATABASE IF EXISTS `tmtest`;
CREATE DATABASE `tmtest`;
USE tmtest;

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

-- 
-- Table structure for table `phpbb_sessions`
-- 

DROP TABLE IF EXISTS `phpbb_sessions`;
CREATE TABLE IF NOT EXISTS `phpbb_sessions` (
  `session_id` char(32) NOT NULL default '',
  `session_user_id` mediumint(8) NOT NULL default '0',
  `session_start` int(11) NOT NULL default '0',
  `session_time` int(11) NOT NULL default '0',
  `session_ip` char(8) NOT NULL default '',
  `session_page` int(11) NOT NULL default '0',
  `session_logged_in` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`session_id`),
  KEY `session_user_id` (`session_user_id`),
  KEY `session_id_ip_user_id` (`session_id`,`session_ip`,`session_user_id`)
) TYPE=MyISAM;

-- 
-- Dumping data for table `phpbb_sessions`
-- 

INSERT INTO `phpbb_sessions` VALUES ('8e245ee4780bbcc7e93107a95cc062ad', -1, 1113297782, 1113299824, 'ac140f66', 0, 0);
INSERT INTO `phpbb_sessions` VALUES ('cae531234975c0955d45e65feeb744f1', 10, 1113299233, 1113299294, 'c01b79d3', 0, 1);
INSERT INTO `phpbb_sessions` VALUES ('d527a3c78b78bb8fa745f9fc1c68725f', -1, 1113297536, 1113297536, 'c01b79d3', 0, 0);
INSERT INTO `phpbb_sessions` VALUES ('3934ff002b70828e616baeb1026efb99', -1, 1113297529, 1113297529, 'c01b79d3', 0, 0);
INSERT INTO `phpbb_sessions` VALUES ('4be3602ee497e87ba499f590bdd2a580', -1, 1113301675, 1113301675, 'c01b79d3', 0, 0);

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

-- 
-- Table structure for table `phpbb_users`
-- 

DROP TABLE IF EXISTS `phpbb_users`;
CREATE TABLE IF NOT EXISTS `phpbb_users` (
  `user_id` mediumint(8) NOT NULL default '0',
  `user_active` tinyint(1) default '1',
  `username` varchar(25) NOT NULL default '',
  `user_password` varchar(32) NOT NULL default '',
  `user_session_time` int(11) NOT NULL default '0',
  `user_session_page` smallint(5) NOT NULL default '0',
  `user_lastvisit` int(11) NOT NULL default '0',
  `user_regdate` int(11) NOT NULL default '0',
  `user_level` tinyint(4) default NULL,
  `user_posts` mediumint(8) unsigned NOT NULL default '0',
  `user_timezone` decimal(5,2) NOT NULL default '0.00',
  `user_style` tinyint(4) default NULL,
  `user_lang` varchar(255) default NULL,
  `user_dateformat` varchar(14) NOT NULL default 'd M Y H:i',
  `user_new_privmsg` smallint(5) unsigned NOT NULL default '0',
  `user_unread_privmsg` smallint(5) unsigned NOT NULL default '0',
  `user_last_privmsg` int(11) NOT NULL default '0',
  `user_emailtime` int(11) default NULL,
  `user_viewemail` tinyint(1) default NULL,
  `user_attachsig` tinyint(1) default NULL,
  `user_allowhtml` tinyint(1) default '1',
  `user_allowbbcode` tinyint(1) default '1',
  `user_allowsmile` tinyint(1) default '1',
  `user_allowavatar` tinyint(1) NOT NULL default '1',
  `user_allow_pm` tinyint(1) NOT NULL default '1',
  `user_allow_viewonline` tinyint(1) NOT NULL default '1',
  `user_notify` tinyint(1) NOT NULL default '1',
  `user_notify_pm` tinyint(1) NOT NULL default '0',
  `user_popup_pm` tinyint(1) NOT NULL default '0',
  `user_rank` int(11) default NULL,
  `user_avatar` varchar(100) default NULL,
  `user_avatar_type` tinyint(4) NOT NULL default '0',
  `user_email` varchar(255) default NULL,
  `user_icq` varchar(15) default NULL,
  `user_website` varchar(100) default NULL,
  `user_from` varchar(100) default NULL,
  `user_sig` text,
  `user_sig_bbcode_uid` varchar(10) default NULL,
  `user_aim` varchar(255) default NULL,
  `user_yim` varchar(255) default NULL,
  `user_msnm` varchar(255) default NULL,
  `user_occ` varchar(100) default NULL,
  `user_interests` varchar(255) default NULL,
  `user_actkey` varchar(32) default NULL,
  `user_newpasswd` varchar(32) default NULL,
  PRIMARY KEY  (`user_id`),
  KEY `user_session_time` (`user_session_time`)
) TYPE=MyISAM;

-- 
-- Dumping data for table `phpbb_users`
-- 

INSERT INTO `phpbb_users` VALUES (-1, 0, 'Anonymous', '', 0, 0, 0, 1099926690, 0, 5, 0.00, NULL, '', '', 0, 0, 0, NULL, 0, 0, 0, 1, 1, 1, 0, 1, 0, 1, 0, NULL, '', 0, '', '', '', '', '', NULL, '', '', '', '', '', '', '');
INSERT INTO `phpbb_users` VALUES (2, 1, 'administrator', '2b26d6ea548db4c9c7eda2db6e494119', 1113300886, 0, 1113299180, 1099926690, 1, 1, 0.00, 1, 'english', 'd M Y h:i a', 0, 0, 1113228988, NULL, 1, 0, 0, 1, 1, 1, 1, 1, 0, 1, 1, 1, '', 0, 'ss@cc.c', '', '', '', '', '', '', '', '', '', '', '', '');
INSERT INTO `phpbb_users` VALUES (10, 1, 'trevorm', '9dd4e461268c8034f5c8564e155c67a6', 1113299294, 0, 1113235227, 1100014577, 0, 13, 0.00, 1, 'english', 'D M d, Y g:i a', 0, 0, 1107439856, NULL, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 1, NULL, 'Avatar_Pack_1/102.gif', 3, '[e-mail]', '', '', '', '', '', '', '', '', '', '', '', NULL);

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

--
-- Query with no ORDER BY - is OK
--

SELECT u.user_id, s.session_user_id
FROM phpbb_users u, phpbb_sessions s
WHERE u.user_id = s.session_user_id;

--
-- Repeat
--

SELECT u.user_id, s.session_user_id
FROM phpbb_users u, phpbb_sessions s
WHERE u.user_id = s.session_user_id;

--
-- Repeat
--

SELECT u.user_id, s.session_user_id
FROM phpbb_users u, phpbb_sessions s
WHERE u.user_id = s.session_user_id;

--
-- Repeat
--

SELECT u.user_id, s.session_user_id
FROM phpbb_users u, phpbb_sessions s
WHERE u.user_id = s.session_user_id;

--
-- Repeat
--

SELECT u.user_id, s.session_user_id
FROM phpbb_users u, phpbb_sessions s
WHERE u.user_id = s.session_user_id;

--
-- Repeat with ORDER BY
--

SELECT u.user_id, s.session_user_id
FROM phpbb_users u, phpbb_sessions s
WHERE u.user_id = s.session_user_id
ORDER BY u.user_id;

--
-- Repeat with ORDER BY
--

SELECT u.user_id, s.session_user_id
FROM phpbb_users u, phpbb_sessions s
WHERE u.user_id = s.session_user_id
ORDER BY u.user_id;

--
-- Repeat with ORDER BY
--

SELECT u.user_id, s.session_user_id
FROM phpbb_users u, phpbb_sessions s
WHERE u.user_id = s.session_user_id
ORDER BY u.user_id;

--
-- Repeat with ORDER BY
--

SELECT u.user_id, s.session_user_id
FROM phpbb_users u, phpbb_sessions s
WHERE u.user_id = s.session_user_id
ORDER BY u.user_id;

--
-- Repeat with ORDER BY
--

SELECT u.user_id, s.session_user_id
FROM phpbb_users u, phpbb_sessions s
WHERE u.user_id = s.session_user_id
ORDER BY u.user_id;

---------------------------------%<------------------------------------

All comments welcome, this is driving me mad.

[7 May 2005 10:47] Hartmut Holzgraefe

None of these testcases is reproducable on Linux ...

[12 May 2005 14:17] MySQL Verification Team

Still can't repeat on Windows with 4.0.24.