Hi, there seems to be some bugs when using floating point constants and user variables with the if() function. Is this known bugs? I have searched for it, but not found anything... is it fixed... I have tested it on 3.23.30-gamma on win2k and 3.23.39 on solaris, I don't have access to any newer installation. Maybe someone could test it on 4.0.x?
mysql> select if(0.4,'true','false'),if(0.5,'true','false'); +------------------------+------------------------+ | if(0.4,'true','false') | if(0.5,'true','false') | +------------------------+------------------------+ | false | true | +------------------------+------------------------+ 1 row in set (0.00 sec) This is as expected: 0.4 is rounded down to 0, and evaluates as false, while 0.5 is rounded up to 1, and evaluates as true. (This is mentioned on <URL: http://www.mysql.com/doc/C/o/Control_flow_functions.html >) mysql> select @a:=0.4,@b:=0.5,if(@a,'true','false'),if(@b,'true','false'); +---------+---------+-----------------------+-----------------------+ | @a:=0.4 | @b:=0.5 | if(@a,'true','false') | if(@b,'true','false') | +---------+---------+-----------------------+-----------------------+ | 0.4 | 0.5 | false | false | +---------+---------+-----------------------+-----------------------+ 1 row in set (0.01 sec) In this case I would expect @b to be true... I suspect the problem is it is beeing converted to the string '0.5', which again is truncated to the integer 0: mysql> select if('0.9','true','false'); +--------------------------+ | if('0.9','true','false') | +--------------------------+ | false | +--------------------------+ 1 row in set (0.01 sec) The string '0.9' is obviously not recognized as a floating point number... mysql> select @a:='A',@b:='B',if(@a,'true','false'),if(@b,'true','false'); +---------+---------+-----------------------+-----------------------+ | @a:='A' | @b:='B' | if(@a,'true','false') | if(@b,'true','false') | +---------+---------+-----------------------+-----------------------+ | A | B | false | false | +---------+---------+-----------------------+-----------------------+ 1 row in set (0.00 sec) ...and string values are always false (except '1','2','2a' and so on!), only integers seems to work as user variables for the if function: mysql> select @a:=0,@b:=1,if(@a,'true','false'),if(@b,'true','false'); +-------+-------+-----------------------+-----------------------+ | @a:=0 | @b:=1 | if(@a,'true','false') | if(@b,'true','false') | +-------+-------+-----------------------+-----------------------+ | 0 | 1 | false | true | +-------+-------+-----------------------+-----------------------+ 1 row in set (0.00 sec) Floating point expressions are also buggy: mysql> select if(0.4+0.4,'true','false'),if(0.5-0.4,'true','false'); +----------------------------+----------------------------+ | if(0.4+0.4,'true','false') | if(0.5-0.4,'true','false') | +----------------------------+----------------------------+ | false | true | +----------------------------+----------------------------+ 1 row in set (0.00 sec) In this case I would expect the first expression to be true and the second false. It seems that each of the operands in the expression is evaluated instead of the result... expressions containing a floating point number above 0.5 seems to return true, expressions containing a floating point number below 0.5 seems to return false...: mysql> select if(0.5-0.4-0.1,'true','false'),if(0.4*10,'true','false'); +--------------------------------+---------------------------+ | if(0.5-0.4-0.1,'true','false') | if(0.4*10,'true','false') | +--------------------------------+---------------------------+ | true | false | +--------------------------------+---------------------------+ (if(0.5-0.5) is false, though...) -- Roger --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php