← Home

Data tools

Four DuckDB Behaviours That Return NULL Instead of an Error

DuckDB is famously forgiving, and that is the problem. Four everyday operations hand back a wrong answer with no warning — including one where count(*) succeeds and SELECT * crashes on the same file.

An error is a good outcome. It stops the pipeline, it names a line number, and somebody fixes it. The expensive failures are the ones that return a number.

DuckDB is unusually forgiving, and four of its everyday behaviours hand back a plausible wrong answer instead of complaining.

Pick one and see the real output

SELECT ([10, 20, 30])[1]

10

Every result in that component was executed against DuckDB 1.4.5 and recorded, rather than run in your browser — DuckDB-WASM is several megabytes and this page has a 220 KB JavaScript budget. The version is printed under the component so you can tell whether it still applies to yours.

1. list[0] is NULL, not an error

DuckDB lists are 1-indexed, like the rest of SQL. What makes this a hazard rather than a footnote is what happens when you reach for index 0:

SELECT ([10, 20, 30])[1];   -- 10
SELECT ([10, 20, 30])[0];   -- NULL
SELECT ([10, 20, 30])[-1];  -- 30

No error. Anyone arriving from Python or JavaScript writes [0], gets NULL for every row, and now has to work out whether the data is empty or the query is wrong. Negative indexing works and counts from the end, which is a nice touch and also one more way to be off by one.

2. A missing MAP key is NULL; a missing STRUCT key is an error

These two look like the same operation and behave completely differently:

SELECT MAP{'a': 1}['zzz'];  -- NULL
SELECT {'a': 1}.zzz;        -- Binder Error: Could not find key "zzz" in struct

The STRUCT version fails at bind time, before a single row is read, and the message even lists the keys that do exist. That is the behaviour you want. The MAP version cannot do that — a MAP’s keys are data, not schema, so DuckDB has no way to know at plan time that zzz will never appear.

Which means the choice between MAP and STRUCT is not only about flexibility. STRUCT buys you a typo check. If your keys are fixed, that is worth more than the flexibility you are giving up.

3. CSV inference samples the head, and count(*) hides it

This is the one that reaches production. A file with 20,500 clean integer rows and one N/A on the last line:

SELECT count(*) FROM read_csv('late.csv');
-- 20501

SELECT * FROM read_csv('late.csv') WHERE id = 99999;
-- Conversion Error: CSV Error on Line: 20502
-- Could not convert string "N/A" to 'BIGINT'

Same file, same session. The count succeeds because of projection pushdown: count(*) never materialises the value column, so the bad string is never converted. The moment a query actually reads that column, it throws.

The practical consequence is that a row-count sanity check passes on a file that will break the next step. If your ingest does “load, count, compare against expected, proceed”, it proceeds.

Scanning the whole file fixes the inference:

SELECT typeof(value) FROM read_csv('late.csv', sample_size = -1) LIMIT 1;
-- VARCHAR

That costs a full pass over the file. The cheaper habit is to declare the columns you actually depend on, so inference has nothing to get wrong.

4. 7 / 2 is 3.5

SELECT 7 / 2   AS v, typeof(v);  -- 3.5 · DOUBLE
SELECT 7 // 2  AS v, typeof(v);  -- 3   · INTEGER

DuckDB’s / is true division and returns DOUBLE even when both operands are integers. PostgreSQL truncates. Queries copied between the two silently change meaning, and the result is a number either way — so nothing tells you.

The pattern worth taking away

Three of these four fail by returning NULL, and NULL propagates. It flows through joins, survives aggregation as a skipped row, and lands in a dashboard as a slightly low number that nobody queries.

So the defensive habit is not “remember these four”. It is: when a column is unexpectedly full of NULLs, suspect the access syntax before you suspect the data. The data is usually fine.