Bug #16272 IF with floting point values does not work with GROUP BY
Submitted: 6 Jan 2006 23:16 Modified: 21 Feb 2006 22:32
Reporter: Kai Ruhnau Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.18 OS:Linux (Gentoo Linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[6 Jan 2006 23:16] Kai Ruhnau
The following query gives strange results:

mysql> SELECT IF(RAND(),40.0,5.00) FROM mysql.user GROUP BY 1=1, User LIMIT 1;
| IF(RAND(),40.0,5.00) |
|                 9.99 |
1 row in set (0.00 sec)

How to repeat:
One GROUP BY argument must be from the target table.
A second GROUP BY argument must exist.
The IF condition must not be static.
The true-case floating point number must be >=1.
The false case floating point number must be >=0.

The result of IF is calculated via:
The maximum number of digits before and after decimal point is filled with 9.
The first 9 is replaced with 0.
[8 Jan 2006 14:57] Hartmut Holzgraefe
verified with latest 5.0 source, 4.1 returns 5.0 as expected
[9 Jan 2006 0:17] Kai Ruhnau
If I'm not missing something, I would expect 40.0 most of the time. RAND() returns something <> 0 "most of the time". This is documented to mean true in a condition.
[10 Jan 2006 12:06] Kai Ruhnau
(for loosely typed languages)

Make the floting point numbers strings.

mysql> SELECT IF(RAND(),'40.0','5.00') FROM mysql.user GROUP BY 1=1, User LIMIT 1;
| IF(RAND(),'40.0','5.00') |
| 40.0                     |
1 row in set (0.00 sec)

This also works for calculations within MySQL because the strings are converted back to floats.
[14 Feb 2006 13:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

[14 Feb 2006 13:38] Alexander Barkov
The patch looks ok to push to me.
[14 Feb 2006 17:13] Evgeny Potemkin
The Item_func_if::fix_length_and_dec() function when calculating length of 
result doesn't take into account unsigned_flag. But it is taken when 
calculating length of temporary field. This results in creating field that 
shorter than needed. Due to this, in the reported query 40.0 converted to 9.99.

Fixed in 5.0.19, cset 1.2038.2.1
[21 Feb 2006 22:32] Mike Hillyer
Documented in 5.0.19 changelog:

          A call to the <function>IF()</function> function using
          decimal arguments could return incorrect
          results. (Bug #16272)