[BUG] Databricks: dbt0217: Invalid alias for non-struct type, expected 1 alias, found 3
- Dominant language
- Rust
- Stars
- 13.8k
- Forks
- 2.6k
- Avg merge
- 21h 31m
- Merged PRs (30d)
- 56
Description
**Describe the bug**
In Databricks / Spark SQL, there is a function named 'stack'.
https://spark.apache.org/docs/latest/api/sql/index.html#stack
The stack-function can be used to generate n rows based on column expressions.
example:
```
SELECT
stack(3,
'first_row', COLUMN_1,
'second_row', COLUMN_2,
'third_row', COLUMN_3)
AS (row_string, column_value)
FROM my_table;
```
This query would generate three rows for each row in the dataset, with two columns: row_string and column_value based on to pair-values I provided to the function. The first parameter is the amount of columns created based on the provided values.
Maybe my explaination is a little bit complicated, but it is pretty straight forward if you try it out.
However, while the stack function works in Databricks / Spark SQL with any mount of columns generated, dbt fusion can not execute in whenever you want to generate more than one column. It seems to have an issue with the AS (x, y, z) expression.
The VS Code extension shows following error; also I can not execute the query with the vs code extension.
> Databricks: dbt0217: Invalid alias for non-struct type, expected 1 alias, found 3
When I only use one column, it works.
When I leave away the alias, by default the names 'col0', 'col1' etc are used. And this works with dbt fusion!
However, whenever I want to reference these columns, it will fail again.
The stack function is definitely used correct, since it work directly inside the Databricks SQL and with dbt-core.
**What version of dbt Fusion is this bug in? (find out by running `dbt --version`)**
dbt-fusion 2.0.0-preview.23
**Is this a discrepancy between the dbt Fusion Engine and dbt Core? Check one.**
- [X] YES
- [ ] NO
**To Reproduce**
1. Open [this workspace](https://github.com/dbt-labs/scratch/tree/main/repros/fusion-839)
2. Follow the instructions in the README
Or see above.
**Expected behavior**
dbt fusion should run the query using stack function without any issue
**Screenshots**
If applicable, add screenshots to help explain your problem.
**Operating System and CPU Type (please complete the following information):**
Windows
X86
Contributor guide
Assessment
This issue has not been assessed yet.