Unexpected Result byCRC32() and SIN() Functions

[bajinsheng]

Bug Report

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 BIGINT);
INSERT t0 VALUES(1674610777);
SELECT t0.c0 FROM t0 WHERE CRC32(SIN(t0.c0)) >= t0.c0; -- Empty set

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

CRC32(SIN(t0.c0)) = 1782146908, which is greater than 1674610777, so this query should return one row, instead of empty.

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

+--------------------------------------------+
| version()                                  |
+--------------------------------------------+
| 8.0.11-TiDB-v9.0.0-beta.1.pre-563-gea52376 |
+--------------------------------------------+

[bajinsheng]

/label fuzz/sqlancer

[xxxuuu]

I can't reproduce it. do you have more information?

[bajinsheng]

Thanks for looking into it! @xxxuuu

I believe it should be related to TiKV coprocessor.

I ran it using TiKV:

$ tiup/bin/tiup playground nightly                 
Checking updates for component playground... Start pd instance: v9.0.0-beta.1.pre-nightly
Start tikv instance: v9.0.0-beta.1.pre-nightly
Start tidb instance: v9.0.0-beta.1.pre-nightly
Waiting for tidb instances ready
- TiDB: 127.0.0.1:4000 ... Done
component grafana version v9.0.0-beta.1.pre-nightly is already installed
Start tiflash instance: v9.0.0-beta.1.pre-nightly
Waiting for tiflash instances ready
- TiFlash: 127.0.0.1:3930 ... Done

🎉 TiDB Playground Cluster is started, enjoy!

Connect TiDB:    mysql --comments --host 127.0.0.1 --port 4000 -u root
TiDB Dashboard:  http://127.0.0.1:2379/dashboard
Grafana:         http://127.0.0.1:3000

My SQL file test.sql:

DROP DATABASE IF EXISTS dbtest;
CREATE DATABASE dbtest;
USE dbtest;
CREATE TABLE t0(c0 BIGINT);
INSERT t0 VALUES(1674610777);
SELECT t0.c0 FROM t0 WHERE CRC32(SIN(t0.c0)) >= t0.c0;

Then run this SQL file and get empty result.

$ mysql -h 127.0.0.1 -P 4000 -uroot < test.sql 

[xxxuuu]

I analyzed the code and found that the reason for the difference lies in the SIN function.

When the function is used in the SELECT clause, it will be calculated in TiDB. When it is used in the WHERE clause, it may be pushed down to the TiKV Coprocessor for execution.

Both use the standard library to calculate SIN, but there is a slight difference in the results.

• For TiDB (Golang): SIN(1674610777) = -0.5116910117896449
• For TiKV (Rust): SIN(1674610777) = -0.511691011789645

From the perspective of error, this difference is reasonable. However, after being converted to a string and having CRC32 applied, it will lead to a significant difference in the results.

In addition, I tested the situation in MySQL 8.0/5.7, and the results are consistent with those of TiKV:

This issue is similar to #31164. I'm not sure if it's worth fixing now.

[solotzg]

It is a corner case and should be masked as severity/minor. cc @windtalker