cockroach dump INSERTs do not include collate clauses

[dpkirchner]

Describe the problem

If you are using tables with columns that use collation, the SQL generated by cockroach dump won't insert without massaging the queries.

To Reproduce

1. Install CockroachDB v20.1.0-rc2 -- single node is fine (also, I haven't tested earlier versions)
2. Create a database and table[1] with a column that uses collation, then insert some data
3. cockroachdb dump --insecure db > sql [2]
4. Drop the database and recreate it
5. cat sql | cockroachdb sql --insecure -d db, see error [3]

Expected behavior
I expected the dump sql to include the necessary collate en_u_ks_level2 clause. I think, anyway -- is there a better way?

Additional data / screenshots

[1]

create database demo;
use demo;
create table demo (greeting varchar(30) collate en_u_ks_level2);
insert into demo (greeting) values ('hello, world' collate en_u_ks_level2);

[2]

cat sql
CREATE TABLE demo (
	demo VARCHAR(30) COLLATE en_u_ks_level2 NULL,
	FAMILY "primary" (greeting, rowid)
);

INSERT INTO demo (greeting) VALUES
	('hello, world');

[3]

CREATE TABLE
Time: 11.7267ms
ERROR: value type string doesn't match type collatedvarchar{en_u_ks_level2} of column "greeting"
SQLSTATE: 42804
ERROR: value type string doesn't match type collatedvarchar{en_u_ks_level2} of column "greeting"
SQLSTATE: 42804
Failed running "sql"

Environment:

• CockroachDB version v20.1.0-rc2
• Server OS: Debianish 9 (using the official docker image)

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @florence-crl (member of the technical support engineering team)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[knz]

Thanks for the report.

cc #28948

[knz]

We'll investigate.

[knz]

cc @dt for triage

[dt]

@rohany should FmtParsable be printing datums with the coalition clause ?

[rohany]

It should, and the corresponding format function on DCollatedString looks like it does include the collate clause when formatting.

[jseldess]

Documented in cockroachdb/docs#7311.