Bug #67846 Bug fix LIMIT CLAUSE ORDER BY not fixed
Submitted: 8 Dec 2012 10:48 Modified: 3 Jan 2013 10:12
Reporter: Thomas Brunnthaler Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1.66 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: LIMIT ORDER

[8 Dec 2012 10:48] Thomas Brunnthaler
Description:
In changelog of 5.1.66 i found:

"Adding a LIMIT clause to a query containing GROUP BY and ORDER BY could cause the optimizer to choose an incorrect index for processing the query, and return more rows than required. (Bug #54599, Bug #11762052)"

I use it on Windows and with 5.1.65 the problem did not happen but with 5.1.66 i get wrong results whenever using ORDER BY and LIMIT clause.

How to repeat:
I don't know how to explain exactly (because downgraded to 5.1.65 already) but maybe someone can reproduce also. My database:

TID      ARTICLE      OBS     L     EAN
0        TEST         1       1     1234
1        TEST         0       0     1234
2        TEST         null    1     1234
.....

( (article like '%test%' or ...) ) and (obs = '0' or obs = '' or obs = '1' and l > '0') GROUP by ean ORDER BY tid,article LIMIT 0,25

In 5.1.66 the first article with TID 0 is not present in result. In 5.1.65 the TID 0 OBS 1 is first as expected.
[8 Dec 2012 15:12] Valeriy Kravchuk
Please, send the exact CREATE TABLE statement for the table used.
[8 Dec 2012 15:48] Thomas Brunnthaler
CREATE TABLE IF NOT EXISTS `database` (
  `tid` int(1) DEFAULT NULL,
  `an` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  `article` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `l` int(12) DEFAULT NULL,
  `ean` varchar(13) COLLATE latin1_general_ci DEFAULT NULL,
  `obs` varchar(1) COLLATE latin1_general_ci DEFAULT NULL,
  KEY `tid` (`tid`),
  KEY `an` (`an`),
  KEY `article` (`article`),
  KEY `l` (`l`),
  KEY `ean` (`ean`),
  KEY `obs` (`obs`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
[10 Dec 2012 8:01] Valeriy Kravchuk
Please, send also the exact SELECT statement that produced wrong results.
[13 Dec 2012 20:34] Sveta Smirnova
Thank you for the feedback.

Please send SELECT which Valeriy requested.
[14 Dec 2012 6:10] Thomas Brunnthaler
SELECT * FROM DATABASE WHERE ( (article like '%test%') ) and (obs = '0' or obs = '' or obs = '1' and l > '0') GROUP by ean ORDER BY tid,article LIMIT 0,25
[14 Dec 2012 17:11] Sveta Smirnova
Thank you for the query.

This is not a bug. Please read at http://dev.mysql.com/doc/refman/5.1/en/group-by-extensions.html:

----<q>----
 For the query to be legal, the name column must be omitted from the select list or named in the GROUP BY clause.

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses. 
----</q>----

MySQL is free to change order of selected columns depending from query plan. Use standard SQL syntax to get consistent results.
[3 Jan 2013 10:12] Thomas Brunnthaler
In 5.1.65 the sorting is correct as i expect (also without using the mysql syntax correctly) but in 5.1.66, the sorting is completely different (missing data) even if i dindn't change my query - strange.