MySQL Bugs: #33859: IFNULL broken for unsigned bigint values

Bug #33859	IFNULL broken for unsigned bigint values
Submitted:	14 Jan 2008 10:57	Modified:	14 Jan 2008 12:17
Reporter:	Florian Wesch	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.54, 5.0.45	OS:	Linux (debian sid)
Assigned to:		CPU Architecture:	Any
Tags:	bigint ifnull

Description:
An unexpected result is displayed in the client when using IFNULL on unsigned bigint values. IFNULL(val, 0) won't display values bigger then 0x7FFFFFFFFFFFFFFF (the biggest value representable using 63 bits) on the client. Strange enough, using "into outfile" syntax yields the correct result.

How to repeat:
use test;
create temporary table test (val BIGINT UNSIGNED);
insert into test set val=0x7FFFFFFFFFFFFFFE;
insert into test set val=0x7FFFFFFFFFFFFFFF;
insert into test set val=0x8000000000000000;
insert into test set val=0x8000000000000001;
insert into test set val = NULL;
select IFNULL(val, 0), IF(val IS NULL, 0, val), val from test;

+---------------------+-------------------------+---------------------+
| IFNULL(val, 0)      | IF(val IS NULL, 0, val) | val                 |
+---------------------+-------------------------+---------------------+
| 9223372036854775806 |     9223372036854775806 | 9223372036854775806 | 
| 9223372036854775807 |     9223372036854775807 | 9223372036854775807 | 
| 9223372036854775807 |     9223372036854775808 | 9223372036854775808 | 
| 9223372036854775807 |     9223372036854775809 | 9223372036854775809 | 
|                   0 |                       0 |                NULL | 
+---------------------+-------------------------+---------------------+

select IFNULL(val, 0), IF(val IS NULL, 0, val), val from test into outfile "/tmp/ifnull";
$ cat /tmp/ifnull
9223372036854775806     9223372036854775806     9223372036854775806
9223372036854775807     9223372036854775807     9223372036854775807
9223372036854775808     9223372036854775808     9223372036854775808
9223372036854775809     9223372036854775809     9223372036854775809
0       0       \N
$

Suggested fix:
Use IF(val is NULL, x, val) instead of IFNULL(val, x)

Thank you for a bug report. Results on 5.0.54 are even more wrong:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -T -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.54-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create temporary table test (val BIGINT UNSIGNED);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test set val=0x7FFFFFFFFFFFFFFE;
Query OK, 1 row affected (0.03 sec)

mysql> insert into test set val=0x7FFFFFFFFFFFFFFF;
Query OK, 1 row affected (0.03 sec)

mysql> insert into test set val=0x8000000000000000;
Query OK, 1 row affected (0.03 sec)

mysql> insert into test set val=0x8000000000000001;
Query OK, 1 row affected (0.03 sec)

mysql> insert into test set val = NULL;
Query OK, 1 row affected (0.03 sec)

mysql> select IFNULL(val, 0), IF(val IS NULL, 0, val), val from test;
Field   1:  `IFNULL(val, 0)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     21
Max_length: 19
Decimals:   0
Flags:      NOT_NULL BINARY NUM

Field   2:  `IF(val IS NULL, 0, val)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     21
Max_length: 20
Decimals:   0
Flags:      BINARY NUM

Field   3:  `val`
Catalog:    `def`
Database:   `test`
Table:      `test`
Org_table:  `test`
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 19
Decimals:   0
Flags:      UNSIGNED NUM

+---------------------+-------------------------+---------------------+
| IFNULL(val, 0)      | IF(val IS NULL, 0, val) | val                 |
+---------------------+-------------------------+---------------------+
| 9223372036854775806 |     9223372036854775806 | 9223372036854775806 |
| 9223372036854775807 |     9223372036854775807 | 9223372036854775807 |
| 9223372036854775807 |    -9223372036854775808 | 9223372036854775808 |
| 9223372036854775807 |    -9223372036854775807 | 9223372036854775809 |
|                   0 |                       0 |                NULL |
+---------------------+-------------------------+---------------------+
5 rows in set, 2 warnings (0.00 sec)

Note the negative sign above and BINARY NUM instead of UNSIGNED NUM for both IFNULL and (!) IF.

I think http://bugs.mysql.com/bug.php?id=33851 refers to the same bug

This problem still exist in version 5.6.24.
However, the if-statement workaround does not work here.
Using the example given in the issue:

create table test (val BIGINT UNSIGNED);
insert into test set val=18446744073709551614;
insert into test set val=18446744073709551613;

select IFNULL(null, val), IFNULL(val, 0), IF(val IS NULL, 0, val), val from test;

The select statement will have the following result:

IFNULL(null, val)  IFNULL(val, 0)       IF(val IS NULL, 0, val)  val                   
-----------------  -------------------  -----------------------  --------------------  
-2                 9223372036854775807  -2                       18446744073709551614  
-3                 9223372036854775807  -3                       18446744073709551613  

The coalesce function and equivalent case-expression will produce the same truncation error.

The only workaround seems to be this:
select x'FFFFFFFFFFFFFFFF' & IFNULL(null, val) from test;

I find it a bit strange that this issue has severity Non-critical. Silently truncating data is a big deal.
Good news is that this seems to work fine in 5.7.