MySQL Bugs: #1053: Using IFNULL on INTEGERS in a SELECT DISTINCT query does not work.

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

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

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

by the way, if 3.23 compiles easy where 4.0 requires some "magic" - you can submit a bugreport about it :)