Bug #79906 Implicit and Explicit convert from bigint unsigned to signed give different val

• Submitted: 11 Jan 2016 3:55
• Modified: 13 Jan 2016 7:25
• Reporter: Su Dylan
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8, 5.6.28, 5.7.10, 5.5.48
• OS: Any
• CPU Architecture: Any

[11 Jan 2016 3:55] Su Dylan

Description:
Output:
=====
mysql> create table t1(c1 bigint);
Query OK, 0 rows affected (0.46 sec)

mysql> insert into t1 values(9223372036854775808);
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> select c1, convert(9223372036854775808, signed) from t1;
+---------------------+--------------------------------------+
| c1                  | convert(9223372036854775808, signed) |
+---------------------+--------------------------------------+
| 9223372036854775807 |                 -9223372036854775808 |
+---------------------+--------------------------------------+
1 row in set (0.00 sec)

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

Problem:
=====
Implicit and Explicit convert from bigint unsigned to signed gives different value.
Truncation is expected for convert(9223372036854775808, signed), and result 9223372036854775807  is expected.

How to repeat:

set sql_mode='';
drop table if exists t1;
create table t1(c1 bigint);
insert into t1 values(9223372036854775808);
select c1, convert(9223372036854775808, signed) from t1;

Suggested fix:
Truncation is expected for convert(9223372036854775808, signed), and result 9223372036854775807  is expected.

[13 Jan 2016 7:25] Umesh Shastry

Hello Su Dylan,

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

Thanks,
Umesh

[13 Jan 2016 9:19] Tor Didriksen

Posted by developer:
 
Even more inconsistencies:
select convert('9223372036854775808', signed) 
| Warning | 1105 | Cast to signed converted positive out-of-range integer to it's negative complement |

1105 is 'unknown error'

[13 Jan 2016 9:21] Umesh Shastry

-- 5.5.48 - affected

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.48: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.48 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(c1 bigint);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(9223372036854775808);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select c1, convert(9223372036854775808, signed) from t1;
+---------------------+--------------------------------------+
| c1                  | convert(9223372036854775808, signed) |
+---------------------+--------------------------------------+
| 9223372036854775807 |                 -9223372036854775808 |
+---------------------+--------------------------------------+
1 row in set (0.00 sec)