"Data truncated for column '%s' at row %d"

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists PK_PRECISION10063;
CREATE TABLE `PK_PRECISION10063` (
  `COL1` decimal(55,0) NOT NULL,
  `COL2` varchar(20) DEFAULT NULL,
  `COL4` datetime DEFAULT NULL,
  `COL3` bigint(20) DEFAULT NULL,
  `COL5` float DEFAULT NULL,
  PRIMARY KEY (`COL1`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into `PK_PRECISION10063` (`COL1`) values (8320145330174776428);

select col1, tan(col1) from PK_PRECISION10063 where tan(col1) < tan(9021874879467600608071521900001091070693729763119983979);

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

[16:55:05]MySQL root:test> select col1, tan(col1) from PK_PRECISION10063 where tan(col1) < tan(9021874879467600608071521900001091070693729763119983979);
+---------------------+---------------------+
| col1                | tan(col1)           |
+---------------------+---------------------+
| 8320145330174776428 | 0.06084016242886609 |
+---------------------+---------------------+

3. What did you see instead (Required)

[16:54:19]TiDB root:test> select col1, tan(col1) from PK_PRECISION10063 where tan(col1) < tan(9021874879467600608071521900001091070693729763119983979);
(1265, "Data truncated for column '%s' at row %d")

4. What is your TiDB version? (Required)

v8.1.0

[aytrack]

It's a bit strange, I can execute it fine on one clustered environment, but not on another!

[10:03:11]TiDB root:coms> select col1, tan(col1) from PK_PRECISION10063 where tan(col1) < tan(9021874879467600608071521900001091070693729763119983979);
+---------------------+---------------------+
| col1                | tan(col1)           |
+---------------------+---------------------+
| 8320145330174776428 | 0.06084016242886609 |
+---------------------+---------------------+
1 row in set
Time: 0.064s
[10:03:18]TiDB root:coms> explain select col1, tan(col1) from PK_PRECISION10063 where tan(col1) < tan(9021874879467600608071521900001091070693729763119983979);
+-----------------------+---------+-----------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| id                    | estRows | task      | access object           | operator info                                                                                                                                |
+-----------------------+---------+-----------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_4          | 0.80    | root      |                         | coms.pk_precision10063.col1, tan(cast(coms.pk_precision10063.col1, double BINARY))->Column#6                                                 |
| └─Selection_5         | 0.80    | root      |                         | lt(tan(cast(coms.pk_precision10063.col1, double BINARY)), tan(cast(9021874879467600608071521900001091070693729763119983979, double BINARY))) |
|   └─TableReader_7     | 1.00    | root      |                         | data:TableFullScan_6                                                                                                                         |
|     └─TableFullScan_6 | 1.00    | cop[tikv] | table:PK_PRECISION10063 | keep order:false, stats:pseudo                                                                                                               |
+-----------------------+---------+-----------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set
Time: 0.046s
[10:03:29]TiDB root:coms> select tidb_version();
+-----------------------------------------------------------+
| tidb_version()                                            |
+-----------------------------------------------------------+
| Release Version: v8.0.0                                   |
| Edition: Community                                        |
| Git Commit Hash: 8ba1fa452b1ccdbfb85879ea94b9254aabba2916 |
| Git Branch: HEAD                                          |
| UTC Build Time: 2024-03-28 14:22:15                       |
| GoVersion: go1.21.4                                       |
| Race Enabled: false                                       |
| Check Table Before Drop: false                            |
| Store: tikv                                               |
+-----------------------------------------------------------+
1 row in set
Time: 0.058s

another same version cluster

mysql> select col1, tan(col1) from PK_PRECISION10063 where tan(col1) < tan(9021874879467600608071521900001091070693729763119983979);
ERROR 1265 (01000): Data truncated for column '%s' at row %d
mysql> explain select col1, tan(col1) from PK_PRECISION10063 where tan(col1) < tan(9021874879467600608071521900001091070693729763119983979);
+---------------------------+----------+-----------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| id                        | estRows  | task      | access object           | operator info                                                                                                                                |
+---------------------------+----------+-----------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_4              | 8000.00  | root      |                         | test.pk_precision10063.col1, tan(cast(test.pk_precision10063.col1, double BINARY))->Column#6                                                 |
| └─Selection_5             | 8000.00  | root      |                         | lt(tan(cast(test.pk_precision10063.col1, double BINARY)), tan(cast(9021874879467600608071521900001091070693729763119983979, double BINARY))) |
|   └─TableReader_7         | 10000.00 | root      |                         | data:TableFullScan_6                                                                                                                         |
|     └─TableFullScan_6     | 10000.00 | cop[tikv] | table:PK_PRECISION10063 | keep order:false, stats:pseudo                                                                                                               |
+---------------------------+----------+-----------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.0.0
Edition: Community
Git Commit Hash: 8ba1fa452b1ccdbfb85879ea94b9254aabba2916
Git Branch: HEAD
UTC Build Time: 2024-03-28 14:22:15
GoVersion: go1.21.4
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

[yibin87]

It seems tidb doesn't try to handle truncate error inside builtinCastDecimalAsRealSig::evalReal:

tidb/pkg/expression/builtin_cast.go

Line 1231 in 024fdd2

val, isNull, err := b.args[0].EvalDecimal(ctx, row)

[VivekGupta137]

Facing this issue as well.

[apollodafoni]

/label fuzz/comp