Unnest struct expression can't be aliased
- 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
Assessment
This issue has not been assessed yet.