"invalid locale C: language: tag is not well-formed" when using cast with COLLATE

[gordthompson]

To Reproduce

import psycopg2

print(psycopg2.__version__)  # 2.9.5 (dt dec pq3 ext lo64)

sql = """\
SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class
WHERE pg_catalog.pg_class.relkind = 'foo'::VARCHAR(64) COLLATE "C"
"""

# okay with PostgreSQL
#
pg_cnxn = psycopg2.connect(
    "host=192.168.0.199 dbname=test user=scott password=tiger"
)
crsr = pg_cnxn.cursor()
crsr.execute("SELECT version()")
print(crsr.fetchall())
# [('PostgreSQL 12.3 (Debian 12.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit',)]
crsr.execute(sql)
print(crsr.fetchall())  # []

# error with CockroachDB
#
cr_cnxn = psycopg2.connect(
    "host=localhost port=26257 dbname=defaultdb user=root"
)
crsr = cr_cnxn.cursor()
crsr.execute("SELECT version()")
print(crsr.fetchall())
# [('CockroachDB CCL v22.2.2 (x86_64-pc-linux-gnu, built 2023/01/04 17:23:00, go1.19.1)',)]
crsr.execute(sql)
"""
Traceback (most recent call last):
  File "/home/gord/git/sqlalchemy-cockroachdb/gord_test/collate_issue.py", line 32, in <module>
    crsr.execute(sql)
psycopg2.errors.InvalidParameterValue: invalid locale C: language: tag is not well-formed
"""

Environment

• CockroachDB version: 22.2.2
• Server OS: Ubuntu 20.04
• Client app: psycopg2, psycopg(3)

Additional notes
The same error occurs from Java (DBeaver) using the PostgreSQL JDBC driver.

Jira issue: CRDB-23665

Epic CRDB-17785

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-sessions (found keywords: pg_,sqlalchemy,psycopg2,import)

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