Bug #75 Bug with user defined variables
Submitted: 15 Feb 2003 16:01 Modified: 15 Feb 2003 23:31
Reporter: jocelyn fournier (Silver Quality Contributor) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:all OS:Any (all)
Assigned to: CPU Architecture:Any

[15 Feb 2003 16:01] jocelyn fournier
Description:
If a user defined is set in the SELECT and use in the WHERE clause, the result is wrong. 

How to repeat:
SET @a=2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT a FROM (SELECT 1 a) a WHERE @a=3;
Empty set (0.00 sec)

mysql> SELECT a FROM (SELECT 1 a) a WHERE @a=2;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> SELECT a FROM (SELECT 1 a) a WHERE @a=1;
Empty set (0.00 sec)

=> it works ok

However :

SELECT @a:=2 FROM (SELECT 1 a) a WHERE @a=3;
+-------+
| @a:=2 |
+-------+
|     2 |
+-------+

Should not return any result, AFAIK (since SELECT 1 FROM (SELECT 1 a) a
WHERE @a=3; returns nothing)
[15 Feb 2003 23:31] Michael Widenius
This is not a bug;  The time of assignment is not defined (and can't be)  
when you set a variable in a SELECT or WHERE statement.  
  
This is because the columns in the SELECT part may be calculated at  
different points in time (when crearing a temporary table for the results,  
when result is sent to client etc) and may even be calculated multiple 
times, depending on how MySQL resolves the query.  
  
The simple rule is that never set and use a variable in the same statement. 
This is also documented in the manual.