Description:
mysql> drop table t1;
mysql> create table t1(a bigint unsigned);
mysql> insert into t1 values (18446744073709551615);
mysql> select case when true then a end from t1;
+---------------------------+
| case when true then a end |
+---------------------------+
| 18446744073709551615 |
+---------------------------+
1 row in set (0.00 sec)
it is good for now, but when we type this :
mysql> select case when true then a else null end from t1;
+-------------------------------------+
| case when true then a else null end |
+-------------------------------------+
| -1 |
+-------------------------------------+
so weird... let us digg into this.
connect the mysql server with --column-type-info option in client and check the metainfo
mysql> select case when true then a end from t1;
Field 1: `case when true then a end`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: LONGLONG
Collation: binary (63)
Length: 20
Max_length: 20
Decimals: 0
Flags: UNSIGNED BINARY NUM //yeah, unsigned flag
+---------------------------+
| case when true then a end |
+---------------------------+
| 18446744073709551615 |
+---------------------------+
1 row in set (0.00 sec)
mysql> select case when true then a else null end from t1;
Field 1: `case when true then a else null end`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: LONGLONG
Collation: binary (63)
Length: 21
Max_length: 2
Decimals: 0
Flags: BINARY NUM //no unsigned flag
+-------------------------------------+
| case when true then a else null end |
+-------------------------------------+
| -1 |
+-------------------------------------+
1 row in set (0.01 sec)
How to repeat:
drop table t1;
create table t1(a bigint unsigned);
insert into t1 values (18446744073709551615);
select case when true then a end from t1;
select case when true then a else null end from t1;
Suggested fix:
select case when true then a end from t1;
select case when true then a else null end from t1;
both return same results
