pingcap / pingcap/tidb

Data type Value conversion difference in TIDB and mysql

Open
#33,642 0 comments 0 reactions 0 assignees View on GitHub
severity/moderate sig/execution type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

```sql

SET sql_mode = 'NO_ENGINE_SUBSTITUTION';

drop table t9 ;
create table t9
(
c1 tinyint, c2 smallint, c3 mediumint, c4 int,
c5 integer, c6 bigint, c7 float, c8 double,
c9 double precision, c10 real, c12 numeric(8, 4),
primary key(c1)
) engine = InnoDB ;

insert into t9
(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12)
values
('1.11111111111111111111e+50','1.11111111111111111111e+50','1.11111111111111111111e+50', '1.11111111111111111111e+50','1.11111111111111111111e+50', '1.11111111111111111111e+50', '1.11111111111111111111e+50','1.11111111111111111111e+50','1.11111111111111111111e+50','1.11111111111111111111e+50','1.11111111111111111111e+50' );
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 from t9;
delete from t9 ;

insert into t9
(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12)
values
('-9223372036854775808','-9223372036854775808','-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808' );
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 from t9;
delete from t9 ;

insert into t9
(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
values
(1.11111111111111111111e+50,1.11111111111111111111e+50,1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50,1.11111111111111111111e+50 );
select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 from t9;
delete from t9;

```
### 2. What did you expect to see? (Required)
```sql
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> drop table t9 ;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t9
-> (
-> c1 tinyint, c2 smallint, c3 mediumint, c4 int,
-> c5 integer, c6 bigint, c7 float, c8 double,
-> c9 double precision, c10 real, c12 numeric(8, 4),
-> primary key(c1)
-> ) engine = InnoDB ;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t9
-> (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12)
-> values
-> ('1.11111111111111111111e+50','1.11111111111111111111e+50','1.11111111111111111111e+50', '1.11111111111111111111e+50','1.11111111111111111111e+50', '1.11111111111111111111e+50', '1.11111111111111111111e+50','1.11111111111111111111e+50','1.11111111111111111111e+50','1.11111111111111111111e+50','1.11111111111111111111e+50' );
Query OK, 1 row affected, 8 warnings (0.00 sec)

mysql> select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 from t9;
+-----+-------+---------+------------+------------+---------------------+------------+----------------------+----------------------+----------------------+-----------+
| c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 | c12 |
+-----+-------+---------+------------+------------+---------------------+------------+----------------------+----------------------+----------------------+-----------+
| 127 | 32767 | 8388607 | 2147483647 | 2147483647 | 9223372036854775807 | 3.40282e38 | 1.111111111111111e50 | 1.111111111111111e50 | 1.111111111111111e50 | 9999.9999 |
+-----+-------+---------+------------+------------+---------------------+------------+----------------------+----------------------+----------------------+-----------+
1 row in set (0.00 sec)

mysql> delete from t9 ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t9
-> (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12)
-> values
-> ('-9223372036854775808','-9223372036854775808','-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808' );
Query OK, 1 row affected, 6 warnings (0.00 sec)

mysql> select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 from t9;
+------+--------+----------+-------------+-------------+----------------------+-------------+-----------------------+-----------------------+-----------------------+------------+
| c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 | c12 |
+------+--------+----------+-------------+-------------+----------------------+-------------+-----------------------+-----------------------+-----------------------+------------+
| -128 | -32768 | -8388608 | -2147483648 | -2147483648 | -9223372036854775808 | -9.22337e18 | -9.223372036854776e18 | -9.223372036854776e18 | -9.223372036854776e18 | -9999.9999 |
+------+--------+----------+-------------+-------------+----------------------+-------------+-----------------------+-----------------------+-----------------------+------------+
1 row in set (0.00 sec)

mysql> delete from t9 ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t9
-> (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
-> values
-> (1.11111111111111111111e+50,1.11111111111111111111e+50,1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50,1.11111111111111111111e+50 );
Query OK, 1 row affected, 8 warnings (0.01 sec)

mysql> select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 from t9;
+-----+-------+---------+------------+------------+---------------------+------------+----------------------+----------------------+----------------------+-----------+
| c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 | c12 |
+-----+-------+---------+------------+------------+---------------------+------------+----------------------+----------------------+----------------------+-----------+
| 127 | 32767 | 8388607 | 2147483647 | 2147483647 | 9223372036854775807 | 3.40282e38 | 1.111111111111111e50 | 1.111111111111111e50 | 1.111111111111111e50 | 9999.9999 |
+-----+-------+---------+------------+------------+---------------------+------------+----------------------+----------------------+----------------------+-----------+
1 row in set (0.00 sec)

mysql> delete from t9;
Query OK, 1 row affected (0.00 sec)
```

### 3. What did you see instead (Required)
```sql
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table t9 ;
Query OK, 0 rows affected (0.31 sec)

mysql> create table t9
-> (
-> c1 tinyint, c2 smallint, c3 mediumint, c4 int,
-> c5 integer, c6 bigint, c7 float, c8 double,
-> c9 double precision, c10 real, c12 numeric(8, 4),
-> primary key(c1)
-> ) engine = InnoDB ;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t9
-> (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12)
-> values
-> ('1.11111111111111111111e+50','1.11111111111111111111e+50','1.11111111111111111111e+50', '1.11111111111111111111e+50','1.11111111111111111111e+50', '1.11111111111111111111e+50', '1.11111111111111111111e+50','1.11111111111111111111e+50','1.11111111111111111111e+50','1.11111111111111111111e+50','1.11111111111111111111e+50' );
Query OK, 1 row affected, 14 warnings (0.00 sec)

mysql> select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 from t9;
+----+------+------+------+------+------+------------+----------------------+----------------------+----------------------+-----------+
| c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 | c12 |
+----+------+------+------+------+------+------------+----------------------+----------------------+----------------------+-----------+
| 0 | 0 | 0 | 0 | 0 | 0 | 3.40282e38 | 1.111111111111111e50 | 1.111111111111111e50 | 1.111111111111111e50 | 9999.9999 |
+----+------+------+------+------+------+------------+----------------------+----------------------+----------------------+-----------+
1 row in set (0.00 sec)

mysql> delete from t9 ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t9
-> (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12)
-> values
-> ('-9223372036854775808','-9223372036854775808','-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808', '-9223372036854775808' );
Query OK, 1 row affected, 6 warnings (0.00 sec)

mysql> select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 from t9;
+------+--------+----------+-------------+-------------+----------------------+-------------+-----------------------+-----------------------+-----------------------+------------+
| c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 | c12 |
+------+--------+----------+-------------+-------------+----------------------+-------------+-----------------------+-----------------------+-----------------------+------------+
| -128 | -32768 | -8388608 | -2147483648 | -2147483648 | -9223372036854775808 | -9.22337e18 | -9.223372036854776e18 | -9.223372036854776e18 | -9.223372036854776e18 | -9999.9999 |
+------+--------+----------+-------------+-------------+----------------------+-------------+-----------------------+-----------------------+-----------------------+------------+
1 row in set (0.00 sec)

mysql> delete from t9 ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t9
-> (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 )
-> values
-> (1.11111111111111111111e+50,1.11111111111111111111e+50,1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50, 1.11111111111111111111e+50,1.11111111111111111111e+50 );
Query OK, 1 row affected, 8 warnings (0.01 sec)

mysql> select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c12 from t9;
+-----+-------+---------+------------+------------+---------------------+------------+----------------------+----------------------+----------------------+-----------+
| c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 | c12 |
+-----+-------+---------+------------+------------+---------------------+------------+----------------------+----------------------+----------------------+-----------+
| 127 | 32767 | 8388607 | 2147483647 | 2147483647 | 9223372036854775807 | 3.40282e38 | 1.111111111111111e50 | 1.111111111111111e50 | 1.111111111111111e50 | 9999.9999 |
+-----+-------+---------+------------+------------+---------------------+------------+----------------------+----------------------+----------------------+-----------+
1 row in set (0.00 sec)

mysql> delete from t9;
Query OK, 1 row affected (0.00 sec)

```
### 4. What is your TiDB version? (Required)
```sql
| Release Version: v5.4.0
Edition: Community
Git Commit Hash: 55f3b24c1c9f506bd652ef1d162283541e428872
Git Branch: heads/refs/tags/v5.4.0
UTC Build Time: 2022-01-25 08:41:05
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.