bug: the range of unsigned should be determined

[davidshiz]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

the range of unsigned ( INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT ) should be determined,
and when INSERT the unsigned data out of range, should return the correct prompt message.

mysql> create table test(
id1 TINYINT UNSIGNED,
id2 SMALLINT UNSIGNED,
id3 INT UNSIGNED,
id4 BIGINT UNSIGNED
);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test values(255,65535,4294967295,18446744073709551615);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(253,65533,4294967293,18446744073709551613);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+-------+------------+----------------------+
| id1  | id2   | id3        | id4                  |
+------+-------+------------+----------------------+
|  127 | 32767 | 2147483647 | 18446744073709551615 |
|  127 | 32767 | 2147483647 | 18446744073709551613 |
+------+-------+------------+----------------------+
2 rows in set (0.00 sec)

Expected behavior

mysql> create table test(
id1 TINYINT UNSIGNED,
id2 SMALLINT UNSIGNED,
id3 INT UNSIGNED,
id4 BIGINT UNSIGNED
);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(255,65535,4294967295,18446744073709551615);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(253,65533,4294967293,18446744073709551613);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+-------+------------+----------------------+
| id1  | id2   | id3        | id4                  |
+------+-------+------------+----------------------+
|  255 | 65535 | 4294967295 | 18446744073709551615 |
|  253 | 65533 | 4294967293 | 18446744073709551613 |
+------+-------+------------+----------------------+
2 rows in set (0.00 sec)

How To Reproduce

create table test(id1 TINYINT UNSIGNED,id2 SMALLINT UNSIGNED,id3 INT UNSIGNED,id4 BIGINT UNSIGNED);
insert into test values(255,65535,4294967295,18446744073709551615);
insert into test values(253,65533,4294967293,18446744073709551613);
select * from test;

Environment

root@ub01:~# /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: 863cebc58
        Last commit time: Date:   Wed Dec 7 14:16:34 2022 +0800
        Build time: Date: Mon Dec 19 14:29:02 CST 2022
root@ub01:~# cat /etc/os-release
NAME="Ubuntu"
VERSION="20.04.5 LTS (Focal Fossa)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 20.04.5 LTS"
VERSION_ID="20.04"

Are you interested in submitting a PR to solve the problem?

• Yes, I will!

[hustjieke]

MySQL will throw TYPE_WARN_OUT_OF_RANGE when signed/unsigned data out of range:

type_conversion_status Field_tiny::store(longlong nr, bool unsigned_val)
{
  ASSERT_COLUMN_MARKED_FOR_WRITE;
  type_conversion_status error= TYPE_OK;

  if (unsigned_flag)
  {
    if (nr < 0 && !unsigned_val)
    {
      *ptr= 0;
      set_warning(Sql_condition::SL_WARNING, ER_WARN_DATA_OUT_OF_RANGE, 1);
      error= TYPE_WARN_OUT_OF_RANGE;
    }
    else if ((ulonglong) nr > (ulonglong) 255)
    {
      *ptr= (char) 255;
      set_warning(Sql_condition::SL_WARNING, ER_WARN_DATA_OUT_OF_RANGE, 1);
      error= TYPE_WARN_OUT_OF_RANGE;
    }
    else
      *ptr=(char) nr;
  }
  else
  {
    if (nr < 0 && unsigned_val)
      nr= 256;                                    // Generate overflow
    if (nr < -128)
    {
      *ptr= (char) -128;
      set_warning(Sql_condition::SL_WARNING, ER_WARN_DATA_OUT_OF_RANGE, 1);
      error= TYPE_WARN_OUT_OF_RANGE;
    }
    else if (nr > 127)
    {
      *ptr=127;
      set_warning(Sql_condition::SL_WARNING, ER_WARN_DATA_OUT_OF_RANGE, 1);
      error= TYPE_WARN_OUT_OF_RANGE;
    }
    else
      *ptr=(char) nr;
  }
  return error;
}

For tianmu engine, the data will be truncated:

      case MYSQL_TYPE_TINY: {
        int64_t v = f->val_int();
        if (v > TIANMU_TINYINT_MAX)
          v = TIANMU_TINYINT_MAX;
        else if (v < TIANMU_TINYINT_MIN)
          v = TIANMU_TINYINT_MIN;
        *reinterpret_cast<int64_t *>(ptr) = v;
        ptr += sizeof(int64_t);
      } break;

[hustjieke]

Out-of-Range and Overflow Handling

• If strict SQL mode is enabled, MySQL rejects the out-of-range value with an error, and the insert fails, in accordance with the SQL standard.

• If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the column data type range and stores the resulting value instead.

For more infos:
https://dev.mysql.com/doc/refman/8.0/en/out-of-range-and-overflow.html

[hustjieke]

All unsigned boundary values ​​are supported in my demo feature, I used it to verify my idea and it works well for now, I'll pr the code as soon as possible!

I'll write the design docs to desc how to impl all boundary values on integer types(including from tynyint to bigint).
My demo feature branch:
https://github.com/hustjieke/stonedb/tree/feat_unsigned_support

[davidshiz]

similar to #1206

CREATE TABLE `test` (
  `id1` tinyint(3) unsigned DEFAULT NULL,
  `id2` smallint(5) unsigned DEFAULT NULL,
  `id3` int(10) unsigned DEFAULT NULL,
  `id4` bigint(20) unsigned DEFAULT NULL
)DEFAULT CHARSET=utf8mb4;

insert into test values (255,65535,4294967295,18446744073709551615);
insert into test values (255,65535,4294967295,9223372036854775807);
insert into test values (129,32769,2147483647,9223372036854775808);

mysql> select * from test;
+------+-------+------------+----------------------+
| id1  | id2   | id3        | id4                  |
+------+-------+------------+----------------------+
|  127 | 32767 | 2147483647 | 18446744073709551615 |
|  127 | 32767 | 2147483647 |  9223372036854775807 |
|  127 | 32767 | 2147483647 |                 NULL |
+------+-------+------------+----------------------+
3 rows in set (0.00 sec)

[hustjieke]

Tianmu engine deal with unsigned data with different way：

• With tianmu_insert_delayed=ON and tianmu_enable_rowstore=ON/OFF mode, the integer data will be truncated if unsigned integer is bigger than max signed integer

void Engine::EncodeRecord {
      switch (f->type()) {
      case MYSQL_TYPE_TINY: {
        int64_t v = f->val_int();
        if (v > TIANMU_TINYINT_MAX)
          v = TIANMU_TINYINT_MAX;
        else if (v < TIANMU_TINYINT_MIN)
          v = TIANMU_TINYINT_MIN;
        *reinterpret_cast<int64_t *>(ptr) = v;
        ptr += sizeof(int64_t);
      } break;
  ...
  ...
}

call stack:

Tianmu::core::Engine::EncodeRecord(Tianmu::core::Engine * const this, const std::string & table_path, int table_id, Field ** field, size_t col, size_t blobs, std::unique_ptr<char [], std::default_delete<char []> > & buf, uint32_t & size) (\github\stonedb\storage\tianmu\core\engine.cpp:464)
Tianmu::core::Engine::InsertMemRow(Tianmu::core::Engine * const this, const std::string & table_path, std::shared_ptr<Tianmu::core::TableShare> & share, TABLE * table) (\github\stonedb\storage\tianmu\core\engine.cpp:1480)
Tianmu::core::Engine::InsertRow(Tianmu::core::Engine * const this, const std::string & table_path, Tianmu::core::Transaction * trans_, TABLE * table, std::shared_ptr<Tianmu::core::TableShare> & share) (\github\stonedb\storage\tianmu\core\engine.cpp:1491)
Tianmu::handler::ha_tianmu::write_row(Tianmu::handler::ha_tianmu * const this, uchar * buf) (\github\stonedb\storage\tianmu\handler\ha_tianmu.cpp:466)
handler::ha_write_row(handler * const this, uchar * buf) (\github\stonedb\sql\handler.cc:8189)
...
...

• With tianmu_insert_delayed=OFF mode, Tianmu engine do not truncate if the integer is out of range when encording integer data. The data getted from field stored into ValueCache directly.

void TianmuTable::Field2VC(Field *f, loader::ValueCache &vc, size_t col) {
  ...
  ...
  switch (f->type()) {
    case MYSQL_TYPE_TINY:
    case MYSQL_TYPE_SHORT:
    case MYSQL_TYPE_LONG:
    case MYSQL_TYPE_INT24:
    case MYSQL_TYPE_LONGLONG:
    case MYSQL_TYPE_BIT: {
      int64_t value = f->val_int();
      if (m_attrs[col]->GetIfAutoInc() && value == 0)
        // Value of auto inc column was not assigned by user
        *reinterpret_cast<int64_t *>(vc.Prepare(sizeof(int64_t))) = m_attrs[col]->AutoIncNext();
      else
        *reinterpret_cast<int64_t *>(vc.Prepare(sizeof(int64_t))) = value;
      vc.ExpectedSize(sizeof(int64_t));

call stack:

Tianmu::core::TianmuTable::Field2VC(Tianmu::core::TianmuTable * const this, Field * f, Tianmu::loader::ValueCache & vc, size_t col) (\github\stonedb\storage\tianmu\core\tianmu_table.cpp:603)
Tianmu::core::TianmuTable::Insert(Tianmu::core::TianmuTable * const this, TABLE * table) (\github\stonedb\storage\tianmu\core\tianmu_table.cpp:734)
Tianmu::core::Engine::InsertRow(Tianmu::core::Engine * const this, const std::string & table_path, Tianmu::core::Transaction * trans_, TABLE * table, std::shared_ptr<Tianmu::core::TableShare> & share) (\github\stonedb\storage\tianmu\core\engine.cpp:1499)
Tianmu::handler::ha_tianmu::write_row(Tianmu::handler::ha_tianmu * const this, uchar * buf) (\github\stonedb\storage\tianmu\handler\ha_tianmu.cpp:466)
handler::ha_write_row(handler * const this, uchar * buf) (\github\stonedb\sql\handler.cc:8189)
...
...