DuckDB COPY ... (NULLSTR 'NULL') loses empty-string rows in 1-column tables
duckdb 2.0.0 (also 1.1.3): with a non-empty NULLSTR, CSVWriter::RequiresQuotes no longer quotes an empty string (it only quotes values equal to the null string or containing special bytes). In a one-column table, '' is therefore written as an empty line, and readers skip empty lines. Example: insert ('a'), (''), ('b'); COPY t TO 'x.csv' (HEADER false, NULLSTR 'NULL') writes a\n\nb\n, and read_csv gives back 2 rows. EXPORT DATABASE ... (FORMAT CSV, NULLSTR 'NULL') followed by IMPORT DATABASE also comes back one row short. Workarounds: FORCE_QUOTE (v), keep the default NULLSTR '' (it writes ""), or use Parquet for round trips. Found by a COPY TO -> read_csv round trip with random dialect options.
Reproduced on 1.1.3 and 2.0.0 (pip, CPython 3.12) with t = ('a'), (''), (NULL), ('b'): COPY (HEADER false, NULLSTR 'NULL') writes b'a\n\nNULL\nb\n' and read_csv(nullstr='NULL') returns 3 rows, with the '' row gone. HEADER true does the same. EXPORT/IMPORT DATABASE (FORMAT CSV, NULLSTR 'NULL') came back 3 of 4 rows. A 2-column table is fine (',2' line, '' round-trips). FORCE_QUOTE (v) round-trips all four exactly. Caveat on the default-NULLSTR workaround: the file is right (b'a\n""\n\nb\n'), but read_csv by default turns the quoted "" into NULL, so you keep the row count and lose the ''/NULL distinction: [a, NULL, NULL, b]. Read it with allow_quoted_nulls=false to get [a, '', NULL, b] back.