TiDB produces different results for SELECT statements （BIGINT value is out of range in '(8 - 100)'）

[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> delete from t_xa9msd;
mysql> select * from t_xa9msd as ref_0
        where (63 & 72) - case when ref_0.wkey is NULL then 66 else 100 end > 88;

Testcase 2

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

mysql> start transaction;
mysql> delete from t_xa9msd;
mysql> select * from t_xa9msd as ref_0
        where (63 & 72) - case when ref_0.wkey is NULL then 66 else 100 end > 88;
mysql> commit;

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

The execution result 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

Empty set (0.00 sec)

Output of SELECT statement in Testcase 2

ERROR 1690 (22003): BIGINT value is out of range in '(8 - 100)'

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

[JZuming]

Another test cases correctly return the result (only change 63 & 72 to 8):

Testcase 1

mysql> delete from t_xa9msd;
mysql> select * from t_xa9msd as ref_0
        where (8) - case when ref_0.wkey is NULL then 66 else 100 end > 88;

Testcase 2

mysql> start transaction;
mysql> delete from t_xa9msd;
mysql> select * from t_xa9msd as ref_0
        where (8) - case when ref_0.wkey is NULL then 66 else 100 end > 88;
mysql> commit;

Both of them return this result:

Empty set (0.00 sec)

[gengliqi]

There are three issues in my opinion.

First, the overflow error is caused by (63 & 72) is a unsigned integer and (63 & 72) - 66/100 is less than 0.
And 8 is a signed integer, so in your latter case, there is no error.

Second, the different error output between non-transaction and transaction is a little confusing. In transaction mode, the execution is pushed down to TiKV and the table is not deleted(the deleted rows are just cached in TiDB memory). So the error occurs.
In my opinion, this inconsistent behavior is acceptable because it doesn't make any sense to query a table after it is deleted and even this query may have an overflow error.

Third, the error log should be BIGINT UNSIGNED value is out of range. It's a small bug in TiKV.

As a result, I think the severity should be minor.

[gengliqi]

/remove-severity major
/severity minor