dbt-labs / dbt-labs/dbt

[BUG] Databricks: dbt0217: Invalid alias for non-struct type, expected 1 alias, found 3

Open
#13,831 1 comment 0 reactions 0 assignees View on GitHub
adapter:databricks area:static-analysis discrepancy engine:v2 status:has-repro status:triage type:bug
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

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.