cockroachdb / cockroachdb/cockroach
QA: sql: copy with non-csv format allows invalid delimiters
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Describe the problem**
Postgres places some limitations on what characters can be used with the `DELIMITER` option for `COPY TO STDOUT` - for example, no backslash character, no lower-case letters or digits. This restriction is checked [here](https://github.com/postgres/postgres/blob/de4d456b406bf502341ef526710d3f764b41e2c8/src/backend/commands/copy.c#L643-L652) in the postgres source. Currently, CRDB does not make the same check for `COPY TO STDOUT`.
Similar restrictions apply to `COPY FROM STDIN`, and CRDB does not correctly error for these cases either,
**To Reproduce**
The following example should give the error: `ERROR: COPY delimiter cannot be "\"`, but instead produces a result using the backslash character as a delimiter.
```
CREATE TABLE country (abbr TEXT, name TEXT);
INSERT INTO country VALUES
('AF', 'AFGHANISTAN'),
('AL', 'ALBANIA'),
('DZ', 'ALGERIA'),
('ZM', 'ZAMBIA'),
('ZW', 'ZIMBABWE');
COPY country TO STDOUT (DELIMITER '\');
```
Here's a similar case for `COPY FROM STDIN`:
```
CREATE TABLE country_copy(abbr TEXT, name TEXT);
COPY country_copy FROM STDIN (DELIMITER '\');
AF\AFGHANISTAN
AL\ALBANIA
DZ\ALGERIA
ZM\ZAMBIA
ZW\ZIMBABWE
```
Using `a` or `1` as a delimiter also produces the same incorrect behavior.
Jira issue: CRDB-26464
Contributor guide
Assessment
This issue has not been assessed yet.