Bug #8810 user variables' behaviour depends on using DISTINCT
Submitted: 25 Feb 2005 16:15 Modified: 26 Feb 2005 21:22
Reporter: Przemyslaw Popielarski Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.10 OS:Linux (Linux kernel 2.4.26)
Assigned to: CPU Architecture:Any

[25 Feb 2005 16:15] Przemyslaw Popielarski
Description:
The results on user variables depends of using DISTINCT keyword in a select statement. When not using DISTINCT, operating on user variable (assigned in the same query) gives proper results. When using DISTINCT, the user variable is always set to the value from the previous statement.

How to repeat:
INVALID RESULTS:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14593 to server version: 4.1.10-standard-log

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

mysql> SELECT DISTINCT @VAR1:=10, @VAR1*1 FROM tAnytable;
+-----------+---------+
| @VAR1:=10 | @VAR1*1 |
+-----------+---------+
|        10 |    NULL |
+-----------+---------+
1 row in set (0.00 sec)

mysql> SELECT DISTINCT @VAR1:=20, @VAR1*1 FROM tAnytable;
+-----------+---------+
| @VAR1:=20 | @VAR1*1 |
+-----------+---------+
|        20 |      10 |
+-----------+---------+
1 row in set (0.00 sec)

mysql> SELECT DISTINCT @VAR1:=30, @VAR1*1 FROM tAnytable;
+-----------+---------+
| @VAR1:=30 | @VAR1*1 |
+-----------+---------+
|        30 |      20 |
+-----------+---------+
1 row in set (0.00 sec)

mysql> 

---------------------------------------------------------------------

VALID RESULTS:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14614 to server version: 4.1.10-standard-log

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

mysql> SELECT @VAR1:=10, @VAR1*1 FROM tAnytable;
+-----------+---------+
| @VAR1:=10 | @VAR1*1 |
+-----------+---------+
|        10 |      10 |
|        10 |      10 |
+-----------+---------+
2 rows in set (0.00 sec)

mysql> SELECT @VAR1:=20, @VAR1*1 FROM tAnytable;
+-----------+---------+
| @VAR1:=20 | @VAR1*1 |
+-----------+---------+
|        20 |      20 |
|        20 |      20 |
+-----------+---------+
2 rows in set (0.00 sec)

mysql> SELECT @VAR1:=30, @VAR1*1 FROM tAnytable;
+-----------+---------+
| @VAR1:=30 | @VAR1*1 |
+-----------+---------+
|        30 |      30 |
|        30 |      30 |
+-----------+---------+
2 rows in set (0.00 sec)

mysql> 

Suggested fix:
User variables should behave in the same way in both DISTINCT and non-DISTINCT queries.
[26 Feb 2005 17:48] Aleksey Kishkin
Sorry Cannot repeat it. If you have any ideas how to reproduce this bug please let us know..

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.10-standard-log

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

mysql> SELECT DISTINCT @VAR1:=10, @VAR1*1 from tAnytable;
+-----------+---------+
| @VAR1:=10 | @VAR1*1 |
+-----------+---------+
|        10 |      10 |
+-----------+---------+
1 row in set (0.00 sec)

mysql> SELECT DISTINCT @VAR1:=20, @VAR1*1 from tAnytable;
+-----------+---------+
| @VAR1:=20 | @VAR1*1 |
+-----------+---------+
|        20 |      20 |
+-----------+---------+
1 row in set (0.00 sec)

mysql>
[26 Feb 2005 17:50] Aleksey Kishkin
I found a condition when it happened: when tAnytable contains more than 1 record.
[26 Feb 2005 21:22] Sergei Golubchik
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

See http://dev.mysql.com/doc/mysql/en/variables.html
note "The general rule is to never assign and use the same variable in the same statement."
[11 Apr 2005 23:18] Przemyslaw Popielarski
Sergei,

I still claim, that it IS a bug. You're right, that the manual says: "The general rule is to never assign and use the same variable in the same statement".

But, firstly, this is "a general rule", not the forbidden usage.

Secondly, you guys assign & use variables in the same statements in the examples in the manual, i.e.: "SELECT @a:=SUM(total),@b=COUNT(*),@a/@b AS avg FROM test_table;" (http://dev.mysql.com/doc/mysql/en/extensions-to-ansi.html)

And finally, I DO CLAIM, that the behaviour of MySQL engine should be the same while assigning & using variables in the same statement -- it should NOT matter if I use DISTINCT or not.