List:General Discussion« Previous MessageNext Message »
From:Lay András Date:February 22 2012 12:05pm
Subject:Strange "row counter" issues
View as plain text  
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 or 5.5.x the cnt column is bad:

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 |
+------+-----------+------+
|    7 | negyszer  |    4 |
|    4 | haromszor |    3 |
|    2 | ketszer   |    2 |
|    1 | egyszer   |    1 |
+------+-----------+------+
4 rows in set (0.00 sec)

Documentation ( http://dev.mysql.com/doc/refman/5.5/en/user-variables.html )
says this, so not a bug:

"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."

Is there any other solution to emulate row counter, which works with the
above query under 5.1 and 5.5 mysql version?

Thank you!

Lay

Thread
Strange "row counter" issuesLay András22 Feb
  • Re: Strange "row counter" issuesMySQL)22 Feb