xxx.cockroachlabs.cloud:26257/defaultdb> UPDATE tax_definition SET tax_definition_name = CONCAT(tax_type, ' ', country_code) WHERE tax_type = 'VAT';
ERROR: unknown signature: concat(tax_type, string, iso_3166_2_type) (returning )
SQLSTATE: 42883
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
**Expected behavior**
There should be no error while using CONCAT() with enum types.
**Additional data / screenshots**
If the problem is SQL-related, include a copy of the SQL query and the schema
of the supporting tables.
If a node in your cluster encountered a fatal error, supply the contents of the
log directories (at minimum of the affected node(s), but preferably all nodes).
Note that log files can contain confidential information. Please continue
creating this issue, but contact support@cockroachlabs.com to submit the log
files in private.
If applicable, add screenshots to help explain your problem.
**Environment:**
- CockroachDB version: 24.1.0
- Server OS: n/a
- Client app: `cockroach sql`
**Additional context**
Unable to use CONCAT() effectively
Tracking issue for the root cause: https://github.com/cockroachdb/cockroach/issues/75101
Jira issue: CRDB-39198
Describe the problem
Concat() function in UPDATE statement throws
unknown signature
while using enum types for column.To Reproduce
Time: 1.222s total (execution 1.130s / network 0.092s)
xxx.cockroachlabs.cloud:26257/defaultdb> CREATE TABLE tax_definition (
-> tax_definition_uid UUID NOT NULL,
-> tax_definition_name VARCHAR NULL, country_code iso_3166_2_type NOT NULL, local_name VARCHAR NOT NULL, abbreviation VARCHAR NOT NULL, prefix VARCHAR NULL, formats VARCHAR[] NOT NULL, -> tax_type tax_type NOT NULL, created_on TIMESTAMPTZ NOT NULL DEFAULT now():::TIMESTAMPTZ, updated_on TIMESTAMPTZ NULL, CONSTRAINT tax_definition_pk PRIMARY KEY (tax_definition_uid ASC),
-> CONSTRAINT tax_definition_country_code FOREIGN KEY (country_code) REFERENCES public.country(country_code) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE INDEX
-> tax_definition_local_name_country_code_uniq (local_name ASC, country_code ASC), INDEX tax_definition_country_code (country_code ASC) ) LOCALITY REGIONAL BY TABLE IN PRIMARY REGION;
xxx.cockroachlabs.cloud:26257/defaultdb > select * from tax_definition;
tax_definition_uid | tax_definition_name | country_code | local_name | abbreviation | prefix | formats | tax_type | created_on | updated_on ---------------------------------------+---------------------+--------------+-----------------+--------------+--------+---------------------+----------+-------------------------------+------------- d042691c-5044-4e72-944c-d8bf0970503c | VAT IE | IE | Value-Added Tax | VAT | IE | {var-01:1,var-02:2} | VAT | 2024-04-03 06:24:08.643818+00 | NULL (1 row)
Time: 95ms total (execution 3ms / network 93ms)
xxx.cockroachlabs.cloud:26257/defaultdb> UPDATE tax_definition SET tax_definition_name = CONCAT(tax_type, ' ', country_code) WHERE tax_type = 'VAT';)
SQLSTATE: 42883
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
ERROR: unknown signature: concat(tax_type, string, iso_3166_2_type) (returning