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

Bug #33859	IFNULL broken for unsigned bigint values
Submitted:	14 Jan 2008 11:57	Modified:	14 Jan 2008 13:17
Reporter:	Florian Wesch
Status:	Verified
Category:	Server: Types	Severity:	S3 (Non-critical)
Version:	5.0.54, 5.0.45	OS:	Linux (debian sid)
Assigned to:	Gleb Shchepa	Target Version:
Tags:	bigint ifnull
Triage:	Triaged: D2 (Serious)

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