insert truncated decimal value with sql_mode='' should succeed

[tiancaiamao]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t;
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `x` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>  insert into t   select round(cast('88888899999999999888888888888888888888888888888888888.11111111111111111111' as decimal(18,12)) * cast('88888899999999999888888888888888888888888888888888888.11111111111111111111' as decimal(42,18)) );
Query OK, 1 row affected, 2 warnings (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 2

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1264 | Out of range value for column '(null)' at row 1 |
| Warning | 1264 | Out of range value for column '(null)' at row 1 |
+---------+------+-------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from t;
+--------------------------------+
| x                              |
+--------------------------------+
| 999999999999999999000000000000 |
+--------------------------------+
5 rows in set (0.00 sec)

2. What did you expect to see? (Required)

This SQL should succeed with warning.

 insert into t   select ...

3. What did you see instead (Required)

mysql> set @@sql_mode ='';
Query OK, 0 rows affected (0.00 sec)

mysql>  show create table t;
+-------+---------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `x` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> insert into t   select round(cast('88888899999999999888888888888888888888888888888888888.11111111111111111111' as decimal(18,12)) * cast('88888899999999999888888888888888888888888888888888888.11111111111111111111' as decimal(42,18)) );
ERROR 1690 (22003): DECIMAL value is out of range in '(18, 12)'
mysql> select * from t;
Empty set (0.00 sec)

4. What is your TiDB version? (Required)

mysql> select tidb_version();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.6.0-alpha-387-gc3982c4e74-dirty
Edition: Community
Git Commit Hash: c3982c4e74116dade00d6eb12bef81ce38d77eaa
Git Branch: resultset-close
UTC Build Time: 2023-12-08 01:47:08
GoVersion: go1.21.0
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[bb7133]

@jebter I don't think this is a major bug, it is in an edge case for a specific sql mode.

[bb7133]

@tiancaiamao It's not about MySQL 8 compatibility: behavior is the same for MySQL 5.7 so I will remove 'compatibility-mysql8'.

[seiya-annie]

/found customer