pingcap / pingcap/tidb

EXCEPT with identical left/right queries isn't eliminated

Open
#60,850 1 comment 0 reactions 1 assignee Claimed by @hawkingrei 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
Dear TiDB Developer.

q1 EXCEPT q1 should always return an empty set and consume minimal execution time. It should not be executed, but the empty set is returned quickly and directly. However, OceanBase execute it. We think this is a performance bug and should be optimized.

Best regard,

### 1. Minimal reproduce step (Required)
```
mysql> use INFORMATION_SCHEMA;

mysql> SELECT * FROM TABLES CROSS JOIN COLUMNS ON TABLES.TABLE_NAME != COLUMNS.TABLE_NAME
EXCEPT
SELECT * FROM TABLES CROSS JOIN COLUMNS ON TABLES.TABLE_NAME != COLUMNS.TABLE_NAME;
Empty set (00.00 sec)
```
### 2. What did you expect to see? (Required)
```
mysql> SELECT * FROM TABLES CROSS JOIN COLUMNS ON TABLES.TABLE_NAME != COLUMNS.TABLE_NAME
EXCEPT
SELECT * FROM TABLES CROSS JOIN COLUMNS ON TABLES.TABLE_NAME != COLUMNS.TABLE_NAME;
Empty set (3 min 32.57 sec)
```
### 3. What did you see instead (Required)
```
mysql> SELECT * FROM TABLES CROSS JOIN COLUMNS ON TABLES.TABLE_NAME != COLUMNS.TABLE_NAME
EXCEPT
SELECT * FROM TABLES CROSS JOIN COLUMNS ON TABLES.TABLE_NAME != COLUMNS.TABLE_NAME;
Empty set (3 min 32.57 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.