CSV ESCAPE option is used when reading but ignored when writing: INSERT/COPY files cannot be read back with the same options (rows merged, backslashes dropped)

Describe the bug

format.escape is accepted by CREATE EXTERNAL TABLE ... STORED AS CSV and COPY ... STORED AS CSV, and the
documentation lists it among the options "available when reading or writing CSV files". The reader honours it, but the
writer does not: values are written with the default quote-doubling rules and backslashes are left as they are. Reading
the file back with the same options then treats every \ inside a quoted field as an escape:

\X is read as X, so backslashes disappear from quoted values;
a quoted value that ends in \ turns its closing quote into a literal quote, so the field continues over the following
lines and all later rows of the file are merged into that one value.

There is no error. For a CSV listing table this means INSERT INTO reports all rows written, and SELECT on the same
table returns fewer rows with changed text.

To Reproduce

-- repro.sql, run with: datafusion-cli -q -f repro.sql
CREATE EXTERNAL TABLE files (id INT, path VARCHAR) STORED AS CSV LOCATION 'tdir/'
  OPTIONS ('format.escape' '\', 'format.has_header' 'true');
INSERT INTO files VALUES (1, 'C:\Users\ann, smith\'), (2, 'D:\backup'), (3, 'E:\logs');
SELECT * FROM files;

COPY (SELECT 1 AS id, 'a\b, c' AS s) TO 'copy.csv' STORED AS CSV OPTIONS ('format.escape' '\');
CREATE EXTERNAL TABLE back (id INT, s VARCHAR) STORED AS CSV LOCATION 'copy.csv' OPTIONS ('format.escape' '\');
SELECT * FROM back;

Output:

+-------+
| count |
+-------+
| 3     |
+-------+
+----+--------------------+
| id | path               |
+----+--------------------+
| 1  | C:Usersann, smith" |
|    | 2,D:backup         |
|    | 3,E:logs           |
|    |                    |
+----+--------------------+
+-------+
| count |
+-------+
| 1     |
+-------+
+----+-------+
| id | s     |
+----+-------+
| 1  | ab, c |
+----+-------+

The files that were written:

$ cat tdir/*.csv
id,path
1,"C:\Users\ann, smith\"
2,D:\backup
3,E:\logs
$ cat copy.csv
id,s
1,"a\b, c"

Three rows were inserted, one comes back, holding the rest of the file; a\b, c comes back as ab, c. Reading
tdir/ without the escape option returns the three rows correctly, so the files are plain RFC 4180 CSV written as if
no escape character had been set.

Setting 'format.double_quote' 'false' as well does not help: the writer then escapes quotes as \" but still writes
\ unescaped (1,"C:\Users\ann, smith\"), and the table again returns 2 of 3 rows.

Expected behavior

A file written with a set of CSV options can be read back with the same options and returns the same rows. With
ESCAPE '\' the writer should escape the escape character itself and the quote character with it (e.g.
1,"C:\\Users\\ann, smith\\", which the reader already decodes correctly). If that is not supported, ESCAPE should
be rejected for writing instead of being accepted and ignored.

Additional context

DataFusion main fe612e2 (2026-10-18), datafusion-cli 55.1.0 built from source; arrow-csv 60.0.0.
Cause: CsvWriterOptions::try_from(&CsvOptions)
(datafusion/common/src/file_options/csv_writer.rs#L91-L96)
passes escape to arrow-csv's WriterBuilder::with_escape and leaves double_quote at its default true.
arrow-csv documents that the escape character "is only used when double_quote is disabled", so it has no effect.
The reader built from the same CsvOptions
(datafusion/datasource-csv/src/source.rs#L205-L207)
calls ReaderBuilder::with_escape, which is active inside quoted fields. With double_quote = false the underlying
csv crate writer escapes quotes but not the escape byte (BurntSushi/rust-csv PR Simplified creation of array from scalar. #432, open), so DataFusion cannot fix
it by only flipping double_quote; the writer needs to escape the escape byte (in arrow-csv / csv, or by DataFusion
rejecting escape on write until then).
format.comment has the same asymmetry: COPY (SELECT '#tag' AS s, 4 AS id UNION ALL SELECT 'two', 2) TO 'cm.csv' STORED AS CSV OPTIONS ('format.comment' '#', 'format.has_header' 'false') writes #tag,4 unquoted at the start of a
line, and a table over that file with the same option skips it as a comment. The writer options never receive the
comment character, so it cannot quote such values.
Backslashes are common in Windows paths, regular expressions and JSON snippets, and ESCAPE '\' is the usual setting for
CSV exchanged with MySQL/PostgreSQL-style tools.
Related: arrow-csv does not safely escape forward slashes arrow-rs#9657 (open; default writer output read by other parsers, no escape option involved).

Willingness to contribute

None

I looked into this. The reader and writer disagree because escape is conditional on the writer side only.

Read path — datafusion/datasource-csv/src/file_format.rs:579 applies the escape unconditionally:

if let Some(escape) = self.options.escape {
    format = format.with_escape(escape);
}

Write path — datafusion/common/src/file_options/csv_writer.rs:91 passes it to arrow's WriterBuilder:

if let Some(v) = &value.escape {
    builder = builder.with_escape(*v)
}

but arrow-csv documents with_escape as "only used when double_quote is disabled", and CsvOptions::double_quote defaults to None (common/src/config.rs:3982), so DataFusion never calls with_double_quote and arrow's default of true wins. The escape is accepted, plumbed through, and then silently inert — which is why the file is written with quote-doubling and read back with backslash escaping.

So format.escape on its own is only half-applied. Adding 'format.double_quote' 'false' alongside it makes the repro round-trip correctly, which confirms the mechanism.

Two ways to fix, and I'd rather check which you prefer before opening a PR:

Make the write path honour escape — when escape is set and double_quote is unset, default double_quote to false for writing. This makes the documented "available when reading or writing" promise true and fixes the round-trip. It does change the bytes written for anyone setting escape today, though those writes are already unreadable with the same options.
Validate instead — reject escape without double_quote = false on write, so the failure is loud rather than silent data loss.

I'd lean towards (1) with a round-trip sqllogictest, since the current behaviour drops rows with no error. Happy to implement either — just let me know which you'd prefer.

take

I tried the first option I proposed above — defaulting double_quote to false on the write path when escape is set — and it does not fix the round trip. Posting the evidence before anyone spends time on that approach.

With 'format.escape' '\' and double_quote defaulted to false, COPY writes:

column1
"a\b, c"
"d\e"
plain

The quotes are there, but the backslashes are written raw. arrow-csv uses the escape character for the quote character only; it never escapes the escape character itself. Reading that file back with the same format.escape therefore still consumes them:

value written	read back, before	read back, with double_quote=false
`a\b, c`	`ab, c`	`ab, c`
`d\e`	`d\e`	`de`

It is actually worse than main: d\e has no delimiter in it so today it is written unquoted and survives, but with double_quote=false the writer quotes it and the backslash is then eaten on read.

So this cannot be fixed by choosing different writer options from DataFusion. For escape-style CSV to round trip, the writer has to escape the escape character as well, which is arrow-csv behaviour rather than ours.

That leaves the second option: treat format.escape on the write path as unsupported and say so, rather than accepting it and silently producing a file that cannot be read back with the same options. Something like rejecting escape for writes (optionally unless double_quote is explicitly false, which is the existing tested combination), so the failure is loud.

Worth noting the existing coverage passes only by luck of its data: csv_files.slt tests double_quote=false + escape='#' over values containing quotes, and quotes are escaped. Values containing a literal # would fail the same way.

Happy to open a PR for the validation route, or for an arrow-rs issue on the writer, whichever you prefer.

Navigation Menu

Repository navigation

CSV `ESCAPE` option is used when reading but ignored when writing: `INSERT`/`COPY` files cannot be read back with the same options (rows merged, backslashes dropped) #26514

Describe the bug

To Reproduce

Expected behavior

Additional context

Willingness to contribute

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

CSV ESCAPE option is used when reading but ignored when writing: INSERT/COPY files cannot be read back with the same options (rows merged, backslashes dropped) #26514

Description

Describe the bug

To Reproduce

Expected behavior

Additional context

Willingness to contribute

Activity

raghavyadav01 commented on Oct 20, 2026

raghavyadav01 commented on Oct 22, 2026

raghavyadav01 commented on Oct 22, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

CSV `ESCAPE` option is used when reading but ignored when writing: `INSERT`/`COPY` files cannot be read back with the same options (rows merged, backslashes dropped) #26514