CREATE TABLE with DATE column in sqlite saves column as VARCHAR (missing affinity?)

[datbth]

What happens?

Cannot CREATE TABLE on sqlite with DATE column (it is saved as VARCHAR instead).

But when ALTER TABLE t ADD COLUMN c DATE still saves the new column as DATE.

To Reproduce

Using DuckDB (CLI) v1.3.2 (Ossivalis) 0b83e5d2f6

CREATE TABLE db.a (a INT, b DATE);
DESCRIBE db.a;

┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ a           │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │
│ b           │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

ALTER TABLE db.a ADD COLUMN c DATE;
DESCRIBE db.a;

┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ a           │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │
│ b           │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ c           │ DATE        │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

OS:

Ubuntu 24.04.2 x86_64

DuckDB Version:

v1.3.2 (Ossivalis) 0b83e5d2f6

DuckDB Client:

CLI

Hardware:

No response

Full Name:

Dat Bui

Affiliation:

Holistics Software (holistics.io)

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have not tested with any build

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

Yes

Did you include all code required to reproduce the issue?

• [x] Yes, I have

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

• [x] Yes, I have

[datbth]

Could it be related to this limitation?

D describe db.a;
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ x           │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │
│ y           │ DATE        │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘
D insert into db.a values(1, '2025-01-01');
INTERNAL Error:
Unsupported type "DATE" for SQLite::BindValue

Stack Trace:

/home/datbth/.duckdb/extensions/v1.3.2/linux_amd64/sqlite_scanner.duckdb_extension(+0x9f3986) [0x7b0ea0df3986]
/home/datbth/.duckdb/extensions/v1.3.2/linux_amd64/sqlite_scanner.duckdb_extension(+0x9f3a34) [0x7b0ea0df3a34]
/home/datbth/.duckdb/extensions/v1.3.2/linux_amd64/sqlite_scanner.duckdb_extension(+0x9f4fb1) [0x7b0ea0df4fb1]
/home/datbth/.duckdb/extensions/v1.3.2/linux_amd64/sqlite_scanner.duckdb_extension(_ZN6duckdb17InternalExceptionC1IJNS_11LogicalTypeEEEERKNSt7__cxx1112basic_stringIcSt11char_traitsIcESaIcEEEDpT_+0x15a) [0x7b0ea095b90a]
/home/datbth/.duckdb/extensions/v1.3.2/linux_amd64/sqlite_scanner.duckdb_extension(+0x1a60ec) [0x7b0ea05a60ec]
/home/datbth/.duckdb/extensions/v1.3.2/linux_amd64/sqlite_scanner.duckdb_extension(_ZNK6duckdb12SQLiteInsert4SinkERNS_16ExecutionContextERNS_9DataChunkERNS_17OperatorSinkInputE+0x6e) [0x7b0ea093fbce]
/home/datbth/tools/duckdb-1.3.2/duckdb() [0xbe8811]
/home/datbth/tools/duckdb-1.3.2/duckdb() [0xbeda6e]
/home/datbth/tools/duckdb-1.3.2/duckdb() [0xbeddba]
/home/datbth/tools/duckdb-1.3.2/duckdb() [0xbe4971]
/home/datbth/tools/duckdb-1.3.2/duckdb() [0xbe7c04]
/home/datbth/tools/duckdb-1.3.2/duckdb() [0xba4180]
/home/datbth/tools/duckdb-1.3.2/duckdb() [0xba43bc]
/home/datbth/tools/duckdb-1.3.2/duckdb() [0xba4554]
/home/datbth/tools/duckdb-1.3.2/duckdb() [0x730ad5]
/home/datbth/tools/duckdb-1.3.2/duckdb() [0x715fa9]
/home/datbth/tools/duckdb-1.3.2/duckdb() [0x716567]
/home/datbth/tools/duckdb-1.3.2/duckdb() [0x71708a]
/home/datbth/tools/duckdb-1.3.2/duckdb() [0x7176e1]
/home/datbth/tools/duckdb-1.3.2/duckdb() [0x708dd4]
/lib/x86_64-linux-gnu/libc.so.6(+0x2a1ca) [0x7b0eaae2a1ca]
/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0x8b) [0x7b0eaae2a28b]
/home/datbth/tools/duckdb-1.3.2/duckdb() [0x70cd2e]

This error signals an assertion failure within DuckDB. This usually occurs due to unexpected conditions or errors in the program's logic.
For more information, see https://duckdb.org/docs/stable/dev/internal_errors

But I'm not seeing any info in the doc https://duckdb.org/docs/stable/core_extensions/sqlite.html