When executing the GROUP BY and HAVING clauses, TiDB returns unexpected results

[sjyango]

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)

[yibin87]

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)

[yibin87]

/sig planner

[yibin87]

/remove-sig execution

[hawkingrei]

it has been fixed by #52225