apache / apache/druid

Incorrect output for UNNEST on nested arrays

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

Description

We are observing incorrect output for UNNEST when applying it on nested arrays

### Affected Version

32.0.1

### Description
Given the following "values" table:
__time | value
-------------------------------|-------
2025-04-24T21:20:10.000Z | 1
2025-04-24T21:21:10.000Z | 2
2025-04-24T21:22:10.000Z | 3

And the following query:
```
SELECT *
FROM (
SELECT ARRAY_AGG(ARRAY[__time, "value"], 1000) AS array_agg
FROM "values"
)
CROSS JOIN UNNEST(array_agg) AS unnested
```
We are receiving this as the result:

array_agg|unnested
-------------------------------|-------
[[1745529610000,1],[1745529670000,2],[1745529730000,3]] | [1745529610000]
[[1745529610000,1],[1745529670000,2],[1745529730000,3]] | null

This is the expected output:
array_agg|unnested
-------------------------------|-------
[[1745529610000,1],[1745529670000,2],[1745529730000,3]] | [1745529610000,1]
[[1745529610000,1],[1745529670000,2],[1745529730000,3]] | [1745529670000,2]
[[1745529610000,1],[1745529670000,2],[1745529730000,3]] | [1745529670000,3]

We are also observing similar behavior when trying to UNNEST with an array of JSON_OBJECTS:
```
SELECT *
FROM (
SELECT ARRAY_AGG(JSON_OBJECT('t':__time, 'v':"value"), 1000) AS array_agg
FROM "values"
)
CROSS JOIN UNNEST(array_agg) AS unnested
```
We are receiving this as the result:
array_agg|unnested
-------------------------------|-------
[{"t":1745529610000,"v":1},{"t":1745529670000,"v":2},{"t":1745529730000,"v":3}] | {"t":1745529610000,"v":null}

We are also observing incorrect results when trying to UNNEST an array of doubles:
```
SELECT *
FROM (
SELECT ARRAY_AGG("value", 1000) AS array_agg
FROM "values"
)
CROSS JOIN UNNEST(array_agg) AS unnested
```
array_agg|unnested
-------------------------------|-------
null | 1
null | 2
null | 3

Expected output:
array_agg|unnested
-------------------------------|-------
[1, 2, 3] | 1
[1, 2, 3] | 2
[1, 2, 3] | 3

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the three UNNEST queries from the issue against Druid 32.0.1, then trace the SQL UNNEST and array/complex-value handling entry points. Done means nested arrays, JSON objects, and doubles retain the complete array values and produce the three expected rows shown.

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
Active
Clarity
Mostly clear
Newbie friendliness
55/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.