INTERNAL Error when using UNLIST

[rsund]

What happens?

Query throws an INTERNAL Error: Failed to bind column reference "r" [5.0] (bindings: [7.0]). Moreover, any query after that gives:

FATAL Error: Failed: database has been invalidated because of a previous fatal error. The database must be restarted prior to being used again.
Original error: "INTERNAL Error: Failed to bind column reference "r" [5.0] (bindings: [7.0])"

Happens in the latest master (tested with R client) and also in WASM shell.

Somewhat similar to #5660, but no visible GROUP BY ALL here.

To Reproduce

SELECT unlist(str_split('A',' ')) AS "r", CASE WHEN ("r" = 'A') THEN (-1) ELSE 0 END AS "x", sum("x") OVER () AS "s";
SELECT 1;

OS:

MacOS 10.15.7

DuckDB Version:

0.6.1

DuckDB Client:

Wasm shell, R

Full Name:

Reijo Sund

Affiliation:

University of Eastern Finland

Have you tried this on the latest master branch?

• [X] I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• [X] I agree

[Tishj]

Thanks for the report!

InternalException derives from FatalException, when a FatalException is thrown it invalidates the database, causing another FatalException to be thrown when it's used before restarting. That's not an issue in and of itself.

Looking at the other issue you linked, I think this is definitely a duplicate, but it helps narrow down the cause :)

[rsund]

I'm not quite sure to which part the error is related as it vanishes if any of those three parts (unlist, case when, sum over()) is left out. Maybe something to do with "sequential" referencing to the earlier parts of the query?!

[Tishj]

Made a (slightly) smaller reproducable example

select unnest([5,4,3]) as r, r as x, sum(r) OVER ();

Note: both sum(x) and sum(r) variations break like this

To me it seems related to unnest combined with sequential referencing

[Tishj]

But you are right, they are less related than I initially thought, though I do suspect they have the same cause

[hawkfish]

This may be related to #5669 - and if so, it's a much simpler repro.

[hawkfish]

No it's different.

[l1t1]

D SELECT unlist(str_split('A',' ')) AS "r", max("r") OVER () AS "s";
Error: INTERNAL Error: Failed to bind column reference "r" [5.0] (bindings: [7.0])
D SELECT max(unnest([1,2])) OVER () AS "s";
Error: INTERNAL Error: Failed to bind column reference "UNNEST(list_value(1, 2))" [5.0] (bindings: [7.0])
D SELECT max(unnest([1,2]))AS "s";
Error: Binder Error: UNNEST not supported here
LINE 1: SELECT max(unnest([1,2]))AS "s";
                   ^
``

[Tishj]

I just saw this issue I filed a while ago, it seems related to this https://github.com/duckdb/duckdb/issues/5150

[Tmonster]

Worked on some binder bugs sometime in november, so I can take a look at this

[github-actions[bot]]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[github-actions[bot]]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[github-actions[bot]]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[rsund]

select unnest([ 5, 4, 3 ]) as r, r as x, sum(r) over(); still reproduces the internal error in DuckDB Web Shell [Database: v0.10.1 - Package: @duckdb/[email protected]]

[Maxxen]

On main this no longer throws an internal error, but instead a proper exception:

v0.10.2-dev722 07352ec6cf
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D select unnest([ 5, 4, 3 ]) as r, r as x, sum(r) over();;
Binder Error: UNNEST not supported here
LINE 1: select unnest([ 5, 4, 3 ]) as r, r as x, sum(r...

So I guess this is just not supported.

[Tmonster]

@rsund can you check again with v0.10.2? You should get a Binder Error now instead of an InternalException

[szarnyasg]

This throws a Binder Error with 0.10.2:

Binder Error: UNNEST not supported here