assertion failed when inserting some rows

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table tf8f605ff ( col_30 boolean ,col_31 decimal ( 37 , 17 )    default 957.79 ,col_32 time   not null , unique key idx_6 ( col_32 ,col_30 ,col_31 ) ,unique key idx_7 ( col_31 ,col_32 ,col_30 ) ,unique key idx_8 ( col_31 ,col_32 ,col_30 ) ) charset gbk collate gbk_bin;
alter table tf8f605ff add column col_59 tinyint  unsigned not null;
alter table tf8f605ff alter column col_31 drop default;
alter table tf8f605ff add unique key idx_25 ( col_30 );
INSERT INTO `tf8f605ff` VALUES (0,NULL,'12:09:39',128), (1,211.00000000000000000,'10:44:41',76);
insert ignore into tf8f605ff  values ( 0,0.71,'17:42:07.00',2 ) ,( 1,5207.803,'05:05:08.00',42 ) ,( 1,8.755,'16:53:49.00',19 ) ,( 0,0.81,'14:08:29.00',208 ) on duplicate key update col_31 = 8438.3418, col_59 = 61, col_32 = '00:35:59.00', col_30 = 0;

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

No error

3. What did you see instead (Required)

mysql> insert ignore into tf8f605ff  values ( 0,0.71,'17:42:07.00',2 ) ,( 1,5207.803,'05:05:08.00',42 ) ,( 1,8.755,'16:53:49.00',19 ) ,( 0,0.81,'14:08:29.00',208 ) on duplicate key update col_31 = 8438.3418, col_59 = 61, col_32 = '00:35:59.00', col_30 = 0;
ERROR 8141 (HY000): assertion failed: key: 7480000000000005125f69800000000000000107800027d117be5e000380000000000000000625118000000000000000000000000000000000, assertion: Exist, start_ts: 446766707530006528, existing start ts: 0, existing commit ts: 0

4. What is your TiDB version? (Required)

33480e8

[kennedy8312]

/type regression

[kennedy8312]

Regression Analysis
The issue happened on v6.2.0.

The issue didn't happen on v6.1.0.

[kennedy8312]

Regression Analysis
PR caused this regression : The regression PR should be a commit between c8468a7 and c5393d0. But it was hard to locate further since the tidb-server broke for all commits between these two.

[windtalker]

Hi @jebter I think it is a transaction related bug, maybe we should label it as sig/transaction?

[cfzjywxk]

It's possbile a regression related to the codec or execution processing, but not transaction protocol.
@crazycs520 PTAL if you have time, thx

[crazycs520]

The test case can simplify to:

create table t ( c1 boolean ,c2 decimal ( 37 , 17 ), unique key idx1 (c1 ,c2) );
alter table t alter column c2 drop default;
alter table t add unique key idx4 ( c1 );
INSERT INTO t VALUES (0,NULL), (1,1);
insert ignore into t  values ( 0,2) ,( 1,3) on duplicate key update c2 = 5, c1 = 0;

(8141, 'assertion failed: key: 7480000000000001525f6980000000000000010380000000000000000625118000000000000000000000000000000000, assertion: Exist, start_ts: 447963869755211782, existing start ts: 0, existing commit ts: 0')

Decode the key, you can see c2 value is 0.00000000000000000, this is strange, since we never insert 0 value into column c2.

select tidb_decode_key( '7480000000000001525f6980000000000000010380000000000000000625118000000000000000000000000000000000' );
+-----------------------------------------------------------------------------------------------------------------------+
| tidb_decode_key( '7480000000000001525f6980000000000000010380000000000000000625118000000000000000000000000000000000' ) |
+-----------------------------------------------------------------------------------------------------------------------+
| {"index_id":1,"index_vals":{"c1":"0","c2":"0.00000000000000000"},"table_id":338}                                      |
+-----------------------------------------------------------------------------------------------------------------------+

Analyze

The last SQL statement insert ignore ... on duplicate ... is strange.

[tidb/pkg/executor/insert.go at 6e10826f164eb13f6f86640e856b945539b57b0d · pingcap/tidb (github.com)](

tidb/pkg/executor/insert.go

Line 80 in 6e10826

// However, if the `on duplicate update` is also specified, the duplicated row will be updated.

) said:

If you use the IGNORE keyword, duplicate-key error that occurs while executing the INSERT statement are ignored.
However, if the on duplicate update is also specified, the duplicated row will be updated.

But in this issue, actually is on duplicate work, and ignore keyword also work that cause to ignore some error.

So if you remove ignore keyword use following SQL, then you will got another error:

> insert into t  values ( 0,2) ,( 1,3) on duplicate key update c2 = 5, c1 = 0;
(1364, "Field 'c2' doesn't have a default value")

This error is unexpected and wired, should fix this issue first. @wjhuang2016 @lcwangchao PTAL

Returning to this issue, the reason for this is as follows:

1. Since ignore keyword, the error Field 'c2' doesn't have a default value is been ignored.
   • [tidb/pkg/table/column.go at 1ee58bf15bb4cbbadb740fff32612159577c4111 · pingcap/tidb (github.com)](

     tidb/pkg/table/column.go

     Line 665 in 1ee58bf

     return GetZeroValue(col), nil

     )
2. Then use wrong default value of column c2, it’s value should be NULL, but it use a zero value instead, which will make later assertion failed, since the zero value of column c2 is not exist.

Thanks to the assertion feature, if we disable assertion, this issue will cause a data inconsistency issue:

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

mysql> create table t ( c1 boolean ,c2 decimal ( 37 , 17 ), unique key idx1 (c1 ,c2) );
Query OK, 0 rows affected (0.01 sec)

mysql> alter table t alter column c2 drop default;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table t add unique key idx4 ( c1 );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t VALUES (0,NULL), (1,1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert ignore into t  values ( 0,2) ,( 1,3) on duplicate key update c2 = 5, c1 = 0;
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 2

mysql> admin check table t;
ERROR 8223 (HY000): data inconsistency in table: t, index: idx1, handle: 1, index-values:"handle: 1, values: [KindInt64 0 KindNull <nil>]" != record-values:"handle: 1, values: [KindInt64 0 KindMysqlDecimal 5.00000000000000000]"

[crazycs520]

@Defined2014 PTAL

[crazycs520]

another simply failed case is:

create table t ( c1 boolean ,c2 decimal ( 37 , 17 ), unique key idx1 (c1 ,c2),unique key idx2 ( c1 ));
INSERT INTO t VALUES (0,NULL);
alter table t alter column c2 drop default;
update t set c2=5 where c1=0;

(1364, "Field 'c2' doesn't have a default value")