Bug #79930 Func IF() truncates unsigned value to signed

• Submitted: 12 Jan 2016 9:11
• Modified: 9 Dec 2019 21:57
• Reporter: Su Dylan
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8, 5.5.48, 5.6.28, 5.7.10
• OS: Any
• CPU Architecture: Any

[12 Jan 2016 9:11] Su Dylan

Description:
Output:
=====
mysql> select if(true, 18446744073709551615, 0 );
+------------------------------------+
| if(true, 18446744073709551615, 0 ) |
+------------------------------------+
|                                 -1 |
+------------------------------------+
1 row in set (0.00 sec)

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

Problem:
=====
18446744073709551615 is expected.

How to repeat:
select if(true, 18446744073709551615, 0 );

Suggested fix:
18446744073709551615 is returned.

[12 Jan 2016 10:49] 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:35] Tor Didriksen

Posted by developer:
 
You need to convert the zero to unsigned:

mysql> select if(true, 18446744073709551615, convert(0, unsigned) );
+-------------------------------------------------------+
| if(true, 18446744073709551615, convert(0, unsigned) ) |
+-------------------------------------------------------+
|                                  18446744073709551615 |
+-------------------------------------------------------+

[18 Jan 2016 17:45] Erlend Dahl

Posted by developer:

[13 Jan 2016 5:35] Tor Didriksen 

You need to convert the zero to unsigned:

mysql> select if(true, 18446744073709551615, convert(0, unsigned) );
+-------------------------------------------------------+
| if(true, 18446744073709551615, convert(0, unsigned) ) |
+-------------------------------------------------------+
|                                  18446744073709551615 |
+-------------------------------------------------------+

 (TDIDRIKS)

You need to convert the zero to unsigned:

mysql> select if(true, 18446744073709551615, convert(0, unsigned) );
+-------------------------------------------------------+
| if(true, 18446744073709551615, convert(0, unsigned) ) |
+-------------------------------------------------------+
|                                  18446744073709551615 |
+-------------------------------------------------------+

[19 Jan 2016 3:42] 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'.

[9 Dec 2019 21:57] Roy Lyseng

Posted by developer:
 
Fixed in 8.0.18