Bug #1053 Using IFNULL on INTEGERS in a SELECT DISTINCT query does not work.
Submitted: 15 Aug 2003 4:37 Modified: 15 Aug 2003 6:04
Reporter: Rasmus Tengblad Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:3.23.54 OS:BSD/OS
Assigned to: CPU Architecture:Any

[15 Aug 2003 4:37] Rasmus Tengblad
Description:
IFNULL on an integer will treat the second argument as an integer regardless of its type when used in a SELECT DISTINCT-query. It works as expected without the keyword DISTINCT, and the normal IF-function works regardless of DISTINCT, so using IF(foo IS NULL,'foo was null', foo) does get around the problem.

mysql 4.x did not compile mithout magic on my machine, so I haven't tested it on more recent versions. (sorry)

How to repeat:
mysql> create table tab1 (a int, b int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tab1 values (1, NULL);
Query OK, 1 row affected (0.03 sec)

mysql> insert into tab1 values (2, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tab1 values (3, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT DISTINCT a, b, IF(b IS NULL, 'b not set', b), IFNULL(b, '7 is wrong') FROM tab1;
+------+------+-------------------------------+-------------------------+
| a    | b    | IF(b IS NULL, 'b not set', b) | IFNULL(b, '7 is wrong') |
+------+------+-------------------------------+-------------------------+
|    1 | NULL | b not set                     |                       7 |
|    2 | NULL | b not set                     |                       7 |
|    3 | NULL | b not set                     |                       7 |
+------+------+-------------------------------+-------------------------+
3 rows in set (0.02 sec)

Suggested fix:
Implement IFNULL like IF is implemented. I actually thought IFNULL was a macro...
[15 Aug 2003 5:19] Sergei Golubchik
Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

According to the "Upgrading from 3.23" section of the manual:

   * The default type returned by `IFNULL(A,B)' is now set to be the
     more 'general' of the types of `A' and `B'. (The
     general-to-specific order is string, `REAL' or `INTEGER').

So, it's fixed in 4.0
[15 Aug 2003 6:04] Sergei Golubchik
by the way, if 3.23 compiles easy where 4.0 requires some "magic" - you can submit a bugreport about it :)