pingcap / pingcap/tidb

EXPLAIN ANALYZE FORMAT=brief fails with duplicate Projection executor id for FTS TiFlash MPP join

Open
#68,568 2 comments 0 reactions 0 assignees View on GitHub
feature/developing severity/moderate sig/diagnosis type/bug
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

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.