Description:
Storing 18446744073709551615 (the max unsigned value) into bigint signed column:
- gives warning if 18446744073709551615 is passed through a literal (good)
- gives no warning if 18446744073709551615 is passed through the content of another, bigint unsigned, column.
Moreover, inserted values differ (but this is less of an issue: if the warning was given in the second case, in strict mode it would make both queries fail identically).
My test is with UPDATE. INSERT SELECT, INSERT VALUES ON DUPLICATE KEY UPDATE should be tested too, as they also feed column values into other columns.
How to repeat:
# To have no query fail
set sql_mode='';
create table t1(u bigint unsigned, i bigint signed);
insert into t1 values(18446744073709551615,0);
select * from t1;
# warning
update t1 set i=18446744073709551615;
select * from t1;
# no warning when too big value comes from column,
# and different value is inserted.
update t1 set i=u;
select * from t1;
update t1, t1 as t2 set t1.i=t2.u;
select * from t1;
drop table t1;
Result:
set sql_mode='';
Warnings:
Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
create table t1(u bigint unsigned, i bigint signed);
insert into t1 values(18446744073709551615,0);
select * from t1;
u i
18446744073709551615 0
update t1 set i=18446744073709551615;
Warnings:
Warning 1264 Out of range value for column 'i' at row 1
select * from t1;
u i
18446744073709551615 9223372036854775807
update t1 set i=u;
select * from t1;
u i
18446744073709551615 -1
update t1, t1 as t2 set t1.i=t2.u;
select * from t1;
u i
18446744073709551615 -1
drop table t1;
