MySQL Bugs: #61408: incorrect behaviour of user-defined variables

Bug #61408	incorrect behaviour of user-defined variables
Submitted:	4 Jun 2011 18:37	Modified:	5 Jun 2011 17:06
Reporter:	Daniil Kamenskiy	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.90	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	User-Defined Variables

Description:
Mysql Server (5.0.90) based on FreeBSD 7.0-STABLE-200807 port.

There is table with four fields of integer type.

 CREATE TABLE `t` (
  `a` int(11) default NULL,
  `b` int(11) default NULL,
  `c` int(11) default NULL,
  `d` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
insert into t values(10,8,7,9);

Goal - get max value of these fields (10).

Trying to get this result using user-defined variables:

assign the variable @t1 the maximum value from the pair (a, b).
assign the variable @t2 the maximum value from the pair (c,d).
assign the variable @t3 the maximum value from the pair (@t1,@t2).

SELECT @t1:=IF(a>b,a,b), @t2:=IF(c>d,c,d), @t3:=IF(@t2>@t1,@t2,@t1) FROM t;

result of query:

+------------------+------------------+--------------------------+
| @t1:=IF(a>b,a,b) | @t2:=IF(c>d,c,d) | @t3:=IF(@t2>@t1,@t2,@t1) |
+------------------+------------------+--------------------------+
|               10 |                9 | 9                        |
+------------------+------------------+--------------------------+

Problem: incorrect value in a variable @t3 (9).

In case of repeating this query (within current session) variable in @t3 assigned correct value (10).

Note:

Query
CREATE TABLE tmp_x SELECT @t1:=IF(a>b,a,b), @t2:=IF(c>d,c,d), @t1,
@t2, @t1>@t2 FROM t;

creats a table:

CREATE TABLE `tmp_x` (
  `@t1:=IF(a>b,a,b)` int(11) default NULL,
  `@t2:=IF(c>d,c,d)` int(11) default NULL,
  `@t1` longblob,
  `@t2` longblob,
  `@t1>@t2` int(1) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I suggest that variables @t1 and t2 must have type int, no longblob.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 283661
Server version: 5.0.90 FreeBSD port: mysql-server-5.0.90

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t(a int, b int ,c int,d int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values(10,8,7,9);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @t1:=IF(a>b,a,b), @t2:=IF(c>d,c,d), @t3:=IF(@t2>@t1,@t2,@t1) FROM t;
+------------------+------------------+--------------------------+
| @t1:=IF(a>b,a,b) | @t2:=IF(c>d,c,d) | @t3:=IF(@t2>@t1,@t2,@t1) |
+------------------+------------------+--------------------------+
|               10 |                9 | 9                        |
+------------------+------------------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT @t1:=IF(a>b,a,b), @t2:=IF(c>d,c,d), @t3:=IF(@t2>@t1,@t2,@t1) FROM t;
+------------------+------------------+--------------------------+
| @t1:=IF(a>b,a,b) | @t2:=IF(c>d,c,d) | @t3:=IF(@t2>@t1,@t2,@t1) |
+------------------+------------------+--------------------------+
|               10 |                9 |                       10 |
+------------------+------------------+--------------------------+
1 row in set (0.00 sec)

but .. 

DROP TABLE IF EXISTS `t`;

CREATE TABLE `t` (
  `a` INT(11) DEFAULT NULL,
  `b` INT(11) DEFAULT NULL,
  `c` INT(11) DEFAULT NULL,
  `d` INT(11) DEFAULT NULL
) ENGINE=MYISAM;
INSERT INTO t VALUES(10,8,7,9);

SELECT @t1:=IF(a>b,a,b), @t2:=IF(c>d,c,d) FROM t;
SELECT @t3:=IF(@t2>@t1,@t2,@t1) FROM t; -- returns 10.

With your example you try to assign a value to @t3 from yet non-populated @t1 and @t2. With assigning 1+2+3 in *one* statement there is no guarantee in what order they are populated (not necessarily from left to right). I am not perfectly sure but I think it is a documented limitation, actually. 

Peter
(not a MySQL person)

I think our manual (http://dev.mysql.com/doc/refman/5.0/en/user-variables.html) clearly explains that you may get unexpected results with this kind of statements:

"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 SELECT @a, @a:=@a+1, ..., you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation.

Another issue with assigning a value to a variable and reading the value within the same statement is that the default result type of a variable is based on its type at the start of the statement."