UnionExec sometimes receives data truncated error from children when casting string to decimal

pcqz commented 6 months ago

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

USE TEST;
CREATE TABLE `test1` (
  `bglac` varchar(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `bglac` varchar(21) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY HASH (`id`) PARTITIONS 3;

Unable to reproduce steadily when not in transaction:

insert into test values(1,'11,11');
insert into test1(bglac) select  sum(convert(ifnull(bglac, 0), decimal(21,3))) from test;

Reproduce steadily when in transaction using UnionScan:

begin;
insert into test values(1,'11,11');
insert into test1(bglac) select  sum(convert(ifnull(bglac, 0), decimal(21,3))) from test;

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

No error.

3. What did you see instead (Required)

Sometimes report error if insert is not in transaction:

mysql> insert into test1(bglac) select  sum(convert(ifnull(bglac, 0), decimal(21,3))) from test;
ERROR 1265 (01000): Data truncated for column '%s' at row %d
mysql> insert into test1(bglac) select  sum(convert(ifnull(bglac, 0), decimal(21,3))) from test;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

Always report error in transaction:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(1,'11,11');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1(bglac) select  sum(convert(ifnull(bglac, 0), decimal(21,3))) from test;
ERROR 1265 (01000): Data truncated for column '%s' at row %d

4. What is your TiDB version? (Required)

v6.5.2

seiya-annie commented 6 months ago

it seems can be steadily reproduced on master branch

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1,'11,11');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test1(bglac) select  sum(convert(ifnull(bglac, 0), decimal(21,3))) from test;
ERROR 1265 (01000): Data truncated for column '%s' at row %d
mysql> 
mysql> 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test1;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test;
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(1,'11,11');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test1(bglac) select  sum(convert(ifnull(bglac, 0), decimal(21,3))) from test;
ERROR 1265 (01000): Data truncated for column '%s' at row %d
mysql> 
mysql> delete from test;
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(1,'11,11');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test1(bglac) select  sum(convert(ifnull(bglac, 0), decimal(21,3))) from test;
ERROR 1265 (01000): Data truncated for column '%s' at row %d
mysql> 
mysql> select tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.1.0-alpha-109-g1b3700c
Edition: Community
Git Commit Hash: 1b3700c1c1507fadc77dfa512f2f4f251921677b
Git Branch: HEAD
UTC Build Time: 2024-04-17 02:36:26
GoVersion: go1.21.4
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

yibin87 commented 6 months ago

Reproduced the issue locally. There are two factors that lead to the random behavior:

Plan changes due to statistic data. When statistic data is accurate, optimizer knows there is only 1 row data in test, then it prefers one-phase stream agg over two-phase hash agg, and the cast operation will be performed in tidb side; When statistic data is not ready, optimizer chooses two-phase hash agg, and the cast operation will be pushed down to tikv side.
Tikv's cast string as decimal seems work silently when input is incorrect string; tidb's cast string as decimal will produce warning when incorrect string.

yibin87 commented 6 months ago

related with #51879

gengliqi commented 6 months ago

fixed by https://github.com/tikv/tikv/pull/16963

gengliqi commented 6 months ago

/close

ti-chi-bot[bot] commented 6 months ago

@gengliqi: Closing this issue.

In response to [this](https://github.com/pingcap/tidb/issues/52644#issuecomment-2104096630): >/close Instructions for interacting with me using PR comments are available [here](https://git.k8s.io/community/contributors/guide/pull-requests.md). If you have questions or suggestions related to my behavior, please file an issue against the [kubernetes/test-infra](https://github.com/kubernetes/test-infra/issues/new?title=Prow%20issue:) repository.

pingcap / tidb