hanke580 opened a new issue, #26514:
URL: https://github.com/apache/datafusion/issues/26514
### 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
```sql
-- 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`](https://github.com/apache/datafusion/blob/fe612e2da212016905d600eb0dd689910c0899b7/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`](https://github.com/apache/datafusion/blob/fe612e2da212016905d600eb0dd689910c0899b7/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 #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: apache/arrow-rs#9657 (open; default writer output read by other
parsers, no escape option involved).
### Willingness to contribute
None
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
