Bug: CSV export cannot be read back by the CSV importer, and loses empty strings

[alvorithm]

Two independent defects in the same CSV round trip. Both verified against com.ladybugdb:lbug 0.19.0 and against the lbug shell reporting Lbug 0.19.0; source cites are main @ c934f673b.

I don't need this anymore (Parquet does round-trip) but I think it's important enough to report.

1. Nested types export in display form, which the reader cannot parse

CREATE NODE TABLE T(id STRING, m MAP(STRING, STRING), PRIMARY KEY(id));
CREATE (:T {id: 'r1', m: map(['k,1','k"2'], ['v,1','v"2'])});
COPY (MATCH (n:T) RETURN n.id, n.m) TO '/tmp/t.csv' (HEADER=true);

CREATE NODE TABLE T2(id STRING, m MAP(STRING, STRING), PRIMARY KEY(id));
COPY T2 FROM '/tmp/t.csv' (HEADER=true);

Copy exception: Error in file /tmp/t.csv on line 2: Conversion exception:
Cast failed. {k,1=v,1, k"2=v"2} is not in MAP(STRING, STRING) range.

The file contains "{k,1=v,1, k""2=v""2}". The outer CSV quoting is correct; the contents are the human-readable form. MAP, STRUCT and LIST all render this way and none escapes the element delimiter.

Per type on 0.19.0, one column and one row, exported and then imported into an identical table:

value	on disk	read back
STRING 'has,comma'	"has,comma"	exact
STRING[] ['a','b']	"[a,b]"	exact
STRING[] ['a,comma','b']	"[a,comma,b]"	silently 3 elements
STRING[] ['a]b','c']	"[a]b,c]"	Copy exception
MAP map(['k'],['v,1'])	"{k=v,1}"	Copy exception
STRUCT {a: 7, b: 'x,y'}	"{a: 7, b: x,y}"	Copy exception

The LIST row is the one that worries me. size(n.v) is 2 before the round trip and 3 after, with no error and no warning: the display form of ['a,comma','b'] and of ['a','comma','b'] is the same string, and the reader takes the second reading. MAP and STRUCT at least fail loudly.

export_csv_function.cpp binds a cast to STRING per column (src/function/export/export_csv_function.cpp:165) and writeRows (:199) writes the result. That cast produces the display form ({k=v, …}, {a: 7, b: x}, [a,b]) rather than a literal the CSV reader's own value parser accepts.

PARALLEL=FALSE does not help. It addresses the separate quoted-newline limitation, which is still enforced on 0.19.0 (Quoted newlines are not supported in parallel CSV reader) and still fixed by that option.

Consequence: EXPORT DATABASE to CSV is not round-trippable for any nested column whose values can contain a delimiter: loudly for MAP and STRUCT, silently for LIST.

2. An empty string comes back as NULL

CREATE NODE TABLE E(id STRING, s STRING, PRIMARY KEY(id));
CREATE (:E {id:'empty', s:''});
CREATE (:E {id:'null'});                       -- s absent
CREATE (:E {id:'plain', s:'x'});
COPY (MATCH (n:E) RETURN n.id, n.s ORDER BY n.id) TO '/tmp/e.csv' (HEADER=true);

CREATE NODE TABLE E1(id STRING, s STRING, PRIMARY KEY(id));
COPY E1 FROM '/tmp/e.csv' (HEADER=true);
MATCH (n:E1) RETURN n.id, n.s IS NULL ORDER BY n.id;

id	source IS NULL	on disk	after import
empty	false	empty,""	true
null	true	null,	true
plain	false	plain,x	false

The file is not ambiguous. The writer already emits "" for the empty string and a bare empty field for NULL. The distinction is discarded on the way in: ParsingDriver::addValue receives the field already unquoted (src/processor/operator/persistent/reader/csv/driver.cpp:22), and setVectorNull matches that text against option->nullStrings (src/function/cast_from_string_functions.cpp:861), whose default is {""} (CopyConstants::DEFAULT_CSV_NULL_STRINGS, src/include/common/constants.h). The two collapse at the reader, not in the format.

NULL_STRINGS does not rescue it; it only flips which way they collapse. With NULL_STRINGS=['\N'] the same file reads back with IS NULL false for both rows, so the genuine NULL is now an empty string.

Parquet keeps IS NULL false for the empty string and true for the absent one.

This one bit me and was caught almost by chance: I had a legitimate path = "", and the CSV loader was storing NULL for it. It surfaced only from a graph-to-graph diff. A test asserting "empty" passes when the value reads back as NULL through most comparisons, so I was lucky to be diffing graphs.

Fixes?

I could think of these:

1. Emit a re-parsable literal for nested types on export, escaping delimiters and quotes within elements, and teach the reader the same escaping; or
2. teach the reader the display form it already produces; or
3. document that CSV export is lossy for nested types and point users at Parquet for round-tripping.

Defect 2 is separable from all three, and looks cheaper: the bytes already carry the distinction, so it is a matter of the reader knowing whether the field it is about to null-test was quoted. That is the convention most CSV dialects use.

Even (3) alone would be worth having. Round-tripping its own export is the property a user reasonably assumes of EXPORT DATABASE, and for LIST and for the empty string the failure is silent.

Reproduction

Everything above is plain Cypher: no bindings, no extensions, no parameters. Both blocks run unmodified in the shell (lbug :memory:), which is how the two tables were produced; the same statements through the JVM binding give identical results. Happy to turn them into a test/test_files/copy/*.test case alongside export_import_db.test if that is more useful than the description.

[alvorithm]

This work was produced with the help of language models.
Reporter here. The second defect, the empty string that comes back as NULL, has a pull request: #808. The writer already distinguishes the two cases, quoting an empty string and leaving a NULL field bare, and the reader loses the distinction because it unquotes the field before it compares the text against nullStrings. The parse loop tracks whether the field carried quotes for its own trimming, so the patch threads that flag as far as the null test and exempts a quoted field from it. Existing files change meaning under it: a "" in a STRING column that used to read as NULL now reads as the empty string. Say the word if you would rather have that behind a copy option than as the default.

The first defect, nested types exporting in display form, I have not patched. Making LIST, MAP and STRUCT round-trip through CSV means choosing a re-parsable text form and teaching the reader to accept it, and that is a format decision that belongs to you rather than to a patch offered unasked. If you name the form you want, the reader side is a small piece of work and I am happy to write it.

I no longer need either fix, because Parquet round-trips. The reason I would still keep the first one open is that its failure is silent for LIST: the export writes the display form, the import accepts it, and the rows differ.