MySQL Bugs: #65732: Session variable late value acessment

Bug #65732	Session variable late value acessment
Submitted:	26 Jun 2012 8:43	Modified:	5 Jul 2012 14:56
Reporter:	Tarmo Kaldma	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If second session variable is calculated using another variable created and valued before in the same SET clause then it is not considered an error, but the referenced variable have NULL value when used. Should be an error or work correctly.

How to repeat:
set
@s= 10,
@f= @s;

SELECT @s, @f

In first execution in session @f is NULL, next execution it is 10 and then stay so.

Suggested fix:
Should be recognized as an error in SQL or handled correctly.

Our manual, http://dev.mysql.com/doc/refman/5.1/en/user-variables.html, clearly says:

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

If you ignore the manual, surely you can get unexpected problems.

Nobody newer remember all manuals, therefore is syntactics check there. As I suggested, the syntactic check should have reported this as an error.

OK, so let's consider this a feature request for parser to output error (or warning) whenever same user variable is read and get value assigned in one statement. Makes some sense for some strict SQL mode.

Note that not *all* cases where a user variable is set and read in the same statement are bad.
IMHO an error is a bit harsh on people that need this.
WE can certainly start with a warning. And eventually expand  to a SQL mode that will disable this.

I do not understand how it can ewer be usable to read variable whose value is dependent of current version implementation?

Use case: swap two variables values:

  SET @a=@b, @b=@a;

If variables get new values after the statement is finalised (as it is now) it work. But manual tell that this is not quaranteed, it may or may not be so. Untill it is not clearly defined that this is the wai it works this usage is illegal. So, error message or change in the manual is still desirable.