MySQL Bugs: #64275: Bad row counter under 5.1.x

Bug #64275	Bad row counter under 5.1.x
Submitted:	9 Feb 2012 10:29	Modified:	22 Feb 2012 13:10
Reporter:	Lay András	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S1 (Critical)
Version:	5.1.x	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Hi!

I have a table:

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL auto_increment,
  `cucc` varchar(255) character set utf8 NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

INSERT INTO `test` (`id`, `cucc`) VALUES
(1, 'egyszer'),
(2, 'ketszer'),
(3, 'ketszer'),
(4, 'haromszor'),
(5, 'haromszor'),
(6, 'haromszor'),
(7, 'negyszer'),
(8, 'negyszer'),
(9, 'negyszer'),
(10, 'negyszer');

select * from test;

+----+-----------+
| id | cucc      |
+----+-----------+
|  1 | egyszer   |
|  2 | ketszer   |
|  3 | ketszer   |
|  4 | haromszor |
|  5 | haromszor |
|  6 | haromszor |
|  7 | negyszer  |
|  8 | negyszer  |
|  9 | negyszer  |
| 10 | negyszer  |
+----+-----------+
10 rows in set (0.00 sec)

Under 5.0.x version this query works good, the cnt column is right:

set @row=0;select @row:=@row+1 as cnt,cucc,count(id)hany from test group by cucc order by hany desc;

+------+-----------+------+
| cnt  | cucc      | hany |
+------+-----------+------+
|    1 | negyszer  |    4 |
|    2 | haromszor |    3 |
|    3 | ketszer   |    2 |
|    4 | egyszer   |    1 |
+------+-----------+------+
4 rows in set (0.00 sec)

Under 5.1.x the cnt column is bad:

set @row=0;select @row:=@row+1 as row,cucc,count(id)hany from test group by cucc order by hany desc;
 
+------+-----------+------+
| row  | cucc      | hany |
+------+-----------+------+
|    7 | negyszer  |    4 |
|    4 | haromszor |    3 |
|    2 | ketszer   |    2 |
|    1 | egyszer   |    1 |
+------+-----------+------+
4 rows in set (0.00 sec)

Under 5.5.8 good again, but i can't upgrade to 5.5.x. Is there (or will?) any 5.1.x version which is good?

Thank you!

Lay

How to repeat:
set @row=0;select @row:=@row+1 as cnt,cucc,count(id)hany from test group by cucc order by hany desc;

I get same result in 5.1.60 and 5.5.20:

   cnt  cucc         hany  
------  ---------  --------
     7  negyszer          4
     4  haromszor         3
     2  ketszer           2
     1  egyszer           1

.. but on 5.0.90:

   cnt  cucc         hany  
------  ---------  --------
     1  negyszer          4
     2  haromszor         3
     3  ketszer           2
     4  egyszer           1

.. so if you are right that 5.5.8 behaves like 5.0.x the change occured between 5.5.8 and 5.5.20 (and same time in 5.1.x tree probably).

Peter
(not a MySQL person)

While it is easy to repeat this on both 5.1.61 and 5.5.20, IMHO our manual, http://dev.mysql.com/doc/refman/5.5/en/user-variables.html, warns again this kind of use very clearly. Unexpected results are expected.

Please, check.

Do you mean theese sentences?

"In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected:"

This is literally in the 5.0 documentation too ( http://dev.mysql.com/doc/refman/5.0/en/user-variables.html ), but under 5.0.x row counter works fine.

Valeriy Kravchuk, can You explain trough an example how can I always cahtch the expected results? Or I can't hope any exact results throught the mysql server's versions?

I think Valeriy meant this:

"As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server. "

Closing as "Not a Bug"

On the mysql mailing list, a helpfull guy, Shawn Green told me the solution:

"You need to materialize your sorted results before applying your row counter. You can use an automatic temp table or a manual temporary table. Here is one way to do this using an automatic temp table:

set @row=0;select @row:=@row+1 as cnt,cucc, hany FROM (SELECT cucc,count(id) hany from test group by cucc order by hany desc) as stats;

The other option is to create the row counter in your application, and not within the database. But, that all depends on how you need the data, too. Any way you go, you must first create your results, then assign them row numbers as we have changed (hopefully improved) the efficiency of how we evaluated the original query which is why your row numbering system no longer works as you expected it to."

I was inspired by this report to look for a solution too. I found a solution in a user comment in the documentation (don't rememeber which version). The solution is to use a subquery if the FROM-clause - like:

SELECT @t:=@t+1 AS uniq, id FROM (SELECT @t:=0) AS derived, thetable ORDER BY uniq;

This does the same (also with various JOINs, HAVING etc.) as 

SET @t = 0;
SELECT @t:=@t+1 AS uniq, id FROM thetable ORDER BY uniq;

.. so the warnng in the docs does not seem to apply to a subquery if the FROM-clause.  This is evaluated as a seprate change.  However optimizer changes in the future could change this (and I have not checked in MariaDB 5.3 where the optimizer has been rewritten for better subquery performance).

too many typos in my last post :( .

I will reframe:

.. so the warnng in the docs does not seem to apply to a subquery if the FROM-clause. This is evaluated as a seprate change. 

>>

.. so the warning in the docs does not seem to apply to a subquery if the FROM-clause. This is evaluated as a separate query.