Bug #99639 Wrong behavior between insert and insert...select
Submitted: 20 May 2020 2:47 Modified: 20 May 2020 13:39
Reporter: Roger Lei (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.19 OS:Ubuntu (Ubuntu 18.04.4 LTS)
Assigned to: CPU Architecture:Any

[20 May 2020 2:47] Roger Lei
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?
[20 May 2020 12:56] MySQL Verification Team
Hi Mr. lei,

Thank you for your bug report.

If I understand you correctly, the only thing that you are reporting here is that we should use LLONG_MAX instead of ULLONG_MAX in both places.

Am I correct ????
[20 May 2020 13:36] Roger Lei
Hi Mr. Milivojevic,
Yes, you are right.
[20 May 2020 13:39] MySQL Verification Team
Thank you Mr. Lei,

Verified as reported.