apache / apache/datafusion

Unnest struct expression can't be aliased

Open
#12,794 3 comments 0 reactions 1 assignee Claimed by @xudong963 View on GitHub
bug
Dominant language
Rust
Stars
9.3k
Forks
2.4k
Avg merge
3d 7h
Merged PRs (30d)
344

Description

### Describe the bug

Consider the following SQL
```sql
> CREATE TABLE unnest_table
AS VALUES
([1,2,3], [7], 1, [13, 14], struct(1,2)),
([4,5], [8,9,10], 2, [15, 16], struct(3,4)),
([6], [11,12], 3, null, null),
([12], [null, 42, null], null, null, struct(7,8)),
-- null array to verify the `preserve_nulls` option
(null, null, 4, [17, 18], null)
;
0 row(s) fetched.
Elapsed 0.022 seconds.

> select unnest(column5) from unnest_table;
+---------------------------------------------+---------------------------------------------+
| unnest_placeholder(unnest_table.column5).c0 | unnest_placeholder(unnest_table.column5).c1 |
+---------------------------------------------+---------------------------------------------+
| 1 | 2 |
| 3 | 4 |
| | |
| 7 | 8 |
| | |
+---------------------------------------------+---------------------------------------------+
5 row(s) fetched.
Elapsed 0.006 seconds.

> select unnest(column5) u1 from unnest_table;
Internal error: unnest on struct can only be applied at the root level of select expression.
This was likely caused by a bug in DataFusion's code and we would welcome that you file an bug report in our issue tracker
>
```

SQL will fail if we try to add an alias for the unnest expression.

Actually, adding an alias for an unnest struct expression is meaningless. The unnest result is a dataset with multiple columns. However, I found that DuckDB can handle this kind of SQL but does nothing.
```sql
D CREATE TABLE unnest_table
AS VALUES
([1,2,3], [7], 1, [13, 14], {'c1': 1, 'c2': 2}),
([4,5], [8,9,10], 2, [15, 16], {'c1':3,'c2':4}),
([6], [11,12], 3, null, null),
([12], [null, 42, null], null, null, {'c1':7,'c2':8}),
-- null array to verify the `preserve_nulls` option
(null, null, 4, [17, 18], null)
;
D select unnest(col4) u1 from unnest_table;
┌───────┬───────┐
│ c1 │ c2 │
│ int32 │ int32 │
├───────┼───────┤
│ 1 │ 2 │
│ 3 │ 4 │
│ │ │
│ 7 │ 8 │
│ │ │
└───────┴───────┘
```

I think we can follow this behavior to make this SQL work.

### To Reproduce

Run the SQL mentioned above.

### Expected behavior

Follow the DuckDB behavior. The SQL should work.

### Additional context

_No response_

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.