CSV IMPORT fails for columns with enum type and default value

[dejan-lokar]

Describe the problem

IMPORT INTO statement fails when importing CSV data into a table with an enum column type that has a default value.

To Reproduce

1. setup schema and a test row

CREATE TYPE IF NOT EXISTS teststate AS ENUM ('ENABLED', 'DISABLED');

CREATE TABLE table1
(
    id STRING NOT NULL,
    state teststate NOT NULL DEFAULT 'DISABLED' -- does not work
    -- state teststate NOT NULL -- works
);

INSERT INTO test1 (id, state) VALUES
    ('id1', 'ENABLED');

2. export table data into CSV

EXPORT INTO CSV 's3://bucket1' FROM TABLE db1.table1

3. import CSV

IMPORT INTO db1.table1 CSV DATA ('s3://bucket1/FILENAME')

Step #3 fails with the following error:
pq: s3://test-bucket/export170a08f5d51fbaf10000000000000001-n786690423440965633.0.csv?AWS_ACCESS_KEY_ID=****&AWS_ENDPOINT=****&AWS_REGION=us-east-2&AWS_SECRET_ACCESS_KEY=****: process default and computed columns: type OID 100112 does not exist

Environment:

• CockroachDB version 21.1.9

Jira issue: CRDB-18484

[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:

• @cockroachdb/sql-experience (found keywords: IMPORT)
• @cockroachdb/bulk-io (found keywords: export)

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.}

[blathers-crl]

cc @cockroachdb/bulk-io

[ZhouXing19]

This has been fixed by 5608424 from v22.1.0 and forward. The test can be found here:

cockroach/pkg/sql/importer/import_stmt_test.go

Lines 1454 to 1469 in 3f00311

	// Test CSV default and computed column imports.
	{
	create: `
	a greeting, b greeting default 'hi', c greeting
	AS (
	CASE a
	WHEN 'hello' THEN 'hi'
	WHEN 'hi' THEN 'hello'
	END
	) STORED`,
	intoCols: "a",
	typ: "CSV",
	contents: "hello\nhi\n",
	verifyQuery: "SELECT * FROM t ORDER BY a",
	expected: [][]string{{"hello", "hi", "hi"}, {"hi", "hi", "hello"}},
	},