Bug #36666 if()
Submitted: 12 May 2008 13:06 Modified: 12 May 2008 16:41
Reporter: alain gilson Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.051 OS:Windows (XP SP3)
Assigned to: CPU Architecture:Any
Tags: if does not function with empty string with XP SP3

[12 May 2008 13:06] alain gilson
I just installed the SP3 for XP home french.
From now the function if(string, A1, A2) results always in action A2.
if(0, A1, A2) functions normally.
The routines that worked normally within XP SP2 now function abnormally with SP3 just because of that...

How to repeat:
command line 
select if("", 1, 2) returns 2
select if("azerty", 1, 2) returns also 2
[12 May 2008 13:34] Valeriy Kravchuk
Sorry, but this is not a bug. Please, read http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if carefully:

"expr1 is evaluated as an integer value, which means that if you are testing floating-point or string values, you should do so using a comparison operation.

mysql> SELECT IF(0.1,1,0);
        -> 0
mysql> SELECT IF(0.1<>0,1,0);
        -> 1

In the first case shown, IF(0.1) returns 0 because 0.1 is converted to an integer value, resulting in a test of IF(0). This may not be what you expect. In the second case, the comparison tests the original floating-point value to see whether it is non-zero. The result of the comparison is used as an integer."
[12 May 2008 16:41] alain gilson
I apologize.
My procedure was only partially tested and my readings were much too fast!