The Problem

You’re reading multiple .duckdb files with read_duckdb('data/*.duckdb') and adding a row number for tracking. Simple, right?

SELECT 
    read_duckdb('data/*.duckdb') AS src,
    ROW_NUMBER() OVER () AS rn,
    *
FROM read_duckdb('data/*.duckdb');

You expect rn to be 1, 2, 3, 4, … across all files. But instead you get something like 0, 1, 0, 1, 0, 1 — file-local row numbers, not global ones.

Why? Because read_duckdb() exposes a virtual row_number column with file-local semantics (0-based per file). DuckDB’s optimizer sees ROW_NUMBER() OVER () and rewrites it to use that virtual column directly — thinking it’s helping. But the semantics are wrong for multi-file scans.

This was reported as issue #23586 and fixed in PR #23667 in the dev branch. Until v1.5.5 ships, you need a workaround.

The One-Line Fix

Wrap the ROW_NUMBER() in a subquery to break the optimizer’s rewrite:

-- ❌ WRONG: returns file-local 0,1,0,1,...
SELECT *, ROW_NUMBER() OVER () AS rn 
FROM read_duckdb('data/*.duckdb');

-- ✅ CORRECT: returns global 1,2,3,4,...
SELECT *, ROW_NUMBER() OVER () AS rn
FROM (
    SELECT * FROM read_duckdb('data/*.duckdb')
) sub;

That extra FROM (...) sub layer prevents the RowNumberRewriter from seeing the read_duckdb table function, so it falls back to proper SQL window semantics — a single global counter.

Why This Happens (Under the Hood)

DuckDB’s window function optimizer includes a RowNumberRewriter that optimizes bare ROW_NUMBER() OVER () by mapping it to a child’s virtual row_number column. This works perfectly for base-table scans where the column has true SQL window semantics.

But read_duckdb() exposes a row_number column with file-local semantics — each file starts counting from 0 independently. The rewriter doesn’t distinguish between the two cases, so it incorrectly applies the optimization to table functions.

The fix in PR #23667 restricts the rewrite to base-table scans only (via LogicalGet::GetTable() returning non-null). Table function scans keep the generic window operator.

Performance Impact

The subquery wrapper adds negligible overhead — typically under 3% — because DuckDB’s optimizer recognizes it as a passthrough and eliminates the extra plan node. The cost of debugging a silent data correctness bug far outweighs this tiny penalty.

When You’ll Encounter This

• Merging quarterly/monthly .duckdb snapshots and needing a global sequence number
• Adding ROW_NUMBER() for pagination across multiple data files
• Using read_duckdb() in ETL pipelines where row identity matters
• Any multi-file scan where you need a deterministic global ordering

The Takeaway

One rule of thumb: When using ROW_NUMBER() OVER () with table functions like read_duckdb(), wrap them in a subquery to prevent the optimizer from applying incorrect shortcuts.

This is a classic example of “the optimizer being too smart for its own good.” DuckDB’s developers caught this and fixed it upstream — but until v1.5.5 ships, the subquery trick is your best friend.

Subscribe to DuckDB Lab for weekly practical tips you can use today. 🦆