t0 CROSS JOIN t1 ON FALSE when INTERSECTed with another query should perform no action
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
### 1. Minimal reproduce step (Required)
DearTiDB Developer:
I find a performance bug as follow:
In the case of q1 INTERSECT q2, when q2 takes the form of t0 CROSS JOIN t1 ON FALSE, this operation should not require physical reads. Since t0 CROSS JOIN t1 ON FALSE by definition always returns an empty set, the intersection q1 INTERSECT q2 must logically yield an empty result without needing to execute the underlying query.
```
mysql> use INFORMATION_SCHEMA;
mysql> SELECT * FROM TABLES CROSS JOIN COLUMNS ON TABLES.TABLE_NAME > COLUMNS.TABLE_NAME
INTERSECT
SELECT * FROM TABLES CROSS JOIN COLUMNS ON FALSE;
Empty set (17.95 sec)
```
### 2. What did you expect to see? (Required)
```
mysql> SELECT * FROM TABLES CROSS JOIN COLUMNS ON TABLES.TABLE_NAME > COLUMNS.TABLE_NAME
INTERSECT
SELECT * FROM TABLES CROSS JOIN COLUMNS ON FALSE;
Empty set (00.00 sec)
```
### 3. What did you see instead (Required)
```
mysql> SELECT * FROM TABLES CROSS JOIN COLUMNS ON TABLES.TABLE_NAME > COLUMNS.TABLE_NAME
INTERSECT
SELECT * FROM TABLES CROSS JOIN COLUMNS ON FALSE;
Empty set (17.95 sec)
```
### 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.