Same query errors before CHECKPOINT and succeeds after it: deleted rows are still counted in cardinality estimates and flip the join order

What happens?

A join between an INTEGER column and a DATE column is accepted by the binder, which inserts a cast. Whether that cast is ever evaluated depends on the join order, and the join order depends on a cardinality estimate that still counts rows deleted earlier in the same session. So the identical query, on identical data, in one session:

• before a CHECKPOINT -> Conversion Error: Unimplemented type for cast (INTEGER -> DATE) when casting from source column c0
• after a CHECKPOINT -> returns its result (0 rows)

EXPLAIN shows the mechanism directly. The table t1 has had both its rows deleted:

before CHECKPOINT                          after CHECKPOINT
  Table: memory.main.t1                      Table: memory.main.zseed
  ~2 rows            <-- deleted rows        ~0 rows            <-- correct
  Conditions: c0 = CAST(c0 AS DATE)          Conditions: CAST(c0 AS DATE) = c0

The estimate for the emptied table drops from ~2 to ~0 across the checkpoint, the build and probe sides swap, and the cast moves to the other side of the condition — where it is actually evaluated, and fails.

This matters in ordinary use because checkpoints are automatic: they happen on database close and once the WAL passes checkpoint_threshold (default 16 MB). A query can therefore fail now and succeed later, or the reverse, with no change to data or schema.

Reproduces on current main and on released 1.5.5.

To Reproduce

Two runs, identical except for the CHECKPOINT. No settings are changed.

-- Run A: no checkpoint -> Conversion Error
CREATE TABLE zseed(c0 INTEGER, c1 INTEGER);
CREATE TABLE t1(c0 DATE);
INSERT INTO t1 VALUES (DATE '1970-01-12');
INSERT INTO t1 VALUES (DATE '1969-12-19');
DELETE FROM t1;
INSERT INTO zseed VALUES (-414224324, NULL);
SELECT zseed.rowid, zseed.c0 FROM zseed NATURAL RIGHT JOIN t1 WHERE zseed.c0;

-- Run B: the same statements with CHECKPOINT inserted -> 0 rows, no error
CREATE TABLE zseed(c0 INTEGER, c1 INTEGER);
CREATE TABLE t1(c0 DATE);
INSERT INTO t1 VALUES (DATE '1970-01-12');
INSERT INTO t1 VALUES (DATE '1969-12-19');
DELETE FROM t1;
INSERT INTO zseed VALUES (-414224324, NULL);
CHECKPOINT;
SELECT zseed.rowid, zseed.c0 FROM zseed NATURAL RIGHT JOIN t1 WHERE zseed.c0;

Expected

The same outcome from both runs. t1 is empty, so a RIGHT JOIN against it has no matching rows and the query should return 0 rows in both cases:

Run A -->  0 rows      (this is what Run B already does)
Run B -->  0 rows

If joining INTEGER to DATE is genuinely unsupported, then rejecting it consistently — ideally at bind time, in both runs — would also be acceptable. What should not happen is the outcome depending on whether a checkpoint has occurred.

Actual

Run A:  Conversion Error: Unimplemented type for cast (INTEGER -> DATE) when casting from source column c0
Run B:  (0 rows, no error)

OS:

Ubuntu 22.04.2 LTS, x86_64, kernel 5.15.0-187-generic

DuckDB Version:

current main, commit 8bc48eb3fe36f13b5e57e5e136c3ba85cc7d74d4 (also reproduces on released 1.5.5)

DuckDB Client:

CLI

Hardware:

No response

Full Name:

Ke Han

Affiliation:

Purdue University

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant data sets for reproducing the issue?

Yes