SQL query fails in semi join when using NOT IN with subquery
- Dominant language
- Java
- Stars
- 14.1k
- Forks
- 3.8k
- Avg merge
- 2d 58m
- Merged PRs (30d)
- 233
Description
### Affected Version
0.16.0
### Description
Executing an SQL query from UI,
```
SELECT dimension, SUM(totals)
FROM datasource
WHERE "__time" BETWEEN TIME_FORMAT(TIMESTAMPADD(DAY, -2,CURRENT_DATE), 'yyyy-MM-dd hh:mm:ss')
AND TIME_FORMAT(TIMESTAMPADD(DAY, -1,CURRENT_DATE), 'yyyy-MM-dd hh:mm:ss')
AND dimension NOT IN (
SELECT dimension from datasource1 WHERE d="example"
)
GROUP BY dimension
```
Gets me:
```
Unknown exception / Cannot build plan for query:
```
followed by the original query.
The exact same query works when i use IN keyword.
Contributor guide
Research direction
Start by reproducing the query against Apache Druid 0.16.0 and compare its planning behavior with the equivalent IN query. Trace the semi-join planning path responsible for NOT IN subqueries; done means the query builds and executes without the planning exception while preserving expected NOT IN results.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100