TiDB produces different results for DELETE statements (Division by 0)

[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.txt

Testcase 1

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

mysql> insert into t_3nyn_c values (52, 91, 89.98, 47, 27, 0, '4nj9nb', 91);
mysql> delete from t_3nyn_c where (t_3nyn_c.wkey % t_3nyn_c.c_9kited) is NULL;

Testcase 2

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

mysql> start transaction;
mysql> insert into t_3nyn_c values (52, 91, 89.98, 47, 27, 0, '4nj9nb', 91);
mysql> delete from t_3nyn_c where (t_3nyn_c.wkey % t_3nyn_c.c_9kited) is NULL;
mysql> commit;

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

The execution result of DELETE statements in Testcase 1 and Testcase 2 are same.

3. What did you see instead (Required)

Output of DELETE statement in Testcase 1

Query OK, 1 row affected (0.00 sec)

Output of DELETE statement in Testcase 2

ERROR 1365 (22012): Division by 0

Their results are different.

4. What is your TiDB version? (Required)

Release Version: v6.1.0-alpha-173-g32b9c1477
Edition: Community
Git Commit Hash: 32b9c14779c2a7dd73003667d81bb42f67a33385
Git Branch: master
UTC Build Time: 2022-04-11 17:53:15
GoVersion: go1.18
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[zanmato1984]

After some investigation, found that:

1. Data in tikv (i.e., committed in an auto transaction) will be processed in tikv, by "processed" I mean the calculation of the filter.
2. Tikv doesn't respect SQL mode very well.
3. Data in an uncommitted transaction will be processed in tidb, and it respects SQL mode, hence the difference you saw.

[zanmato1984]

Downgrade to moderate.