tidb
tidb copied to clipboard
Published
20 hours ago •
pingcap
pingcap
When executing the GROUP BY and HAVING clauses, TiDB returns unexpected results
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
CREATE TABLE t0 (
c0 bigint unsigned,
KEY i0 (c0)
);
INSERT INTO t0 VALUES (11823132370799012635);
SELECT count(a.c0) FROM t0 a LEFT OUTER JOIN t0 b ON a.c0 < b.c0 GROUP BY a.c0 HAVING (a.c0 > -9223372036854775808) AND NOT (a.c0 <= 9223372036854775807);
2. What did you expect to see? (Required)
+-------------+
| count(a.c0) |
+-------------+
| 1 |
+-------------+
3. What did you see instead (Required)
MySQL> SELECT count(a.c0) FROM t0 a LEFT OUTER JOIN t0 b ON a.c0 < b.c0 GROUP BY a.c0 HAVING (a.c0 > -9223372036854775808) AND NOT (a.c0 <= 9223372036854775807);
Empty set (0.00 sec)
4. What is your TiDB version? (Required)
MySQL> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.0.0
Edition: Community
Git Commit Hash: 8ba1fa452b1ccdbfb85879ea94b9254aabba2916
Git Branch: HEAD
UTC Build Time: 2024-03-28 14:22:04
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)
It seems a planner issue, the planner generated a cartesian inner join executor:
mysql> explain analyze select * from t0 left outer join t0 as a on t0.c0 < a.c0 where (a.c0 > -9223372036854775808) AND NOT (a.c0 <= 9223372036854775807);
+-------------------------------+---------+---------+-----------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------------+---------+---------+-----------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------+-----------+---------+
| Projection_9 | 0.33 | 0 | root | | time:1.18ms, loops:1, RU:0.500684, Concurrency:OFF | test1.t0.c0, test1.t0.c0 | 8.61 KB | N/A |
| └─HashJoin_11 | 0.33 | 0 | root | | time:1.18ms, loops:1, build_hash_table:{total:32.9µs, fetch:32.9µs, build:0s} | CARTESIAN inner join, other cond:lt(test1.t0.c0, test1.t0.c0) | 0 Bytes | 0 Bytes |
| ├─TableDual_12(Build) | 0.33 | 0 | root | | time:10.7µs, loops:1 | rows:0 | N/A | N/A |
| └─IndexReader_14(Probe) | 1.00 | 1 | root | | time:1.06ms, loops:1, cop_task: {num: 1, max: 1.07ms, proc_keys: 1, tot_proc: 74.9µs, tot_wait: 242.4µs, rpc_num: 1, rpc_time: 1.03ms, copr_cache_hit_ratio: 0.00, build_task_duration: 11.3µs, max_distsql_concurrency: 1} | index:IndexFullScan_13 | 262 Bytes | N/A |
| └─IndexFullScan_13 | 1.00 | 1 | cop[tikv] | table:t0, index:i0(c0) | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 46, total_keys: 2, get_snapshot_time: 209.5µs, rocksdb: {key_skipped_count: 1, block: {}}}, time_detail: {total_process_time: 74.9µs, total_wait_time: 242.4µs, tikv_wall_time: 537.8µs} | keep order:false, stats:pseudo | N/A | N/A |
+-------------------------------+---------+---------+-----------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------+-----------+---------+
5 rows in set (0.01 sec)
