Concat() function in UPDATE statement throws unknown signature error while using enum types.

[mohan-crdb]

Describe the problem

Concat() function in UPDATE statement throws unknown signature while using enum types for column.

To Reproduce

• Create types such as tax_type as enum:

xxx.cockroachlabs.cloud:26257/defaultdb> create type tax_type as enum ('VAT', 'UseTax', 'RegionalVAT', 'SalesTax', 'Other');                                                                                                           
CREATE TYPE

• And another type iso_3166_2_type:

xxx.cockroachlabs.cloud:26257/defaultdb> create type iso_3166_2_type as enum ('AF', 'AX', 'AL', 'DZ', 'AS', 'AD', 'AO', 'AI', 'AQ', 'AG', 'AR', 'AM', 'AW', 'AU', 'AT', 'AZ', 'BS', 'BH', 'BD', 'BB', 'BY', 'BE', 'BZ', 'BJ', 'BM',    
                                                                           -> 'BT', 'BV', 'BO', 'BQ', 'BA', 'BW', 'BR', 'IO', 'BN', 'BG', 'BF', 'BI', 'CV', 'KH', 'CM', 'CA', 'KY', 'CF', 'TD', 'CL', 'CN', 'CX', 'CC', 'CO', 'KM', 'CG', 'CD', 'CK', 'CR', 'CI', 'HR',     
                                                                           -> 'CU', 'CW', 'CY', 'CZ', 'DK', 'DG', 'DJ', 'DM', 'DO', 'EC', 'EG', 'SV', 'GQ', 'ER', 'EE', 'ET', 'FK', 'FO', 'FJ', 'FI', 'FR', 'GF', 'PF', 'TF', 'GA', 'GM', 'GE', 'DE', 'GH', 'GI', 'GR',     
                                                                           -> 'GL', 'GD', 'GP', 'GU', 'GT', 'GG', 'GN', 'GW', 'GY', 'HT', 'HM', 'VA', 'HN', 'HK', 'HU', 'IS', 'IN', 'ID', 'IR', 'IQ', 'IE', 'IM', 'IL', 'IT', 'JM', 'JP', 'JE', 'JO', 'KZ', 'KE', 'KI',     
                                                                           -> 'KP', 'KR', 'KW', 'KG', 'LA', 'LV', 'LB', 'LS', 'LR', 'LY', 'LI', 'LT', 'LU', 'MO', 'MK', 'MG', 'MW', 'MY', 'MV', 'ML', 'MT', 'MH', 'MQ', 'MR', 'MU', 'YT', 'MX', 'FM', 'MD', 'MC', 'MN',     
                                                                           -> 'ME', 'MS', 'MA', 'MZ', 'MM', 'NA', 'NR', 'NP', 'NL', 'NC', 'NZ', 'NI', 'NE', 'NG', 'NU', 'NF', 'MP', 'NO', 'OM', 'PK', 'PW', 'PS', 'PA', 'PG', 'PY', 'PE', 'PH', 'PN', 'PL', 'PT', 'PR',     
                                                                           -> 'QA', 'RE', 'RO', 'RU', 'RW', 'BL', 'SH', 'KN', 'LC', 'MF', 'PM', 'VC', 'WS', 'SM', 'ST', 'SA', 'SN', 'RS', 'SC', 'SL', 'SG', 'SX', 'SK', 'SI', 'SB', 'SO', 'ZA', 'GS', 'SS', 'ES', 'LK',     
                                                                           -> 'SD', 'SR', 'SJ', 'SZ', 'SE', 'CH', 'SY', 'TW', 'TJ', 'TZ', 'TH', 'TL', 'TG', 'TK', 'TO', 'TT', 'TN', 'TR', 'TM', 'TC', 'TV', 'UG', 'UA', 'AE', 'GB', 'US', 'UM', 'UY', 'UZ', 'VU', 'VE',     
                                                                           -> 'VN', 'VG', 'VI', 'WF', 'EH', 'YE', 'ZM', 'ZW', 'ZZ');                                                                                                                                        
CREATE TYPE

Time: 1.222s total (execution 1.130s / network 0.092s)

• And then create table with tax_type as tax_type and country_code as iso_3166_2_type :

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;                  

• The UPDATE Statement breaks stating unknown signature:

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';                                                                                   
ERROR: unknown signature: concat(tax_type, string, iso_3166_2_type) (returning <varchar>)
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: #75101

Jira issue: CRDB-39198

[blathers-crl]

Hi @mohan-crdb, please add branch-* labels to identify which branch(es) this C-bug affects.

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