Description:
Output:
=======
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.02 sec)
mysql> create table t1(a int, b bigint unsigned);
insert into t1 (a, b) valQuery OK, 0 rows affected (0.00 sec)
mysql> insert into t1 (a, b) values (1,4572794622775114594), (2,18196094287899841997),(3,11120436154190595086);
t (case t1.a when 0 then 0 else t1.b end), Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select (case t1.a when 0 then 0 else t1.b end), cast(t1.b as signed) from t1;
+-----------------------------------------+----------------------+
| (case t1.a when 0 then 0 else t1.b end) | cast(t1.b as signed) |
+-----------------------------------------+----------------------+
| 4572794622775114594 | 4572794622775114594 |
| -250649785809709619 | -250649785809709619 |
| -7326307919518956530 | -7326307919518956530 |
+-----------------------------------------+----------------------+
3 rows in set (0.01 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc |
+-----------+
1 row in set (0.00 sec)
Problem:
========
Bigint unsigned column value in CASE WHEN operator is converted to bigint signed, which is unexpected.
How to repeat:
drop table if exists t1;
create table t1(a int, b bigint unsigned);
insert into t1 (a, b) values (1,4572794622775114594), (2,18196094287899841997),(3,11120436154190595086);
select (case t1.a when 0 then 0 else t1.b end), cast(t1.b as signed) from t1;
Suggested fix:
(case t1.a when 0 then 0 else t1.b end) should return the origin data type (BIGINT UNSIGNED).
