MySQL Bugs: #11018: Field aliases with @name:= do not return correct values

Bug #11018	Field aliases with @name:= do not return correct values
Submitted:	1 Jun 2005 15:02	Modified:	1 Jun 2005 15:27
Reporter:	jose luis alfonso arques	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.12a	OS:	Windows (Windows 2000)
Assigned to:	Matthew Lord	CPU Architecture:	Any

Description:
Hello, i've been testing this bug in version 4.1.8 too.

Simple query , using @fields, returning bad results

How to repeat:
select @totalhoras:=sum(tecnicos.horas),
@gastohora:=tecnicos.gastoh,
@totalhoras*@gastohora from partes.tecnicos where tecnico = 112 group by tecnicos.tecnico;

the problem is in third column, i'm getting diferent results when I execute the query, sometimes return 'null', sometimes return another number, obviously the table contents doesn't change, i execute the queries one after other, and i get different results. 

Working in localhost, no other connections through server

if i ommit the @names, it works well.

Thankyou and apologize for any inconvenience if it's not a bug.

Hi Jose,

Thank you for your bug report!  You should not use variables in that way.  This page
explains why:
http://dev.mysql.com/doc/mysql/en/variables.html

"Note: In a SELECT statement, each expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, you cannot refer to an expression that involves variables that are set in the SELECT list. For example, the following statement 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 does not contain the value of the current row, but the value of id from the previous selected row.

The general rule is to never assign a value to a user variable in one part of a statement and use the same variable in some other part the same statement. You might get the results you expect, but this is not guaranteed."

Best Regards