sql: COPY FROM FORMAT CSV: quotes not stripped out unless immediately following a delimiter

[philip-stoev]

What version of Materialize are you using?

From the shell:

v0.9.13-dev (0d9feb389)

What was the issue?

I do not think this is covered under our existing documented differences, and it seems a distinct issue from #9074, but a string containing quotes for the second column is ingested differently in Mz and in Postgres.

Is the issue reproducible? If so, please provide reproduction instructions.

In Mz:

create table t1 (f1 text not null, f2 text not null);
materialize=> COPY t1 FROM STDIN WITH (FORMAT CSV);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> " a "," b "
>> " a " , " b "
>> \.
COPY 2
materialize=> select f1, length(f1), f2, length(f2) from t1;
  f1  | length |   f2   | length 
------+--------+--------+--------
  a   |      3 |  b     |      3
  a   |      4 |  " b " |      6
(2 rows)

On the second row, the quotes around "b" were preserved. Contrast with Postgres:

pstoev=# COPY t1 FROM STDIN WITH (FORMAT CSV);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> " a "," b "
>> " a " , " b "
>> \.
COPY 2
pstoev=# select f1, length(f1), f2, length(f2) from t1;
  f1  | length |  f2  | length 
------+--------+------+--------
  a   |      3 |  b   |      3
  a   |      4 |   b  |      4
(2 rows)

So the two products agree on everything (spaces et al) except the quotes in the second column of the second row.

[sploiselle]

tysm again, @philip-stoev!

The feature this requires is trimming whitespace (or at least spaces) from the beginning of input. It seems as if the library we're using assumes that all characters after a delimiter are definitely part of the intended value.

Despite being valid in PG, Cockroach also fails on this input, which our CSV parser simply doesn't support (i.e. it never fails). Seems like their library also fails to support the trim-like operation PG does.