pingcap / pingcap/tidb

t0 CROSS JOIN t1 ON FALSE when INTERSECTed with another query should perform no action

Open
#60,843 1 comment 0 reactions 0 assignees View on GitHub
severity/minor sig/planner type/bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.