[Bug] insert时数值超出类型表示范围仍导入成功

[yongjinhou]

Search before asking

• I had searched in the issues and found no similar issues.

Version

2.1.5

What's Wrong?

使用新优化器，insert时数值超出类型表示范围仍导入成功
这个问题与新优化器有关，之前的优化器在stmt.analyze阶段就抛异常了，新优化器没考虑这个异常

What You Expected?

insert时数值超出类型表示范围导入失败并抛出异常

How to Reproduce?

insert导入的值超出int范围：

CREATE TABLE int_tb ( k1 INT NULL, v1 INT SUM NULL, v2 INT MAX NULL, v3 INT MIN NULL, v4 INT REPLACE NULL ) AGGREGATE KEY(k1) DISTRIBUTED BY HASH(k1) BUCKETS 15 PROPERTIES ("replication_allocation" = "tag.location.default: 1");

insert into int_tb select 0, -2147483649, 0, 0, 0;

insert导入的值超出char范围：

CREATE TABLE stream_insert_datatype_db.varchar_tb ( k1 VARCHAR(1) NULL, v1 VARCHAR(1) REPLACE NULL ) AGGREGATE KEY(k1) DISTRIBUTED BY HASH(k1) BUCKETS 15 PROPERTIES ("replication_allocation" = "tag.location.default: 1");

insert into varchar_tb select "a", 100;

Anything Else?

无

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[Mryange]

|   0:VUNION(43)                                                                                                       |
|      constant exprs:                                                                                                 |
|          0 | CAST(-2147483649 AS int) | 0 | 0 | 0                                                                    |
|      tuple ids: 0 

现在新优化器上会考虑规划一个cast，而cast的实现中，目前是不会检查是否超出范围的。
有在考虑超出范围的时候报错，这里有个demo
#40334

mysql [test]>set enable_strict_cast_mode = false;
Query OK, 0 rows affected (0.00 sec)

mysql [test]>select cast(k2 as int) from testdb;
+-----------------+
| cast(k2 as INT) |
+-----------------+
|     -2147483648 |
+-----------------+
1 row in set (0.02 sec)

mysql [test]>set enable_strict_cast_mode = true;
Query OK, 0 rows affected (0.00 sec)

mysql [test]>select cast(k2 as int) from testdb;
ERROR 1105 (HY000): errCode = 2, detailMessage = (127.0.0.1)[INVALID_ARGUMENT]Cast to type Int32 ,out of range 9.2173984e+18

[spihiker]

|   0:VUNION(43)                                                                                                       |
|      constant exprs:                                                                                                 |
|          0 | CAST(-2147483649 AS int) | 0 | 0 | 0                                                                    |
|      tuple ids: 0 

现在新优化器上会考虑规划一个cast，而cast的实现中，目前是不会检查是否超出范围的。 有在考虑超出范围的时候报错，这里有个demo #40334

mysql [test]>set enable_strict_cast_mode = false;
Query OK, 0 rows affected (0.00 sec)

mysql [test]>select cast(k2 as int) from testdb;
+-----------------+
| cast(k2 as INT) |
+-----------------+
|     -2147483648 |
+-----------------+
1 row in set (0.02 sec)

mysql [test]>set enable_strict_cast_mode = true;
Query OK, 0 rows affected (0.00 sec)

mysql [test]>select cast(k2 as int) from testdb;
ERROR 1105 (HY000): errCode = 2, detailMessage = (127.0.0.1)[INVALID_ARGUMENT]Cast to type Int32 ,out of range 9.2173984e+18

Which version can support?

[github-actions]

We're closing this issue because it hasn't been updated in a while.
If you'd like to revive this issue, please reopen it and feel free a maintainer to remove the Stale tag!