Description:
1. By insert statement, we pass ULLONG_MAX(18446744073709551615) to signed bigint column 'big' of table t2, actually, we got LLONG_MAX(9223372036854775807), refer to step 8 of "How to repeat" below.
2. By insert...select statement, we pass ULLONG_MAX(18446744073709551615) from unsigned bigint column 'big' of table t1 to signed bigint column 'big' of table t2, actually, we go -1(ULLONG_MAX), refer to step 11 of "How to repeat" below.
How to repeat:
1. log on mysql.
2. create test database and use it:
mysql> create database test;
mysql> use test;
3. change sql_mode:
mysql> set sql_mode='NO_ENGINE_SUBSTITUTION';
4. create source table t1:
mysql> create table t1(a int(11) not null default '1', big bigint(20) unsigned not null, pk int(11) not null auto_increment, primary key (`pk`)) engine=InnoDB;
5. create destination table t2:
mysql> create table t2(a int(11) not null default '1', big bigint(20) not null, pk int(11) not null auto_increment, primary key (`pk`)) engine=InnoDB;
6. make source row in table t1:
mysql> insert into t1(big) value(18446744073709551615);
7. check the result:
mysql> select * from t1;
+---+----------------------+----+
| a | big | pk |
+---+----------------------+----+
| 1 | 18446744073709551615 | 1 |
+---+----------------------+----+
1 row in set (0.00 sec)
8. insert row into table t2:
mysql> insert into t2(big) (select 18446744073709551615);
9. check the result:
mysql> select * from t2;
+---+---------------------+----+
| a | big | pk |
+---+---------------------+----+
| 1 | 9223372036854775807 | 1 |
+---+---------------------+----+
1 row in set (0.01 sec)
10. clear rows in table t2:
mysql> truncate table t2;
11. copy rows from table t1 to table t2:
mysql> insert into t2 (select * from t1);
12. check the result:
mysql> select * from t2;
+---+------+----+
| a | big | pk |
+---+------+----+
| 1 | -1 | 1 |
+---+------+----+
1 row in set (0.01 sec)
Suggested fix:
1. Insert statement, in file sql/field.cc, it checks the unsigned flag and transform from ULLONG_MAX to LLONG_MAX to prevent overflow taken place:
type_conversion_status Field_longlong::store(longlong nr, bool unsigned_val) {
...
if (nr < 0) // Only possible error
{
/*
if field is unsigned and value is signed (< 0) or
if field is signed and value is unsigned we have an overflow
*/
if (unsigned_flag != unsigned_val) {
nr = unsigned_flag ? (ulonglong)0 : (ulonglong)LLONG_MAX; //!!!here
set_warning(Sql_condition::SL_WARNING, ER_WARN_DATA_OUT_OF_RANGE, 1);
error = TYPE_WARN_OUT_OF_RANGE;
}
}
...
}
2. Insert...select statement, in file sql/field_conv.cc, it does not check the unsigned flag, copy the ULLONG_MAX value directly, and overflow taken place:
type_conversion_status field_conv(Field *to, Field *from) {
...
if (to->real_type() == from->real_type() &&
!((is_blob_type(to)) && to->table->copy_blobs) &&
to->charset() == from->charset() && to_type != MYSQL_TYPE_GEOMETRY) {
...
if (to->pack_length() == from->pack_length() &&
!(to->flags & UNSIGNED_FLAG && !(from->flags & UNSIGNED_FLAG)) &&
to->real_type() != MYSQL_TYPE_ENUM &&
to->real_type() != MYSQL_TYPE_SET &&
to->real_type() != MYSQL_TYPE_BIT &&
(!to->is_temporal_with_time() || to->decimals() == from->decimals()) &&
(to->real_type() != MYSQL_TYPE_NEWDECIMAL ||
(to->field_length == from->field_length &&
(((Field_num *)to)->dec == ((Field_num *)from)->dec))) &&
to->table->s->db_low_byte_first == from->table->s->db_low_byte_first &&
(!(to->table->in_use->variables.sql_mode &
(MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | MODE_INVALID_DATES)) ||
(to->type() != MYSQL_TYPE_DATE && to->type() != MYSQL_TYPE_DATETIME &&
(!to->table->in_use->variables.explicit_defaults_for_timestamp ||
to->type() != MYSQL_TYPE_TIMESTAMP))) &&
(from->real_type() != MYSQL_TYPE_VARCHAR)) { // Identical fields
// to->ptr==from->ptr may happen if one does 'UPDATE ... SET x=x'
memmove(to->ptr, from->ptr, to->pack_length()); //!!!here
return TYPE_OK;
}
}
...
}
Why insert..select statement does not check the unsigned flag to prevent overflow taken place just like insert statement?
We give the two statement the same input(ULLONG_MAX), but get the diffrent output(LLONG_MAX and -1), is this intentional design or a coding bug?
Description: 1. By insert statement, we pass ULLONG_MAX(18446744073709551615) to signed bigint column 'big' of table t2, actually, we got LLONG_MAX(9223372036854775807), refer to step 8 of "How to repeat" below. 2. By insert...select statement, we pass ULLONG_MAX(18446744073709551615) from unsigned bigint column 'big' of table t1 to signed bigint column 'big' of table t2, actually, we go -1(ULLONG_MAX), refer to step 11 of "How to repeat" below. How to repeat: 1. log on mysql. 2. create test database and use it: mysql> create database test; mysql> use test; 3. change sql_mode: mysql> set sql_mode='NO_ENGINE_SUBSTITUTION'; 4. create source table t1: mysql> create table t1(a int(11) not null default '1', big bigint(20) unsigned not null, pk int(11) not null auto_increment, primary key (`pk`)) engine=InnoDB; 5. create destination table t2: mysql> create table t2(a int(11) not null default '1', big bigint(20) not null, pk int(11) not null auto_increment, primary key (`pk`)) engine=InnoDB; 6. make source row in table t1: mysql> insert into t1(big) value(18446744073709551615); 7. check the result: mysql> select * from t1; +---+----------------------+----+ | a | big | pk | +---+----------------------+----+ | 1 | 18446744073709551615 | 1 | +---+----------------------+----+ 1 row in set (0.00 sec) 8. insert row into table t2: mysql> insert into t2(big) (select 18446744073709551615); 9. check the result: mysql> select * from t2; +---+---------------------+----+ | a | big | pk | +---+---------------------+----+ | 1 | 9223372036854775807 | 1 | +---+---------------------+----+ 1 row in set (0.01 sec) 10. clear rows in table t2: mysql> truncate table t2; 11. copy rows from table t1 to table t2: mysql> insert into t2 (select * from t1); 12. check the result: mysql> select * from t2; +---+------+----+ | a | big | pk | +---+------+----+ | 1 | -1 | 1 | +---+------+----+ 1 row in set (0.01 sec) Suggested fix: 1. Insert statement, in file sql/field.cc, it checks the unsigned flag and transform from ULLONG_MAX to LLONG_MAX to prevent overflow taken place: type_conversion_status Field_longlong::store(longlong nr, bool unsigned_val) { ... if (nr < 0) // Only possible error { /* if field is unsigned and value is signed (< 0) or if field is signed and value is unsigned we have an overflow */ if (unsigned_flag != unsigned_val) { nr = unsigned_flag ? (ulonglong)0 : (ulonglong)LLONG_MAX; //!!!here set_warning(Sql_condition::SL_WARNING, ER_WARN_DATA_OUT_OF_RANGE, 1); error = TYPE_WARN_OUT_OF_RANGE; } } ... } 2. Insert...select statement, in file sql/field_conv.cc, it does not check the unsigned flag, copy the ULLONG_MAX value directly, and overflow taken place: type_conversion_status field_conv(Field *to, Field *from) { ... if (to->real_type() == from->real_type() && !((is_blob_type(to)) && to->table->copy_blobs) && to->charset() == from->charset() && to_type != MYSQL_TYPE_GEOMETRY) { ... if (to->pack_length() == from->pack_length() && !(to->flags & UNSIGNED_FLAG && !(from->flags & UNSIGNED_FLAG)) && to->real_type() != MYSQL_TYPE_ENUM && to->real_type() != MYSQL_TYPE_SET && to->real_type() != MYSQL_TYPE_BIT && (!to->is_temporal_with_time() || to->decimals() == from->decimals()) && (to->real_type() != MYSQL_TYPE_NEWDECIMAL || (to->field_length == from->field_length && (((Field_num *)to)->dec == ((Field_num *)from)->dec))) && to->table->s->db_low_byte_first == from->table->s->db_low_byte_first && (!(to->table->in_use->variables.sql_mode & (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | MODE_INVALID_DATES)) || (to->type() != MYSQL_TYPE_DATE && to->type() != MYSQL_TYPE_DATETIME && (!to->table->in_use->variables.explicit_defaults_for_timestamp || to->type() != MYSQL_TYPE_TIMESTAMP))) && (from->real_type() != MYSQL_TYPE_VARCHAR)) { // Identical fields // to->ptr==from->ptr may happen if one does 'UPDATE ... SET x=x' memmove(to->ptr, from->ptr, to->pack_length()); //!!!here return TYPE_OK; } } ... } Why insert..select statement does not check the unsigned flag to prevent overflow taken place just like insert statement? We give the two statement the same input(ULLONG_MAX), but get the diffrent output(LLONG_MAX and -1), is this intentional design or a coding bug?