Join hint doesn't work when table names are quoted
- 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