Bug #46056 Duplicate rows returned on MAX + ORDER BY

• Submitted: 8 Jul 2009 20:24
• Reporter: Philip Stoev
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.0,5.1,5.4
• OS: Any
• CPU Architecture: Any

[8 Jul 2009 20:24] Philip Stoev

Description:
This only partially realistic query:

SELECT DISTINCT MAX( table1 .`int_key`) field1
FROM BB table1
JOIN BB table2 ON table2 .`int_nokey`
ORDER BY field1

returns two rows with identical values.

How to repeat:
--disable_warnings
DROP TABLE IF EXISTS BB;
--enable_warnings

CREATE TABLE `BB` (
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  KEY `int_key` (`int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `BB` VALUES (9,5),(0,4);

SELECT DISTINCT MAX( table1 .`int_key`) field1
FROM BB table1
JOIN BB table2 ON table2 .`int_nokey`
ORDER BY field1;

[9 Jul 2009 9:52] Philip Stoev

Here is a bit more realistic test case without DISTINCT (just the ORDER BY is redundant) against Azalea. The query is a MAX() that returns multiple rows:

CREATE TABLE `C` (
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  KEY `int_key` (`int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (5,0),(3,0),(0,2),(3,0),(1,3),(0,0),(1,7),(7,0),(1,7),(0,7),(0,9),(8,2),(4,4),(9,3),(0,9),(2,5),(0,5),(8,0),(5,8),(1,5);
CREATE TABLE `BB` (
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  KEY `int_key` (`int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (9,5),(0,4);

SELECT  MIN( table1 .`int_key`  ) field1
FROM BB table1  JOIN C table2  ON table2 .`int_nokey`
ORDER  BY field1   ;