After cot() is evaluated, the returned result is incorrect

[apollodafoni]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table lrr_test(`COL1` mediumint(45) NOT NULL);
insert into lrr_test values(-2308143);
insert into lrr_test values(90);
select col1, cot(col1) from lrr_test where col1=-2308143;
select col1, cot(col1) from lrr_test where cot(col1)=cot(-2308143);

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

+----------+--------------------+
| col1 | cot(col1) |
+----------+--------------------+
| -2308143 | 0.9135308955291633 |
+----------+--------------------+

3. What did you see instead (Required)

Empty set
It seems that the judgment failed due to precision problems.
Can execution plan be simplified to judge col=-2308143:

4. What is your TiDB version? (Required)

Release Version: v8.4.0
Edition: Community
Git Commit Hash: 2205f33
Git Branch: HEAD
UTC Build Time: 2024-10-17 07:36:44
GoVersion: go1.23.2
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[apollodafoni]

/severity major
/label wrong-result

[ti-chi-bot]

@apollodafoni: The label(s) wrong-result cannot be applied. These labels are supported: fuzz/sqlancer, challenge-program, compatibility-breaker, first-time-contributor, contribution, good first issue, correctness, duplicate, proposal, security, needs-more-info, needs-cherry-pick-release-5.4, needs-cherry-pick-release-6.1, needs-cherry-pick-release-6.5, needs-cherry-pick-release-7.1, needs-cherry-pick-release-7.5, needs-cherry-pick-release-8.1, affects-5.4, affects-6.1, affects-6.5, affects-7.1, affects-7.5, affects-8.1, affects-8.4, may-affects-5.4, may-affects-6.1, may-affects-6.5, may-affects-7.1, may-affects-7.5, may-affects-8.1.

In response to this:

/severity major
/label wrong-result

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[apollodafoni]

/impact wrong-result

[jebter]

`
TiDB [email protected]:test> select col1, cot(col1) from lrr_test where col1=-2308143;
+----------+--------------------+
| col1 | cot(col1) |
+----------+--------------------+
| -2308143 | 0.9135308955291633 |
+----------+--------------------+

1 row in set
Time: 0.008s
TiDB [email protected]:test> select col1, cot(col1) from lrr_test where cot(col1)=cot(-2308143);
+----------+--------------------+
| col1 | cot(col1) |
+----------+--------------------+
| -2308143 | 0.9135308955291633 |
+----------+--------------------+

1 row in set
Time: 0.006s
TiDB [email protected]:test> select tidb_version() \G
[ 1. row ]
tidb_version() | Release Version: v6.5.11
Edition: Community
Git Commit Hash: 3f20732
Git Branch: HEAD
UTC Build Time: 2024-09-18 04:07:03
GoVersion: go1.19.13
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

`

[windtalker]

The root cause is cot(-2308143) get different results in tikv and tidb:

mysql> select cast(col1 as double), cot(cast(col1 as double)), cot(-2308143) from lrr_test;
+----------------------+---------------------------+--------------------+
| cast(col1 as double) | cot(cast(col1 as double)) | cot(-2308143)      |
+----------------------+---------------------------+--------------------+
|             -2308143 |        0.9135308955291633 | 0.9135308955291636 |
|                   90 |       -0.5012027833801532 | 0.9135308955291636 |
+----------------------+---------------------------+--------------------+
2 rows in set (0.00 sec)

mysql> explain analyze select cast(col1 as double), cot(cast(col1 as double)), cot(-2308143) from lrr_test;
+-------------------------+---------+---------+-----------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id                      | estRows | actRows | task      | access object  | execution info                                                                                                                                                                         | operator info                                                                                                                           | memory    | disk |
+-------------------------+---------+---------+-----------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| TableReader_8           | 2.00    | 2       | root      |                | time:820.4µs, loops:2, cop_task: {num: 1, max: 725.2µs, proc_keys: 2, rpc_num: 1, rpc_time: 693.6µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                              | data:Projection_4                                                                                                                       | 293 Bytes | N/A  |
| └─Projection_4          | 2.00    | 2       | cop[tikv] |                | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 2, total_process_keys_size: 77, total_keys: 3, get_snapshot_time: 90.9µs, rocksdb: {key_skipped_count: 2, block: {}}}  | cast(test.lrr_test.col1, double BINARY)->Column#3, cot(cast(test.lrr_test.col1, double BINARY))->Column#4, 0.9135308955291636->Column#5 | N/A       | N/A  |
|   └─TableFullScan_7     | 2.00    | 2       | cop[tikv] | table:lrr_test | tikv_task:{time:0s, loops:1}                                                                                                                                                           | keep order:false, stats:pseudo                                                                                                          | N/A       | N/A  |
+-------------------------+---------+---------+-----------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
3 rows in set (0.01 sec)

[windtalker]

I test this in a v6.5.11 tidb cluster started by tiup, the result is empty.

mysql> select tidb_version();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                               |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.5.11
Edition: Community
Git Commit Hash: 3f2073261bf9f454c3c677d76d120c6ab40ca05c
Git Branch: HEAD
UTC Build Time: 2024-09-18 04:07:16
GoVersion: go1.19.13
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select col1, cot(col1) from lrr_test where cot(col1)=cot(-2308143);
+---------------------------+---------+-----------+----------------+----------------------------------------------------------------------------+
| id                        | estRows | task      | access object  | operator info                                                              |
+---------------------------+---------+-----------+----------------+----------------------------------------------------------------------------+
| Projection_4              | 1.60    | root      |                | test.lrr_test.col1, cot(cast(test.lrr_test.col1, double BINARY))->Column#3 |
| └─TableReader_7           | 1.60    | root      |                | data:Selection_6                                                           |
|   └─Selection_6           | 1.60    | cop[tikv] |                | eq(cot(cast(test.lrr_test.col1, double BINARY)), 0.9135308955291636)       |
|     └─TableFullScan_5     | 2.00    | cop[tikv] | table:lrr_test | keep order:false, stats:pseudo                                             |
+---------------------------+---------+-----------+----------------+----------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select col1, cot(col1) from lrr_test where cot(col1)=cot(-2308143);
Empty set (0.00 sec)

[windtalker]

` TiDB [email protected]:test> select col1, cot(col1) from lrr_test where col1=-2308143; +----------+--------------------+ | col1 | cot(col1) | +----------+--------------------+ | -2308143 | 0.9135308955291633 | +----------+--------------------+

1 row in set Time: 0.008s TiDB [email protected]:test> select col1, cot(col1) from lrr_test where cot(col1)=cot(-2308143); +----------+--------------------+ | col1 | cot(col1) | +----------+--------------------+ | -2308143 | 0.9135308955291633 | +----------+--------------------+

1 row in set Time: 0.006s TiDB [email protected]:test> select tidb_version() \G [ 1. row ] tidb_version() | Release Version: v6.5.11 Edition: Community Git Commit Hash: 3f20732 Git Branch: HEAD UTC Build Time: 2024-09-18 04:07:03 GoVersion: go1.19.13 Race Enabled: false TiKV Min Version: 6.2.0-alpha Check Table Before Drop: false Store: tikv

`

can you also check you plan to see if cot is pushed down to tikv?

[jebter]

explain select col1, cot(col1) from lrr_test where cot(col1)=cot(-2308143);

+-----------------------+---------+-----------+----------------+----------------------------------------------------------------------------+

| id                    | estRows | task      | access object  | operator info                                                              |
+-----------------------+---------+-----------+----------------+----------------------------------------------------------------------------+
| Projection_4          | 1.60    | root      |                | test.lrr_test.col1, cot(cast(test.lrr_test.col1, double BINARY))->Column#3 |
| └─TableReader_7       | 1.60    | root      |                | data:Selection_6                                                           |
|   └─Selection_6       | 1.60    | cop[tikv] |                | eq(cot(cast(test.lrr_test.col1, double BINARY)), 0.9135308955291633)       |
|     └─TableFullScan_5 | 2.00    | cop[tikv] | table:lrr_test | keep order:false, stats:pseudo                                             |
+-----------------------+---------+-----------+----------------+----------------------------------------------------------------------------+