SQL client copy does not appear to work; also throws confusing SSL auth error on insecure demo cluster

[rmloveland]

Trying to use COPY from stdin with cockroach sql on v22.2.0-beta.4. There appear to be multiple issues:

1. It does not seem to load the data from the external file, even though the command returns "Exit success". I would expect a non-zero Unix return value if the command fails to load data from the file (perhaps an odd SQL/Unix interaction here)
2. It throws a weird SSL error in an insecure context where no SSL things should be involved as far as the user is concerned

Details below.

Insecure demo cluster started as follows

$ cockroach version                                                            
Build Tag:        v22.2.0-beta.4
Build Time:       2022/10/17 14:45:40
Distribution:     CCL
Platform:         darwin amd64 (x86_64-apple-darwin19)
Go Version:       go1.19.1
C Compiler:       Clang 10.0.0 
Build Commit ID:  cc25a7893ea924897fcc6d3e80d116c85666e8eb
Build Type:       release

$ cockroach demo --insecure --no-example-database

Tried to send it some data from a CSV file as follows, and get confusing SSL error:

$ cockroach sql --insecure -e "SELECT version(); CREATE TABLE IF NOT EXISTS setecastronomy (name STRING, phrase STRING); COPY setecastronomy FROM STDIN WITH CSV DELIMITER ',' ESCAPE '\'" < /tmp/foo.txt                                                                                         <                                             version
-----------------------------------------------------------------------------------------------
  CockroachDB CCL v22.2.0-beta.4 (x86_64-apple-darwin19, built 2022/10/17 14:45:40, go1.19.1)
(1 row)
CREATE TABLE
NOTICE: relation "setecastronomy" already exists, skipping
ERROR: SSL authentication error while connecting.
-e: ERROR: COPY together with other statements in a query string is not supported (SQLSTATE 08P01)
Failed running "sql"

If I do what the COPY error says and use COPY by itself, everything appears to work as expected:

$ cockroach sql --insecure -e "COPY setecastronomy FROM STDIN WITH CSV DELIMITER ',' ESCAPE '\'" < /tmp/foo.txt                                                                                                                                                              
$ echo $?

Aside: However, the data is not in the DB even though the command above reported back return value of 0 "everything is fine". Should I file a separate bug for that?

select * from setecastronomy;
  name | phrase
-------+---------
(0 rows)

The SSL error appearing in this context seems like a bug since this is an insecure demo cluster being connected to from a SQL client that passed --insecure.

As shown above the cluster and SQL client are the same version if it matters. Apologies if missing something obvious here.

Jira issue: CRDB-21063

[rmloveland]

cc @rafiss found while working through your feedback to https://github.com/cockroachdb/docs/pull/15433

[rafiss]

Using COPY with -e is not well tested or supported. It's meant to be used in an interactive terminal:

root@localhost:26257/defaultdb> CREATE TABLE IF NOT EXISTS setecastronomy (name STRING, phrase STRING);;
CREATE TABLE

root@localhost:26257/defaultdb> COPY setecastronomy FROM STDIN WITH CSV DELIMITER ',' ESCAPE '\';
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.
>> cat,dog
>> hello,world
>> \.
COPY 2

root@localhost:26257/defaultdb> select * from setecastronomy;
  name  | phrase
--------+---------
  cat   | dog
  hello | world
(2 rows)

But the behavior you ran into is definitely confusing. We can use this ticket to make sure a better message is shown if someone uses -e with COPY.

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!