Compare string with collating in array tuple via binary protocol

[zhixinwen]

Discussed in #132867

^{Originally posted by zhixinwen October 17, 2024}
Related question to #117849, but with binary protocol.

I have a table:

   CREATE TABLE test_collate (
                id INT8 PRIMARY KEY,
                "string_field" STRING COLLATE en_US_u_ks_level2 NULL,
            )

And this would work in console

root@localhost:26257/defaultdb> PREPARE pselect3 AS SELECT * FROM "test_collate" WHERE (("id", "string_field")) = ANY($1);
PREPARE

Time: 2ms total (execution 1ms / network 1ms)

root@localhost:26257/defaultdb> EXECUTE pselect3(Array[(1, 'str_collate_1')]);
  id | string_field
-----+----------------
   1 | str_collate_1
(1 row)

However if I send the args in binary format, I will receive:

unsupported comparison: collatedstring{en_us_u_ks_level2} to string"

I write the binary encoding for tuple array myself, and uses oid 25 for collated string. It works for all other cases except collated string. Did I miss anything?

Jira issue: CRDB-43466

[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 was unable to automatically find someone to ping.

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

[zhixinwen]

I believe this is a real issue because the query only works with prepared statement in console. The following would not work:

root@localhost:26257/defaultdb> SELECT * FROM "test_collate" WHERE  (("id","string_field" )) =  ANY(Array[(1, 'a')]);
ERROR: unsupported comparison operator: (id, "string_field") = ANY ARRAY[(1, 'a')]: tuples [(id, "string_field") (1, 'a')] are not the same type: expected 'a' to be of type collatedstring{en_us_u_ks_level2}, found type string
SQLSTATE: 22023

This is also filed in https://support.cockroachlabs.com/hc/en-us/requests/24043