sql: Unexpected Error in SHOW COLUMNS

[bajinsheng]

Describe the problem

Unexpected error.

To Reproduce

CREATE TABLE t0 (c0 STRING[] DEFAULT (ARRAY[(('L+') COLLATE cmn)]));
SHOW COLUMNS FROM t0; -- ERROR: incompatible type annotation for ARRAY['L+':::STRING COLLATE cmn] as string[], found type: collatedstring{cmn}[] SQLSTATE: 42804

Expected behavior
No error.

Environment:

• CockroachDB version [6374bd8]
• Server OS: [Ubuntu 20.04]
• Client app [cockroach sql]

Additional context

Jira issue: CRDB-18225

[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 was unable to automatically find someone to ping.

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

[yuzefovich]

This errors on 22.1.5:

[email protected]:26257/defaultdb> CREATE TABLE t0 (c0 STRING[] DEFAULT (ARRAY[(('L+') COLLATE cmn)]));                                            
ERROR: expected DEFAULT expression to have type string[], but '(ARRAY['L+' COLLATE cmn])' has type collatedstring{cmn}[]

so adding a release blocker label.

[blathers-crl]

Hi @yuzefovich, please add branch-* labels to identify which branch(es) this release-blocker affects.

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

[yuzefovich]

I did some archeology, and here is what I got:

• On 22.1 and prior releases we didn't allow DEFAULT expressions differ from the column type (sql: allow DEFAULT/ON UPDATE types that can be assignment-cast to column type #74854), so on 22.1 and all prior releases CREATE TABLE statement fails. This was fixed in sql: Allow DEFAULT/ON UPDATE types that can be assignment-cast #81071, and I didn't find anything wrong with that change, and on 22.2 the table creation succeeds.
• However, now we run into a problem when type checking expressions when trying to read or modify that table because the type annotation of AnnotateTypeExpr doesn't pass the type check filter here:
  

  cockroach/pkg/sql/sem/tree/type_check.go

  Line 1968 in 0a71722

  if typ := typedExpr.ResolvedType(); !(typ.Family() == types.UnknownFamily || typ.Equivalent(required)) {

We have the DEFAULT value that is an array of collated strings whereas the required type (the type of the column in the table) is an array of strings, so two types are not Equivalent. We did find an assignment cast for them though because the array contents have the same Oid.

I'm not quite sure what to do here, but maybe we need another check in typeCheckAndRequire that is different from types.T.Equvalent check and is more assignment-cast-like permissive. cc @mgartner in case you have some ideas

This seems like a SQL Experience issue, so I'm putting it there to triage for further, but my guess is that it is an edge case, so it'll go on the backlog.

[github-actions]

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!