cockroachdb / cockroachdb/cockroach

sql: generated column with collation crashes with type mismatch

Open
#111,091 1 comment 0 reactions 0 assignees View on GitHub
C-bug O-community T-sql-foundations X-blathers-triaged
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Describe the problem**

Creating a generated column that uses a collation when the function yields a string doesn't work.

**To Reproduce**

```sql
CREATE TABLE "model_fields_generatedmodeloutputfield" (
"id" integer NOT NULL PRIMARY KEY DEFAULT unique_rowid(),
"name" varchar(10) NOT NULL,
"lower_name" varchar(11) COLLATE "sv" GENERATED ALWAYS AS (LOWER("name")) STORED
)
```
ERROR: expected STORED COMPUTED COLUMN expression to have type collatedvarchar{sv}, but 'lower(name)' has type string

**Expected behavior**

The same query works on PostgreSQL (which uses a slightly different collation name):

```sql
CREATE TABLE "model_fields_generatedmodeloutputfield" (
"id" integer NOT NULL PRIMARY KEY,
"name" varchar(10) NOT NULL,
"lower_name" varchar(11) COLLATE "sv-x-icu" GENERATED ALWAYS AS (LOWER("name")) STORED
)
```

**Environment:**
- CockroachDB version 23.1.10 and v23.2.0-alpha.00000000-dev-fe0244d59a82211ef3e2d98420d579f7dd28a886 (2023/09/21)

**Additional context**

Django 5.0 [adds support for `GeneratedField`](https://github.com/django/django/commit/f333e3513e8bdf5ffeb6eeb63021c230082e6f95). This crash is from Django's test suite.

Jira issue: CRDB-31757

Epic CRDB-60815

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.