Binder Error could not find key "key" in struct

[liquidcarbon]

What happens?

Hello, I recently updated DuckDB to 0.9.2 and I'm getting hard error on queries that I'm 99% (but not 100%) sure worked before.

When I run COALESCE(A.Value, '') I was expecting an empty string both when A.Value is NULL as well as when A.Value is absent. Now it errors out.

I'm not sure how to get around that, because the structure of the JSON files I'm querying is quite variable and the keys are indeed often missing.

Related: https://stackoverflow.com/questions/77188196/is-it-possible-to-suppress-binder-error-could-not-find-key-in-duckdb

To Reproduce

Create a STRUCT with a missing field and try to ask for it.

OS:

Linux

DuckDB Version:

0.9.2

DuckDB Client:

python

Full Name:

Alex

Affiliation:

self

Have you tried this on the latest nightly build?

I have tested with a nightly build

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

• Yes, I have

[liquidcarbon]

Dug in more and found the culprit.
Nothing seems to have changed on the DuckDB side.

We changed the FROM clause in these queries to scan a more narrow set of JSON files that have less diverse schemas, and so the query engine is unaware that these struct keys may exist.

Still, like the StackOverflow poster, I would prefer the behavior described (returning NULL for actual NULLS and missing keys alike). Is this possible to arrange, or to have an option in READ_JSON for enabling this?

A possible hack-around: in every partition, create a fake JSON with all possible keys. 🤔

[szarnyasg]

Hello @liquidcarbon, can you please create a reproducible example for this issue?

[liquidcarbon]

Something like this:

cat /tmp/a.json

{"a": {"key": "value"}}
{"b": {"yek": "eulav"}}

SELECT
    a,
    a.key,
    b,
    b.yek,
    --b.key,
FROM '/tmp/a.json'

This works as expected, even though there's no b in the first row and no a in the second row.

But if you ask for b.key you get

Binder Error: Could not find key "key" in struct

Candidate Entries: "yek"

And I think it should (have an option to) work and return a column of NULLs for b.key.

[szarnyasg]

Thanks! I'll discuss it with the team.

[serpiente]

Hello, is there an update on this or a workaround?

[soerenwolfers]

Imo, the important things here are (1) consistency (2) availability of some solution.

For example, (1) you shouldn't make struct return nulls on missing keys if lists don't return nulls on missing indices as well (or you might even argue if tables don't return null values on non existing column specifiers) (2) it would be perfectly fine to have a function like struct_extract that takes an additional default argument.

[vyadav12]

For other query engines like trino/athena, the utility function COALESCE(A.Value, '') works perfectly fine but not with duckdb when A.Value does not exist. As you know schemas evolve over the time, so this is must FEATURE for a query engine. Any solution planning to implement ? or workaround

[serpiente]

Maybe this works? https://stackoverflow.com/questions/77188196/is-it-possible-to-suppress-binder-error-could-not-find-key-in-duckdb