Bug #551 IF() return incorrect result
Submitted: 30 May 2003 22:22 Modified: 25 Jun 2003 23:50
Reporter: CW WAN Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:1.7.5 OS:Windows (win98/win2000)
Assigned to: Bugs System CPU Architecture:Any

[30 May 2003 22:22] CW WAN
Description:
using mysqlgui 1.7.5 execute

select @a:=16,@b:=7,IF(@a>=@b,@a,@b)
return 7

How to repeat:
select @a:=15,@b:=2,IF(@a>=@b,@a,@b)
return 2
[30 May 2003 22:27] CW WAN
select @a:=15,@b:=2,IF(@a>=@b,@a,@b)
return 2
any one can help?
[31 May 2003 4:09] Alexander Keremidarski
There is no problem with IF() itself.

Something goes wrong when comparing two user variables.

mysql> select @a:=5 as a, @b:=2 as b, @a > @b, @a > 2, 5 > @b, @a < @b;
+---+---+---------+--------+--------+---------+
| a | b | @a > @b | @a > 2 | 5 > @b | @a < @b |
+---+---+---------+--------+--------+---------+
| 5 | 2 |       1 |      1 |      1 |       0 |
+---+---+---------+--------+--------+---------+
[22 Jun 2003 16:23] Alexander Keremidarski
Sorry,
My previsous example was from 'wrong' part of testing sequence. It shos correct result. Here are two more test cases which demonstrate the bug:

# Correct result

mysql> select @a:=10,@b:=1, @a > @b, @a < @b;
+--------+-------+---------+---------+
| @a:=10 | @b:=1 | @a > @b | @a < @b |
+--------+-------+---------+---------+
|     10 |     1 |       1 |       0 |
+--------+-------+---------+---------+

# Wron result 
mysql> select @a:=10,@b:=2, @a > @b, @a < @b;
+--------+-------+---------+---------+
| @a:=10 | @b:=2 | @a > @b | @a < @b |
+--------+-------+---------+---------+
|     10 |     2 |       0 |       1 |
+--------+-------+---------+---------+
[25 Jun 2003 23:50] Victor Vagin
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

The reason of the bug was that mysql operated with user variable as it was string.
[3 Jul 2003 3:16] CW WAN
thanks guys!