apache / apache/druid

SQL query fails in semi join when using NOT IN with subquery

Open
#8,833 1 comment 0 reactions 0 assignees View on GitHub
Area - Querying
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.