Open apollodafoni opened 2 hours ago
/severity major /label wrong-result
@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
.
/impact wrong-result
` TiDB root@127.0.0.1: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 root@127.0.0.1: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 root@127.0.0.1:test> select tidb_version() \G [ 1. row ] tidb_version() | Release Version: v6.5.11 Edition: Community Git Commit Hash: 3f2073261bf9f454c3c677d76d120c6ab40ca05c 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
`
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)
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)
` TiDB root@127.0.0.1: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 root@127.0.0.1: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 root@127.0.0.1:test> select tidbversion() \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?
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
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: 2205f332ffcf3553ffc49438cfe1087a0f08dd18 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