Hello Lay,
On 2/22/2012 07:05, Lay András wrote:
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?
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.
Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
