MySQL Bugs: #16272: IF with floting point values does not work with GROUP BY

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:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18	OS:	Linux (Gentoo Linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
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.

verified with latest 5.0 source, 4.1 returns 5.0 as expected

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.

Workaround:
(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.

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:

  http://lists.mysql.com/commits/2589

The patch looks ok to push to me.

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

Documented in 5.0.19 changelog:

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