An incorrect query result

[sayJason]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t1 (c0 decimal(10,0));
INSERT INTO t1 VALUES (0);
SELECT c0 FROM t1 WHERE CAST(ATAN2(((t1.c0) IS NULL), (- (''))) AS TIME); -- actual: {}, expected: {0}

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

The SELECT statement should return the value 0.

3. What did you see instead (Required)

The SELECT statement returns an empty set.

4. What is your TiDB version? (Required)

Release Version: v6.6.0
Edition: Community
Git Commit Hash: f4ca082
Git Branch: heads/refs/tags/v6.6.0
UTC Build Time: 2023-02-17 14:49:02
GoVersion: go1.19.5
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

[seiya-annie]

tried in mysql:

mysql> CREATE TABLE t1 (c0 decimal(10,0));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT c0 FROM t1 WHERE CAST(ATAN2(((t1.c0) IS NULL), (- (''))) AS TIME); 
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

[yibin87]

The root cause is the ATAN2 function behaves differently in tidb and tikv, and tidb behaves the same as MySQL:

mysql> select ATAN2(((t1.c0) IS NULL), (- (''))) as a from t1;
+------+
| a    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

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

mysql> select ATAN2(((t1.c0) IS NULL), (- (''))) as a from t1;
+-------------------+
| a                 |
+-------------------+
| 3.141592653589793 |
+-------------------+
1 row in set (0.00 sec)

[wshwsh12]

This issue occurs because TiDB ignores the sign bit when encoding the floating-point constant -0 and pushing it down to TiKV. TiKV use the 0 to calcute the result and get wrong.
To fix this issue, the lowest-level encode/decode functions need to be modified, which would cause many behavioral changes. For example, incorrect results in hash aggregation and stream aggregation, as well as unstable sorting results.