cockroachdb / cockroachdb/cockroach

QA: sql: copy with non-csv format allows invalid delimiters

Open
#100,492 0 comments 0 reactions 0 assignees View on GitHub
C-bug O-qa T-sql-queries
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.