TiDB fails to insert data when using transaction

[JZuming]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Setup the environment:

tiup playground --db.binpath /path/to/latest/tidb-server &
mysql -h "127.0.0.1" -u root -P 4000 -D testdb < mysql_bk.sql

mysql_bk.sql:
mysql_bk.sql.txt

Testcase 1

mysql -h "127.0.0.1" -u root -P 4000 -D testdb

mysql> delete from t_xkzvqb;
mysql> insert into t_rxrf9c values
(41, case when EXISTS (
    select distinct
        ref_0.c2 as c2
      from
        t_f32hfd as ref_0
      ) then 1 else 0 end
  , 74.4, 31);

/* check the data of t_rxrf9c */;
mysql> select * from t_rxrf9c; 

Testcase 2

mysql -h "127.0.0.1" -u root -P 4000 -D testdb

mysql> start transaction;
mysql> delete from t_xkzvqb;
mysql> insert into t_rxrf9c values
(41, case when EXISTS (
    select distinct
        ref_0.c2 as c2
      from
        t_f32hfd as ref_0
      ) then 1 else 0 end
  , 74.4, 31); 
/* fail to insert and print an error: Truncated incorrect INTEGER value: 'li53c' */
mysql> commit;

/* check the data of t_rxrf9c */;
mysql> select * from t_rxrf9c; 

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

The outputs of SELECT statements in Testcase 1 and Testcase 2 are same.

3. What did you see instead (Required)

Output of SELECT statement in Testcase 1

+---------+---------+----------+---------+
| c_u3bwg | c_a_p8b | c_09ew1d | c_wylqr |
+---------+---------+----------+---------+
|       6 |      24 |    53.56 |      59 |
|      41 |       0 |     74.4 |      31 |
+---------+---------+----------+---------+
2 rows in set (0.01 sec)

Output of SELECT statement in Testcase 2

+---------+---------+----------+---------+
| c_u3bwg | c_a_p8b | c_09ew1d | c_wylqr |
+---------+---------+----------+---------+
|       6 |      24 |    53.56 |      59 |
+---------+---------+----------+---------+
1 row in set (0.00 sec)

Their results are different.

4. What is your TiDB version? (Required)

Release Version: v5.4.0-alpha-311-g28446605c
Edition: Community
Git Commit Hash: 28446605c2e019e84e934602f4bd136294a61ff3
Git Branch: master
UTC Build Time: 2021-12-03 08:12:32
GoVersion: go1.16
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[vivid392845427]

seems duplcate the issue:#26552

[JZuming]

Thanks for your checking, @vivid392845427 . I am just a bit confused why the test case can be successfully executed without transaction but it triggers the error with transaction. Is there any thing special for transaction? If there is, I think I should adjust my testing tool

[zyguan]

Thanks for your checking, @vivid392845427 . I am just a bit confused why the test case can be successfully executed without transaction but it triggers the error with transaction. Is there any thing special for transaction? If there is, I think I should adjust my testing tool

@JZuming , changes made by DMLs will be cached until the final commit in the transaction context. In your case, the underlying data source executor of the subquery can still read the data, even when the previous delete is executed successfully. The deleted rows are marked on tidb side and won't be shown to users, you can learn about the mechanism from this doc (if you're familiar with chinese). So your case can be simplified as the following:

DROP TABLE IF EXISTS `t_rxrf9c`;
CREATE TABLE `t_rxrf9c` (`c_u3bwg` int(11) NOT NULL,`c_a_p8b` int(11) DEFAULT NULL,`c_09ew1d` double NOT NULL,`c_wylqr` int(11) DEFAULT NULL,PRIMARY KEY (`c_09ew1d`,`c_u3bwg`) /*T![clustered_index] NONCLUSTERED */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `t_rxrf9c` VALUES (6,24,53.56,59);

DROP TABLE IF EXISTS `t_xkzvqb`;
CREATE TABLE `t_xkzvqb` (`c_0q_i3b` int(11) DEFAULT NULL,`c__ckpid` int(11) DEFAULT NULL,`c_s4e7jc` double DEFAULT NULL,`c_k_dsxd` text DEFAULT NULL,`c_ofdx2c` double NOT NULL,`c__9zs7d` int(11) DEFAULT NULL,PRIMARY KEY (`c_ofdx2c`) /*T![clustered_index] NONCLUSTERED */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `t_xkzvqb` VALUES (36,59,3.26,'li53c',83.25,6);

DROP VIEW IF EXISTS `t_f32hfd`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `t_f32hfd` (`c0`, `c1`, `c2`, `c3`, `c4`, `c5`, `c7`) AS SELECT DISTINCT `ref_2`.`c_wylqr` AS `c0`,`subq_0`.`c5` AS `c1`,CUME_DIST() OVER (PARTITION BY `ref_1`.`c__9zs7d` ORDER BY `subq_0`.`c1`,`ref_1`.`c__9zs7d`) AS `c2`,`subq_0`.`c1` AS `c3`,CASE WHEN `subq_1`.`c2` NOT LIKE _UTF8MB4'nf%zyd' THEN (_UTF8MB4'6ddkrb' OR `ref_1`.`c_k_dsxd`) ELSE `subq_1`.`c2` END AS `c4`,NULLIF(CASE WHEN `subq_0`.`c1`>=`subq_1`.`c4` THEN `subq_1`.`c1` ELSE `subq_1`.`c5` END, `subq_1`.`c3`) AS `c5`,(`ref_1`.`c__ckpid`*COALESCE(`ref_1`.`c__ckpid`, `ref_1`.`c__ckpid`)) AS `c7` FROM ((SELECT `ref_0`.`c_09ew1d` AS `c0`,`ref_0`.`c_u3bwg` AS `c1`,`ref_0`.`c_09ew1d` AS `c2`,`ref_0`.`c_09ew1d` AS `c3`,`ref_0`.`c_u3bwg` AS `c4`,`ref_0`.`c_a_p8b` AS `c5` FROM `t_rxrf9c` AS `ref_0` WHERE (`ref_0`.`c_wylqr`!=`ref_0`.`c_u3bwg`) OR (`ref_0`.`c_a_p8b`>`ref_0`.`c_a_p8b`)) AS `subq_0` JOIN `t_xkzvqb` AS `ref_1`) JOIN (`t_rxrf9c` AS `ref_2` LEFT JOIN (SELECT `ref_3`.`c_0q_i3b` AS `c0`,`ref_3`.`c__ckpid` AS `c1`,`ref_3`.`c_k_dsxd` AS `c2`,`ref_3`.`c_0q_i3b` AS `c3`,`ref_3`.`c_0q_i3b` AS `c4`,`ref_3`.`c__9zs7d` AS `c5`,`ref_3`.`c_ofdx2c` AS `c6` FROM `t_xkzvqb` AS `ref_3` WHERE `ref_3`.`c_0q_i3b` BETWEEN `ref_3`.`c__9zs7d` AND `ref_3`.`c__ckpid`) AS `subq_1` ON (`ref_2`.`c_wylqr`=`subq_1`.`c0`)) ON (`ref_1`.`c_0q_i3b`=`ref_2`.`c_u3bwg`) WHERE (`ref_1`.`c_k_dsxd` OR `ref_1`.`c_k_dsxd`) LIKE _UTF8MB4'3x__';

insert into t_rxrf9c values (41, case when EXISTS (select distinct ref_0.c2 as c2 from t_f32hfd as ref_0) then 1 else 0 end, 74.4, 31);

The last insert failed due to strict SQL mode. Since the subquery is used by an insert statement and STRICT_TRANS_TABLES is used by default, tidb sends cop request to tikv without FlagIgnoreTruncate, and then tikv reports a truncate error (although it's unwanted).

[JZuming]

Thanks for your clear explanation, @zyguan . I can read the doc (I am Chinese) and I understand now.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.

[xiongjiwei]

dup of #26552