Description:
Adding another column to the ORDER BY clause of an single-aggregate query results in duplicate rows being included in the result set.
Removing `col_time_key` from the query below will result in the proper result set
This appears to be a regression of Bug#47280 - strange results from count(*) with order by multiple columns without where/group.
I'll run additional tests to determine the changeset that caused this and will update the bug if I am able to find this information.
This is in 5.0 main and bugteam and not present in 5.1-bugteam
This query:
SELECT MIN( `pk` ) field1
FROM CC
ORDER BY field1 , `col_time_key` ;
Results in this diff from 5.1 results:
/* Diff: */
/* --- /tmp//randgen11137-1272401409-server0.dump 2010-04-27 16:50:09.000000000 -0400
# +++ /tmp//randgen11137-1272401409-server1.dump 2010-04-27 16:50:09.000000000 -0400
# @@ -1 +1,20 @@
# 10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10 */
EXPLAIN plans: Server0 = 5.1, Server1=5.0
/* Query plan Server 0:
# |1|SIMPLE|CC|ALL|||||20|Using temporary|
# */
/* Query plan Server 1:
# |1|SIMPLE|CC|ALL|||||20|Using temporary|
# */
How to repeat:
MTR test case:
#/* Server0: MySQL 5.1.47-debug-log */
#/* Server1: MySQL 5.0.91-debug-log */
#/* Server 0 : SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on' */;
#/* Begin test case for query 0 */
--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
--enable_warnings
CREATE TABLE `CC` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_time_key` time DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_time_key` (`col_time_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,'01:27:35');
INSERT INTO `CC` VALUES (11,'19:48:31');
INSERT INTO `CC` VALUES (12,'00:00:00');
INSERT INTO `CC` VALUES (13,'19:53:05');
INSERT INTO `CC` VALUES (14,'19:18:56');
INSERT INTO `CC` VALUES (15,'10:55:12');
INSERT INTO `CC` VALUES (16,'00:25:00');
INSERT INTO `CC` VALUES (17,'12:35:47');
INSERT INTO `CC` VALUES (18,'19:53:03');
INSERT INTO `CC` VALUES (19,'17:53:30');
INSERT INTO `CC` VALUES (20,'11:35:49');
INSERT INTO `CC` VALUES (21,NULL);
INSERT INTO `CC` VALUES (22,'06:01:40');
INSERT INTO `CC` VALUES (23,'05:45:11');
INSERT INTO `CC` VALUES (24,'00:00:00');
INSERT INTO `CC` VALUES (25,'00:00:00');
INSERT INTO `CC` VALUES (26,'06:11:01');
INSERT INTO `CC` VALUES (27,'13:02:46');
INSERT INTO `CC` VALUES (28,'21:44:25');
INSERT INTO `CC` VALUES (29,'22:43:58');
SELECT MIN( `pk` ) field1
FROM CC
ORDER BY field1 , `col_time_key` ;
/* Query plan Server 0:
# |1|SIMPLE|CC|ALL|||||20|Using temporary|
# */
/* Query plan Server 1:
# |1|SIMPLE|CC|ALL|||||20|Using temporary|
# */
/* Diff: */
/* --- /tmp//randgen11137-1272401409-server0.dump 2010-04-27 16:50:09.000000000 -0400
# +++ /tmp//randgen11137-1272401409-server1.dump 2010-04-27 16:50:09.000000000 -0400
# @@ -1 +1,20 @@
# 10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10 */
DROP TABLE CC;
#/* End of test case for query 0 */
#/* Begin test case for query 1 */
--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
--enable_warnings
CREATE TABLE `CC` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_time_key` time DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_time_key` (`col_time_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,'01:27:35','v');
INSERT INTO `CC` VALUES (11,'19:48:31','r');
INSERT INTO `CC` VALUES (12,'00:00:00','a');
INSERT INTO `CC` VALUES (13,'19:53:05','m');
INSERT INTO `CC` VALUES (14,'19:18:56','y');
INSERT INTO `CC` VALUES (15,'10:55:12','j');
INSERT INTO `CC` VALUES (16,'00:25:00','d');
INSERT INTO `CC` VALUES (17,'12:35:47','z');
INSERT INTO `CC` VALUES (18,'19:53:03','e');
INSERT INTO `CC` VALUES (19,'17:53:30','h');
INSERT INTO `CC` VALUES (20,'11:35:49','b');
INSERT INTO `CC` VALUES (21,NULL,'s');
INSERT INTO `CC` VALUES (22,'06:01:40','e');
INSERT INTO `CC` VALUES (23,'05:45:11','j');
INSERT INTO `CC` VALUES (24,'00:00:00','e');
INSERT INTO `CC` VALUES (25,'00:00:00','f');
INSERT INTO `CC` VALUES (26,'06:11:01','v');
INSERT INTO `CC` VALUES (27,'13:02:46','x');
INSERT INTO `CC` VALUES (28,'21:44:25','m');
INSERT INTO `CC` VALUES (29,'22:43:58','c');
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_time_key` time DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_time_key` (`col_time_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,'11:28:45','w');
INSERT INTO `C` VALUES (2,'20:25:14','m');
INSERT INTO `C` VALUES (3,'13:47:24','m');
INSERT INTO `C` VALUES (4,'19:24:11','k');
INSERT INTO `C` VALUES (5,'15:59:13','r');
INSERT INTO `C` VALUES (6,'00:00:00','t');
INSERT INTO `C` VALUES (7,'15:15:04','j');
INSERT INTO `C` VALUES (8,'11:32:06','u');
INSERT INTO `C` VALUES (9,'18:32:33','h');
INSERT INTO `C` VALUES (10,'15:19:25','o');
INSERT INTO `C` VALUES (11,'19:03:19',NULL);
INSERT INTO `C` VALUES (12,'00:39:46','k');
INSERT INTO `C` VALUES (13,NULL,'e');
INSERT INTO `C` VALUES (14,'00:00:00','n');
INSERT INTO `C` VALUES (15,'13:12:11','t');
INSERT INTO `C` VALUES (16,'04:56:48','c');
INSERT INTO `C` VALUES (17,'19:56:05','m');
INSERT INTO `C` VALUES (18,'19:35:19','y');
INSERT INTO `C` VALUES (19,'05:03:03','f');
INSERT INTO `C` VALUES (20,'18:38:59','d');
SELECT MIN( table1 . `pk` ) AS field1
FROM ( CC AS table1 LEFT OUTER JOIN C AS table2 ON (( table2 .`pk` > table1 . `col_varchar_nokey` ) AND (table2 .`pk` < table1 . `col_varchar_nokey` ) ) )
ORDER BY field1 , table1 . `col_time_key` ;
/* Query plan Server 0:
# |1|SIMPLE|table1|ALL|||||20|Using temporary|
# |1|SIMPLE|table2|index|PRIMARY|PRIMARY|4||20|Using index|
# */
/* Query plan Server 1:
# |1|SIMPLE|table1|ALL|||||20|Using temporary|
# |1|SIMPLE|table2|index|PRIMARY|PRIMARY|4||20|Using index|
# */
/* Diff: */
/* --- /tmp//randgen11137-1272401410-server0.dump 2010-04-27 16:50:10.000000000 -0400
# +++ /tmp//randgen11137-1272401410-server1.dump 2010-04-27 16:50:10.000000000 -0400
# @@ -1 +1,20 @@
# 10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10
# +10 */
DROP TABLE CC;
DROP TABLE C;
#/* End of test case for query 1 */
