apache / apache/druid

inner join condition on numeric column matching null behaves differently than string key matching null

Open
#9,980 0 comments 0 reactions 0 assignees View on GitHub
Area - Null Handling Area - SQL Bug
Dominant language
Java
Stars
14.1k
Forks
3.8k
Avg merge
2d 58m
Merged PRs (30d)
233

Description

### Affected Version

0.19

### Description

See the following query

```
select f.m1, w.floatDim, w.key, w.longDim, stringDimExt, sum(doubleDim) as dd from druid.foo f inner join index.windex w on f.m1 is null
group by 1,2, 3, 4, 5
having (w.key = '6' or f.m1 > 1) and dd > 0
```

Druid plans this query and it returns no results. Changing the condition to `is not null` returns what appears to be all results.
Looking at the explain plan for the query, it appears that Calcite optimizes this join condition to false / true respectively.

This behavior is different than using a string column in the join condition.

```
select f.m1, w.floatDim, w.key, w.longDim, stringDimExt, sum(doubleDim) as dd from druid.foo f inner join index.windex w on f.dim1 is null
group by 1,2, 3, 4, 5
having (w.key = '6' or f.m1 > 1) and dd > 0
```

dim1 is a key column and this query throws an ISE

Contributor guide

Open the contributing guide

Research direction

Start by running the two SQL queries and comparing their explain plans, focusing on Calcite's treatment of the numeric and string null join conditions. Trace the planner path that folds these conditions and reproduce the string-key query's ISE. Done means numeric null joins have consistent, correct results without the reported exception.

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.