MySQL Bugs: #9764: IFNULL truncates data

Bug #9764	IFNULL truncates data
Submitted:	8 Apr 2005 15:07	Modified:	8 Jun 2005 15:51
Reporter:	Stefano Crimi'	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.0.3alpha	OS:	Windows (Windows 2000/Linux)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
When you use the IFNULL operator in as SELECT DISTINCT, the data are truncated according to the lenght of the "null"-replacer:

mysql> select BUS,DES_BUS from business;
+------+----------------------+
| BUS  | DES_BUS              |
+------+----------------------+
| 01   | LIBERA INSTALLAZIONE |
| 02   | INCASSO              |
| 03   | CKD                  |
+------+----------------------+
3 rows in set (0.00 sec)

mysql> select BUS,IFNULL(DES_BUS,'OTHER') from business;
+------+-------------------------+
| BUS  | IFNULL(DES_BUS,'OTHER') |
+------+-------------------------+
| 01   | LIBERA INSTALLAZIONE    |
| 02   | INCASSO                 |
| 03   | CKD                     |
+------+-------------------------+
3 rows in set (0.03 sec)

mysql> select distinct BUS,IFNULL(DES_BUS,'OTHER') from business;
+------+-------------------------+
| BUS  | IFNULL(DES_BUS,'OTHER') |
+------+-------------------------+
| 01   | LIBER                   |
| 02   | INCAS                   |
| 03   | CKD                     |
+------+-------------------------+
3 rows in set (0.00 sec)

mysql> select distinct BUS,IFNULL(DES_BUS,'O') from business;
+------+---------------------+
| BUS  | IFNULL(DES_BUS,'O') |
+------+---------------------+
| 01   | L                   |
| 02   | I                   |
| 03   | C                   |
+------+---------------------+
3 rows in set (0.00 sec)

How to repeat:
Just try on your own tables...

Verified also on Linux.
Thank you for the bug report.

I have checked on the new 5.0.6beta version, and I have discovered that the bug appears only when the field is a text field (char or varchar) longer the 30 characters: therefore, if the field length is set to 30 (or less), the bug doesn't appear, which starting from a length of 31, the bug appears, resulting in a truncation of the result.

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/internals/25759

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html