hash joins don't work for enums of different types
- Dominant language
- Go
- Stars
- 24.4k
- Forks
- 873
- Avg merge
- 1d 8h
- Merged PRs (30d)
- 120
Description
Setup:
```
"create table animals(e enum('rat','ox','tiger','dog') primary key);",
"create table pets(e enum('cat','dog','fish','rat'));",
"insert into animals values('rat'), ('dog');",
"insert into pets values ('cat'), ('dog'), ('rat');",
```
When testing using `TestSingleScript`, this query returns an empty set, when `{{"rat", "rat"}, {"dog", "dog"}}` is expected.
`select /*+ HASH_JOIN(animals, pets)*/ * from animals join pets on animals.e=pets.e order by animals.e;`
The same query, without the join hint, tested as a `joinOpTest` test also fails when biased for hash joins, which makes sense. What doesn't make sense is that it's able to match the `{"rat", "rat"}` row but not the `{"dog", "dog"}` row.
related to #10316
Contributor guide
No contributing guide indexed for this repository
Research direction
Reproduce the failure using the TestSingleScript setup and the joinOpTest case mentioned in the issue, forcing the HASH_JOIN hint or hash-join bias. Trace the enum comparison path used by the hash join and compare it with the non-hash join behavior. Done means the query returns both expected pairs, {"rat", "rat"} and {"dog", "dog"}, for enums of different types.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 52/100