MySQL Bugs: #70162: Wrong result for @i:=@i+1 in query while using group by and order by

Bug #70162	Wrong result for @i:=@i+1 in query while using group by and order by
Submitted:	27 Aug 2013 12:57	Modified:	28 Aug 2013 2:12
Reporter:	vin chen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.1 5.5 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	@i:=@i+1 Wrong result

Description:
While mysql upgrade from 5.0.67 to 5.5.24, some query result is different as before.
After simplifying the problem, we found that query while using temporary in explain(like group by) would trigger the issue.

How to repeat:
create table t1(c1 int);
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);

in 5.5
mysql> set @i:=0;
mysql> select @i:=@i+1 rank, c1 from t1 group by c1 order by c1 desc;
+------+------+
| rank | c1   |
+------+------+
|    3 |    3 |
|    2 |    2 |
|    1 |    1 |
+------+------+
3 rows in set (0.01 sec)

in 5.0
mysql> set @i:=0;
mysql> select @i:=@i+1 rank, c1 from t1 group by c1 order by c1 desc;
+------+------+
| rank | c1   |
+------+------+
|    1 |    3 |
|    2 |    2 |
|    3 |    1 |
+------+------+
3 rows in set (0.00 sec)

# In my option, something wrong in Using temporary
mysql> explain select @i:=@i+1 rank, c1 from t1 group by c1 order by c1 desc;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.01 sec)

# remove the group by, the result always is same in mysql 5.0 and 5.5.
mysql> set @i:=0;
mysql> select @i:=@i+1 rank, c1 from t1 order by c1 desc;
+------+------+
| rank | c1   |
+------+------+
|    1 |    3 |
|    2 |    2 |
|    3 |    1 |
+------+------+
3 rows in set (0.02 sec)

# without Using temporary, the result is correct.
mysql> explain select @i:=@i+1 rank, c1 from t1 order by c1 desc;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.01 sec)

Suggested fix:
In fact, we don't know the calulation rules of user variables like @i:=@i+1.
In MySQL 5.0, user variables always calculate after group by and order by.
However, it's ambiguous in 5.1/5.5/5.6.

Maybe something wrong in create_tmp_table of sql/sql_select.cc

Please note that this exact use case is expressly discouraged and noted as unsupported in the manual:

"As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:

SET @a = @a + 1;

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for expressions involving user variables is undefined."

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

mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

The reference to b in the HAVING clause refers to an alias for an expression in the select list that uses @aa. This does not work as expected: @aa contains the value of id from the previous selected row, not from the current row."

http://dev.mysql.com/doc/refman/5.6/en/user-variables.html

In that context, I am changing the status of this bug report to indicate it is not a bug.

Does it mean that the behavior of @i:=@i+1 in select is undefined in mysql?

1. select @i:=@i+1 rank from t1;
2. select @i:=@i+1 rank from t1 where ***;
3. select @i:=@i+1 rank from t1 where *** group by *** ;
4. select @i:=@i+1 rank from t1 where *** group by ***  order by ***;
where *** is not refering to @i.

Is the above sql also discouraged ?

I don't think the manual is clear about this. And it's common use of user-defined variables like statement 1 and 2.

The manual:
 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:

mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

In statement 3 and 4, HAVING, GROUP BY, or ORDER BY clause is not referring to a variable, when the select expression would be evaluated ?

It's not clear about this.