Bug #79926 Func IFNULL truncate unsigned param to signed range
Submitted: 12 Jan 2016 8:26 Modified: 19 Jan 2016 3:41
Reporter: Su Dylan Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.8, 5.5.48, 5.6.28, 5.7.10 OS:Any
Assigned to: CPU Architecture:Any

[12 Jan 2016 8:26] Su Dylan
Description:
Output:
=====
mysql> select CONVERT( -1, UNSIGNED ), IFNULL( CONVERT( -1, UNSIGNED ), 0 );
+-------------------------+--------------------------------------+
| CONVERT( -1, UNSIGNED ) | IFNULL( CONVERT( -1, UNSIGNED ), 0 ) |
+-------------------------+--------------------------------------+
|    18446744073709551615 |                  9223372036854775807 |
+-------------------------+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
=====
18446744073709551615 is expected for both

How to repeat:
select CONVERT( -1, UNSIGNED ), IFNULL( CONVERT( -1, UNSIGNED ), 0 );

Suggested fix:
18446744073709551615 is returned for both
[12 Jan 2016 9:02] MySQL Verification Team
Hello Su Dylan,

Thank you for the report and test case.
Observed that 5.5.48/5.6.28/5.7.10 are affected.

Thanks,
Umesh
[13 Jan 2016 13:32] Tor Didriksen
Posted by developer:
 
You need to convert the zero to unsigned as well.

mysql> select CONVERT( -1, UNSIGNED ), IFNULL( CONVERT( -1, UNSIGNED ), convert(0, unsigned) );
+-------------------------+---------------------------------------------------------+
| CONVERT( -1, UNSIGNED ) | IFNULL( CONVERT( -1, UNSIGNED ), convert(0, unsigned) ) |
+-------------------------+---------------------------------------------------------+
|    18446744073709551615 |                                    18446744073709551615 |
+-------------------------+---------------------------------------------------------+
[18 Jan 2016 17:36] Erlend Dahl
Posted by developer:

[13 Jan 2016 5:32] Tor Didriksen

You need to convert the zero to unsigned as well.

mysql> select CONVERT( -1, UNSIGNED ), IFNULL( CONVERT( -1, UNSIGNED ),
convert(0, unsigned) );
+-------------------------+---------------------------------------------------------+
| CONVERT( -1, UNSIGNED ) | IFNULL( CONVERT( -1, UNSIGNED ), convert(0,unsigned) ) |
+-------------------------+---------------------------------------------------------+
|    18446744073709551615 | 18446744073709551615 |
+-------------------------+---------------------------------------------------------+
[19 Jan 2016 3:41] Su Dylan
I know how to bypass the issue.
However, for a user, this behavior is hard to understand.
It looks like that I get 'C' from options 'A' and 'B'.