Bug #79928 Func COALEASE truncates unsigned param to signed

• Submitted: 12 Jan 2016 9:03
• Modified: 19 Jan 2016 3:41
• Reporter: Su Dylan
• Status: Not a Bug
• 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:03] Su Dylan

Description:
Output:
=====
mysql> SELECT COALESCE( 16952111947375968256, 0 ) ;
+-------------------------------------+
| COALESCE( 16952111947375968256, 0 ) |
+-------------------------------------+
|                 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:
=====
The original unsigned value is expected.

How to repeat:
SELECT COALESCE( 16952111947375968256, 0 ) ;

Suggested fix:
The original unsigned value is returned.

[12 Jan 2016 9:37] 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:37] Tor Didriksen

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

mysql> SELECT COALESCE( 16952111947375968256, convert(0, unsigned) ) ;
+--------------------------------------------------------+
| COALESCE( 16952111947375968256, convert(0, unsigned) ) |
+--------------------------------------------------------+
|                                   16952111947375968256 |
+--------------------------------------------------------+

[18 Jan 2016 17:37] Erlend Dahl

Posted by developer:

[13 Jan 2016 5:37] Tor Didriksen

You need to convert the zero to unsigned:

mysql> SELECT COALESCE( 16952111947375968256, convert(0, unsigned) ) ;
+--------------------------------------------------------+
| COALESCE( 16952111947375968256, convert(0, unsigned) ) |
+--------------------------------------------------------+
|                                   16952111947375968256 |
+--------------------------------------------------------+

[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'.