CREATE TABLE with DATE column in sqlite saves column as VARCHAR (missing affinity?) #166
Description
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?
- Yes, I have
Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?
- Yes, I have