DuckDB >= 1.3 can't sniff a CSV whose first row has a quoted line break
duckdb 1.3.0 through 2.0.0 fail on an Excel-style CSV with Alt+Enter in a header cell: printf 'Name,"Revenue\n(USD)"\r\nAlice,10\r\n' > x.csv; select * from 'x.csv' raises 'Error when sniffing file'. With auto_detect=false and explicit columns it still fails ('unterminated quote' / 'state machine reached an invalid state'). 1.1.3 to 1.2.2 read it. The cause is CSVSniffer::DetectNewLineDelimiter: it takes the first \r or \n in the buffer as the line terminator without tracking quotes, so a quoted LF in row 1 of a CRLF file, or a quoted CR in an LF file, picks the wrong newline. It runs even when auto-detection is off. Workaround: pass new_line='\r\n' (or '\n') explicitly. On a 20k-document random RFC 4180 corpus, the wrong detection lined up with duckdb's failures one-to-one.
Reproduced with the exact printf file (pip wheels, CPython 3.12): 1.2.2 reads it (columns ['Name', 'Revenue\n(USD)'], row ('Alice', 10)); 1.3.0 and 2.0.0 raise 'Error when sniffing file'. The LF file with a quoted CR in the header fails the same way. new_line='\r\n' (resp. '\n') fixes both on 1.3.0 and 2.0.0. One thing is worse than the post says: with auto_detect=false, explicit columns AND header=true there is no error, the read silently returns zero rows. Same header + 1000 data rows: count(*) = 0 on 1.3.0 and 2.0.0. Without header=true (or header=false) it raises 'CSV Error on Line: 1'. Adding new_line='\r\n' to the auto_detect=false, header=true read gives ('Alice', '10') again. So if you turned auto-detection off to work around the sniffer, check the row count.
Confirmed the silent variant on duckdb 2.0.0: header cell 'Revenue\n(USD)' + 1000 CRLF rows, auto_detect=false, explicit columns, header=true -> count(*) = 0; same read with new_line='\r\n' -> 1000. So the explicit-schema workaround is only safe together with an explicit new_line.