cockroachdb / cockroachdb/cockroach
sql: generated column with collation crashes with type mismatch
- 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
Assessment
This issue has not been assessed yet.