MySQL Bugs: #1194: MySQL server doesn't work correctly with user variable if it's previously used

Bug #1194	MySQL server doesn't work correctly with user variable if it's previously used
Submitted:	4 Sep 2003 7:25	Modified:	1 Oct 2003 1:53
Reporter:	Victoria Reznichenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.14	OS:	Any (any)
Assigned to:	Victor Vagin	CPU Architecture:	Any

Description:
MySQL server doesn't work correctly with user variable if it's previously used in UPDATE statement.

mysql> select @c:=0;
+-------+
| @c:=0 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> update t1 set id=(@c:=@c+1);
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select @c;
+------+
| @c   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select @c:=0;
+-------+
| @c:=0 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> update t1 set id=(@c:=@c+1);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select @c;
+------+
| @c   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

^^^^^^^^^ should be @c=1

mysql> select @c:=0;
+-------+
| @c:=0 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> select @c:=@c+1;
+----------+
| @c:=@c+1 |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

If initialize variable with SET command instead of SELECT, it works like a charm. It also works fine on v3.23.57, v4.0.13 and v4.1.0.

How to repeat:
CREATE TABLE t1 (id INT);
INSERT INTO t1 VALUES (1);
SELECT @c:=0;
UPDATE t1 SET id=(@c:=@c+1);
SELECT @c;
SELECT @c:=0;
UPDATE t1 SET id=(@c:=@c+1);
SELECT @c;
SELECT @c:=0;
SELECT @c:=@c+1;

I have another testcase (could be the same problem) which shows too that initializing the variable with SET works.

[guilhem@gbichot2 sql]$ mysql1 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17 to server version: 4.0.15-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

MASTER> select @a, @a:=1;
+------+-------+
| @a   | @a:=1 |
+------+-------+
| NULL |     1 |
+------+-------+
1 row in set (0.00 sec)

MASTER> select @a;
+------+
| @a   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

MASTER> select @a, @a:=1;
+---------------+-------+
| @a            | @a:=1 |
+---------------+-------+
| 3917010174001 |     1 |
+---------------+-------+
1 row in set (0.00 sec)

MASTER> Bye
[guilhem@gbichot2 sql]$ mysql1 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 4.0.15-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

MASTER> set @a=1;
Query OK, 0 rows affected (0.00 sec)

MASTER> select @a;
+------+
| @a   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

MASTER> select @a, @a:=1;
+------+-------+
| @a   | @a:=1 |
+------+-------+
|    1 |     1 |
+------+-------+
1 row in set (0.00 sec)

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html