MySQL Bugs: #55232: Variable for setting up an increment field, adds one after using HAVING

Bug #55232	Variable for setting up an increment field, adds one after using HAVING
Submitted:	13 Jul 2010 21:05	Modified:	13 Jul 2010 21:23
Reporter:	Chris Litras	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	MySQL 5.1.42-community	OS:	Windows (Windows 7 x64)
Assigned to:		CPU Architecture:	Any
Tags:	having, increment, user, variable

Description:
Hello.

I am trying to create a dynamic enumeration field by using a mysql variable initializing it to 0 and then increasing it by one for each row, which works just fine.

The use of it is to apply a position on a highscore table result dynamically and all by using (the fastest way) only a MySQL query.

Example:
SELECT @rownum:=@rownum+1 AS rownum, t.*
FROM (SELECT @rownum:=0) AS r, (SELECT 'A', 'B', 'C', 'D') AS t;

Returns correctly:
+--------+---+---+---+---+
| rownum | A | B | C | D |
+--------+---+---+---+---+
|      1 | A | B | C | D |
+--------+---+---+---+---+
1 row in set (0.00 sec)

Now when I want to use HAVING on the 'rownum', engine selects the correct enum row, but it increases 'rownum' by one:

Example:
SELECT @rownum:=@rownum+1 AS rownum, t.*
FROM (SELECT @rownum:=0) AS r, (SELECT 'A', 'B', 'C', 'D') AS t
HAVING rownum=1;

Returns:
+--------+---+---+---+---+
| rownum | A | B | C | D |
+--------+---+---+---+---+
|      2 | A | B | C | D |
+--------+---+---+---+---+
1 row in set (0.00 sec)

It returns the correct row, but it increases the variable by one which is not correct.
I assume that the engine somehow gives a last increase before returning the result when searching for it on a HAVING statement.

My (local) MySQL version is: MySQL 5.1.42-community (Windows 6 x64).
Also tried at a web server running version: MySQL 5.1.39-log (Unix?)

I've searched a little bit to find a similar bug with no results.
I also don't know if I am doing something wrong.

How to repeat:
SELECT @rownum:=@rownum+1 AS rownum, t.*
FROM (SELECT @rownum:=0) AS r, (SELECT 'A', 'B', 'C', 'D') AS t
HAVING rownum=1;

Returns:
+--------+---+---+---+---+
| rownum | A | B | C | D |
+--------+---+---+---+---+
|      2 | A | B | C | D |
+--------+---+---+---+---+
1 row in set (0.00 sec)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

According to http://dev.mysql.com/doc/refman/5.1/en/user-variables.html:

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 SELECT @a, @a:=@a+1, ..., you might think that MySQL will evaluate @a  first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation.

Hello Sveta and thank you for the super fast reply!
I am sorry, I spend only half hour searching for this.
It makes sense and I will come up with another way around to do what I am aiming for.