MySQL Bugs: #9610: Cannot set and read variables in same statement in MySQL 4.1

Bug #9610	Cannot set and read variables in same statement in MySQL 4.1
Submitted:	4 Apr 2005 12:58	Modified:	6 Nov 2005 5:58
Reporter:	Emmanuel KARTMANN	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.10a	OS:	Windows (Windows)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
There is a change in behavior between MySQL 4.0 and MySQL 4.1; apparently one cannot SET AND READ a variable in the same statement in MySQL 4.1.10a, while it works fine in MySQL 4.0!

Is this change in behavior "on purpose" on really a bug?

How to repeat:
MySQL 4.0.20a
===========
SET @v1 := 1, @v2 := @v1 + 1
;
SELECT @v1, @v2
;
+------+------+
| @v1  | @v2  | 
+------+------+
|   1  |  2   |
+------+------+

MySQL 4.1.10a
===========
SET @v1 := 1, @v2 := @v1 + 1
;
SELECT @v1, @v2
;
+------+------+
| @v1  | @v2  | 
+------+------+
|   1  |      | (@v2 is NULL)
+------+------+

Suggested fix:
Behave as in MySQL 4.0 because we need to upgrade to 4.1 without rewriting all MySQL scripts...

I verified this behavior between 4.0.XX and 4.1.XX, however the Manual
explains:

http://dev.mysql.com/doc/mysql/en/variables.html

The general rule is to never assign and use the same variable in the same statement.

I wasn't able to find in our Documentation why those versions behaves
different.

Couldn't reproduce the behaviour:

mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 4.1.11-debug |
+--------------+
1 row in set (0.00 sec)

mysql> SET @v1 := 1, @v2 := @v1 + 1;                                           
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @v1, @v2;                                                        
+------+------+
| @v1  | @v2  |
+------+------+
| 1    | 2    |
+------+------+
1 row in set (0.00 sec)

Scaring. I do have the bug. Here's my session on MySQL 4.1.11 (NON DEBUG, MAX, on Windows - I downloaded the binary from mysql.com):

mysql> SELECT VERSION();
+----------------+
| VERSION()      |
+----------------+
| 4.1.11-max-log |
+----------------+
1 row in set (0.00 sec)

mysql> SET @v1 := 1, @v2 := @v1 + 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @v1, @v2;
+------+------+
| @v1  | @v2  |
+------+------+
| 1    | NULL |
+------+------+
1 row in set (0.00 sec)

Still buggy (or at least different from MySQL 4.0).

E.

See bug #1739:
"the result from assigning and using variable in the save query is not
well defined (it depends on execution plan - in this particular case on using
temporary table for GROUP BY) I would not call it a bug."