EXPLAIN ANALYZE FORMAT=brief fails with duplicate Projection executor id for FTS TiFlash MPP join
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
### 1. Minimal reproduce step
```sql
DROP DATABASE IF EXISTS fts_jq_min_repro;
CREATE DATABASE fts_jq_min_repro;
USE fts_jq_min_repro;
CREATE TABLE obj (
workspace_id VARCHAR(64),
id BIGINT,
label VARCHAR(64),
content TEXT,
PRIMARY KEY (workspace_id, id) CLUSTERED,
FULLTEXT INDEX idx_fts_content (content)
);
CREATE TABLE rel (
workspace_id VARCHAR(64),
id BIGINT,
object_id BIGINT,
referenced_object_id BIGINT,
PRIMARY KEY (workspace_id, id) CLUSTERED
);
INSERT INTO obj VALUES
('w1', 1, 'A-001', 'Fagor washer text'),
('w1', 2, 'B-001', 'normal target text'),
('w1', 3, 'C-001', 'Fagor dryer text'),
('w1', 4, 'D-001', 'another target text');
INSERT INTO rel VALUES
('w1', 1, 1, 2),
('w1', 2, 3, 4);
ALTER TABLE obj SET TIFLASH REPLICA 1;
ALTER TABLE rel SET TIFLASH REPLICA 1;
```
Wait until TiFlash replicas are available:
```sql
SELECT table_name, replica_count, available, progress
FROM information_schema.tiflash_replica
WHERE table_schema = 'fts_jq_min_repro';
```
Then run:
```sql
USE fts_jq_min_repro;
SET SESSION tidb_enforce_mpp = ON;
EXPLAIN ANALYZE FORMAT='brief'
SELECT /*+ READ_FROM_STORAGE(TIFLASH[r]) */ r.referenced_object_id
FROM obj o1
JOIN rel r ON o1.id = r.object_id
WHERE o1.workspace_id = 'w1'
AND r.workspace_id = 'w1'
AND MATCH(o1.content) AGAINST('Fagor' IN BOOLEAN MODE);
```
### 2. What did you expect to see?
`EXPLAIN ANALYZE FORMAT='brief'` should execute successfully and return the brief runtime plan.
### 3. What did you see instead?
The query fails with the following TiFlash error:
```text
ERROR 1105 (HY000): DB::TiFlashException: in tree based request, executor id `Projection` duplicate, which is unexpected.
```
The issue is specific to `EXPLAIN ANALYZE FORMAT='brief'`.
The following query can generate the plan successfully:
```sql
EXPLAIN FORMAT='brief'
SELECT /*+ READ_FROM_STORAGE(TIFLASH[r]) */ r.referenced_object_id
FROM obj o1
JOIN rel r ON o1.id = r.object_id
WHERE o1.workspace_id = 'w1'
AND r.workspace_id = 'w1'
AND MATCH(o1.content) AGAINST('Fagor' IN BOOLEAN MODE);
```
Non-error plan:
```text
+----------------------------------------+---------+--------------+------------------------------------------+----------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------------+---------+--------------+------------------------------------------+----------------------------------------------------------------------------------------------------------------------+
| TableReader | 1.00 | root | | MppVersion: 3, data:ExchangeSender |
| └─ExchangeSender | 1.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─Projection | 1.00 | mpp[tiflash] | | fts_jq_min_repro.rel.referenced_object_id |
| └─HashJoin | 1.00 | mpp[tiflash] | | inner join, equal:[eq(fts_jq_min_repro.obj.id, fts_jq_min_repro.rel.object_id)] |
| ├─ExchangeReceiver(Build) | 1.00 | mpp[tiflash] | | |
| │ └─ExchangeSender | 1.00 | mpp[tiflash] | | ExchangeType: Broadcast, Compression: FAST |
| │ └─Projection | 1.00 | mpp[tiflash] | | fts_jq_min_repro.obj.id |
| │ └─Selection | 1.00 | mpp[tiflash] | | eq(fts_jq_min_repro.obj.workspace_id, "w1") |
| │ └─IndexRangeScan | 2.00 | mpp[tiflash] | table:o1, index:idx_fts_content(content) | range:["w1","w1"], search func:fts_match_word("Fagor", fts_jq_min_repro.obj.content), keep order:false, stats:pseudo |
| └─Projection(Probe) | 1.00 | mpp[tiflash] | | fts_jq_min_repro.rel.referenced_object_id, fts_jq_min_repro.rel.object_id |
| └─Selection | 1.00 | mpp[tiflash] | | not(isnull(fts_jq_min_repro.rel.object_id)) |
| └─TableRangeScan | 1.25 | mpp[tiflash] | table:r | range:["w1","w1"], keep order:false, stats:pseudo |
+----------------------------------------+---------+--------------+------------------------------------------+----------------------------------------------------------------------------------------------------------------------+
```
Also, `EXPLAIN ANALYZE` without `FORMAT='brief'` can execute successfully.
### 4. Branch
```text
feature/fts
```
### 5. Notes
This was found while testing FTS join query enhancement. The failure happens when an FTS `IndexRangeScan` participates in a TiFlash MPP `HashJoin`, and the query is executed through `EXPLAIN ANALYZE FORMAT='brief'`.
The non-brief `EXPLAIN ANALYZE` output uses executor ids with suffixes such as `Projection_71`, while the brief format removes suffixes and appears to send duplicate `Projection` executor ids to TiFlash in the tree-based request.
Contributor guide
Assessment
This issue has not been assessed yet.