TiDB Fails to Handle EXCEPT Operation Involving CROSS JOIN with Empty Table
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Dear TiDB Developer,
TiDB fails to optimize queries containing `EXCEPT` operations when one branch includes a `CROSS JOIN` with an **empty table**. This leads to unnecessary memory consumption and query cancellation, even though the result should be deterministically empty.
I think this is a **common case** in actual production scenarios. It's important to clarify that users might **not intentionally** perform `CROSS JOIN` and `EXCEPT` operations on empty tables. Rather, **they may be unaware that a table is empty** . For example, when data has been deleted by another user or process. If TiDB can address this performance bug, it would significantly improve query efficiency and save users valuable time in such cases.
Thank you for your valuable time, looking forward to your reply!
Best regard,
### 1. Minimal reproduce step (Required)
```
mysql> use test;
mysql> CREATE TABLE t0 LIKE INFORMATION_SCHEMA.COLUMNS;
mysql> CREATE TABLE t1 LIKE t0;
mysql> CREATE TABLE empty_table LIKE t0;
mysql> INSERT INTO t0 SELECT * FROM INFORMATION_SCHEMA.COLUMNS;
mysql> INSERT INTO t1 SELECT * FROM t0;
mysql> SELECT * FROM t0 CROSS JOIN empty_table
Empty set (0.01 sec)
mysql> SELECT * FROM empty_table EXCEPT SELECT * FROM t0;
Empty set (0.02 sec)
mysql> SELECT * FROM t0 CROSS JOIN empty_table EXCEPT SELECT * FROM t0 CROSS JOIN t1;
ERROR 8175 (HY000): Your query has been cancelled due to exceeding the allowed memory limit for a single SQL query. Please try narrowing your query scope or increase the tidb_mem_quota_query limit and try again.[conn=1285554184]
```
### 2. What did you expect to see? (Required)
```
mysql> SELECT * FROM t0 CROSS JOIN empty_table EXCEPT SELECT * FROM t0 CROSS JOIN t1;
Empty set (0.xxsec)
```
### 3. What did you see instead (Required)
```
mysql> SELECT * FROM t0 CROSS JOIN empty_table EXCEPT SELECT * FROM t0 CROSS JOIN t1;
ERROR 8175 (HY000): Your query has been cancelled due to exceeding the allowed memory limit for a single SQL query. Please try narrowing your query scope or increase the tidb_mem_quota_query limit and try again.[conn=1285554184]
```
### 4. What is your TiDB version? (Required)
| Release Version: v8.5.1
Edition: Community
Git Commit Hash: https://github.com/pingcap/tidb/commit/fea86c8e35ad4a86a5e1160701f99493c2ee547c
Git Branch: HEAD
UTC Build Time: 2025-01-16 07:38:34
GoVersion: go1.23.4
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
Contributor guide
Assessment
This issue has not been assessed yet.