UPDATE has inconsistent behaviors in a transaction

[TrocTool]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

/* init */ create table t(a blob not null, b text);
/* init */ insert ignore into t values (null, null), (null, 'abc');  -- [0]

It should be noted that the behavior of "INSERT IGNORE" is confusing, the statement [0] will execute successfully even through there is a NOT NULL constraint on column a. So the initialized table is like:

+------+------+
| a    | b    |
+------+------+
| NULL | NULL |
| NULL | abc  |
+------+------+

This is compatible with MySQL.
Now, start a transaction and execute the following statements:

/* s1 */ begin;
/* s1 */ update t set b = 'test' where a; -- [1]
/* s1 */ rollback;

/* s1 */ begin;
/* s1 */ update t set b = 'def';
/* s1 */ update t set b = 'test' where a; -- [2]

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

The two UPDATE statements [1] and [2] have the same results

3. What did you see instead (Required)

The first UPDATE [1] executes correctly while the second UPDATE [2] gets an error:

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

mysql> update t set b = 'test' where a;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

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

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

mysql> update t set b = 'def';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> update t set b = 'test' where a;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: ''

By the way, this error will not happen in MySQL.

4. What is your TiDB version? (Required)

mysql> select version();

+--------------------+
| version()          |
+--------------------+
| 5.7.25-TiDB-v5.2.1 |
+--------------------+
1 row in set (0.00 sec)

[zyguan]

The bug is not related to transaction, try the following

/* init */ drop table if exists t;
-- init >> 0 rows affected
/* init */ create table t(a blob not null, b text);
-- init >> 0 rows affected
/* init */ insert ignore into t values (null, null), (null, 'abc');
-- init >> 2 rows affected
/* s1 */ update t set b = 'test' where a;
-- s1 >> E1292: Truncated incorrect DOUBLE value: ''

When execute the update statement, tidb try to eval a as a bool, which casts it to a number firstly.

[TrocTool]

@zyguan Thanks for your reply, but this bug can only be triggered in transaction in my tests.

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.7.25-TiDB-v5.3.0 |
+--------------------+
1 row in set (0.00 sec)

mysql> drop table if exists t;
Query OK, 0 rows affected (0.24 sec)

mysql> create table t(a blob not null, b text);
Query OK, 0 rows affected (0.11 sec)

mysql> insert ignore into t values (null, null), (null, 'abc');
Query OK, 2 rows affected, 2 warnings (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> update t set b = 'test' where a;  // no error out of explicit transaction
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

And it should follow another statement

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

mysql> update t set b = 'test' where a; // no error in an explicit transaction
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

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

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

mysql> update t set b = 'def';  // this is needed.
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> update t set b = 'test' where a;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: ''  // triggered then

[zyguan]

@CycleYe update t set b = 'def'; is required because it leads to an union scan when executing update t set b = 'test' where a;, which eval condition a at tidb side (otherwise it will be pushed down to tikv and tikv handle it correctly). The case metioned here can be reproduced when using unistore (instead of tikv) and the root causes are the same.

[TrocTool]

@CycleYe update t set b = 'def'; is required because it leads to an union scan when executing update t set b = 'test' where a;, which eval condition a at tidb side (otherwise it will be pushed down to tikv and tikv handle it correctly). The case metioned here can be reproduced when using unistore (instead of tikv) and the root causes are the same.

I got it. Thanks for your reply.