dolthub / dolthub/dolt

Join hint doesn't work when table names are quoted

Open
#10,528 1 comment 0 reactions 0 assignees View on GitHub
analyzer bug
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 8h
Merged PRs (30d)
120

Description

Discovered this bug when trying to repro #10527. I was using quotes for the table name because the table name had periods in it.
```
tmp/main*> explain plan SELECT /*+ LOOKUP_JOIN(t1, `t2.with.dot`)*/ t1.id, t1.name, t1.location, `t2.with.dot`.country FROM t1 JOIN `t2.with.dot` ON t1.id = `t2.with.dot`.id WHERE t1.id = 1;
+-----------------------------------------------------------------+
| plan |
+-----------------------------------------------------------------+
| Project |
| ├─ columns: [t1.id, t1.name, t1.location, t2.with.dot.country] |
| └─ InnerJoin |
| ├─ (t1.id = t2.with.dot.id) |
| ├─ Table |
| │ ├─ name: t2.with.dot |
| │ └─ columns: [id country] |
| └─ IndexedTableAccess(t1) |
| ├─ index: [t1.id,t1.name,t1.location] |
| ├─ filters: [{[1, 1], [NULL, ∞), [NULL, ∞)}] |
| └─ columns: [id name location] |
+-----------------------------------------------------------------+
11 rows in set (0.00 sec)
```

Join hint works when table name is not wrapped in quotes. But this means we probably don't handle table names with a db prefix (`[db name].[table name]`) correctly.
```
tmp/main*> explain plan SELECT /*+ LOOKUP_JOIN(t1, t2.with.dot)*/ t1.id, t1.name, t1.location, `t2.with.dot`.country FROM t1 JOIN `t2.with.dot` ON t1.id = `t2.with.dot`.id WHERE t1.id = 1;
+--------------------------------------------------------------------------+
| plan |
+--------------------------------------------------------------------------+
| Project |
| ├─ columns: [t1.id, t1.name, t1.location, t2.with.dot.country] |
| └─ LookupJoin |
| ├─ IndexedTableAccess(t1) |
| │ ├─ index: [t1.id,t1.name,t1.location] |
| │ ├─ filters: [{[1, 1], [NULL, ∞), [NULL, ∞)}] |
| │ └─ columns: [id name location] |
| └─ IndexedTableAccess(t2.with.dot) |
| ├─ index: [t2.with.dot.name,t2.with.dot.id,t2.with.dot.country] |
| ├─ columns: [id country] |
| └─ keys: t1.id, t1.id, t1.id |
+--------------------------------------------------------------------------+
11 rows in set (0.00 sec)
```

Contributor guide

No contributing guide indexed for this repository

Research direction

Reproduce the two EXPLAIN PLAN queries in the issue, comparing LOOKUP_JOIN with an unquoted and a quoted table name. Trace the LOOKUP_JOIN handling for quoted identifiers and database-qualified names. Done means the quoted table name, including names containing periods, produces the expected LookupJoin plan.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.