MySQL Bugs: #67846: Bug fix LIMIT CLAUSE ORDER BY not fixed

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:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	LIMIT ORDER

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.

Please, send the exact CREATE TABLE statement for the table used.

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;

Please, send also the exact SELECT statement that produced wrong results.

Thank you for the feedback.

Please send SELECT which Valeriy requested.

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

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.

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.