MySQL Bugs: #1834: IF() return incorrect result

Bug #1834	IF() return incorrect result
Submitted:	13 Nov 2003 23:20	Modified:	15 Nov 2003 5:21
Reporter:	CW WAN	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.16	OS:	Windows (windows 2000 server)
Assigned to:	Victor Vagin	CPU Architecture:	Any

Description:
this bug was fix in 4.0.14
refer bug #551
but it appear again in version 4.0.16

try execute this with mysqlgui or any program with mysql odbc,
but CONTROL CENTER doesn't have this problem

select @a:=10,@b:=2.82,@C:=if(@a>@b,@a,@b)

return result

+--------+----------+---------------------+
| @a:=10 | @b:=2.82 | @C:=if(@a>@b,@a,@b) |
+--------+----------+---------------------+
|     10 |     2.82 | 2.82                |
+--------+----------+---------------------+

thanks

How to repeat:
select @a:=10,@b:=2, @a > @b, @a < @b

+--------+-------+---------+---------+
| @a:=10 | @b:=2 | @a > @b | @a < @b |
+--------+-------+---------+---------+
|     10 |     2 |       0 |       1 |
+--------+-------+---------+---------+

nothing special, just change priority to medium, sorry

nothing special, just change priority to medium, sorry

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

Unfortunately, semantics of such queries with user variables can't always be complitely defined, so mysql doesn't support them.

you can read in detail about it at
http://www.mysql.com/doc/en/Variables.html

Exactly:

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

Another issue with setting a variable and using it in the same statement is that the default result type of a variable is based on the type of the variable at the start of the statement. (A not assigned variable is assumed to have value NULL and to be of type STRING). The following example illustrates this: 

mysql> SET @a="test";
mysql> SELECT @a,(@a:=20) FROM table_name;
In this case MySQL will report to the client that column 1 is a string and convert all accesses of @a to strings, even if @a will be set to a number for the second row. After the statement is executed @a will be regarded as a number. 

If you have any problems with this, either avoid to set and use the same variable in the same statement or set the variable to 0, 0.0 or "" before you use it. 

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

BTW, you can try to use

mysql> select @a:=10,@b:=2, @a+0 > @b+0;
+--------+-------+-------------+
| @a:=10 | @b:=2 | @a+0 > @b+0 |
+--------+-------+-------------+
|     10 |     2 |           1 |
+--------+-------+-------------+
1 row in set (0.00 sec)

thanks