sql: unsupported comparison operator: <varchar[]> IN <tuple>

[sheaffej]

Describe the problem An IN comparison between a VARCHAR[] and a list of VARHCAR[]s errors on CockroachDB v24.1.4 with

j4@j4-mr-demo/defaultdb> select * from frame_in_test where status IN (array['a'::varchar(255), 'b'::varchar(255), 'c'::varchar(255)], array['b'::varchar(255)],
                      -> array['c'::varchar(255)]);
ERROR: unsupported comparison operator: <varchar[]> IN <tuple>
SQLSTATE: 22023

But succeeds on PostgreSQL 12.7

postgres=# select * from frame_in_test where status IN (array['a'::varchar(255), 'b'::varchar(255), 'c'::varchar(255)], array['b'::varchar(255)], array['c'::varchar(255)]);
 status
---------
 {a,b,c}
(1 row)

To Reproduce

CREATE TABLE frame_in_test (
    status VARCHAR(255)[] NULL
);

insert into frame_in_test values (array['a', 'b', 'c']);

Then execute the comparison on CokroachDB to see the error

j4@j4-mr-demo/defaultdb> select * from frame_in_test where status IN (array['a'::varchar(255), 'b'::varchar(255), 'c'::varchar(255)], array['b'::varchar(255)],
                      -> array['c'::varchar(255)]);
ERROR: unsupported comparison operator: <varchar[]> IN <tuple>
SQLSTATE: 22023

Environment:

j4@j4-mr-demo/defaultdb> select version();
                                                  version
-----------------------------------------------------------------------------------------------------------
  CockroachDB CCL v24.1.4 (x86_64-pc-linux-gnu, built 2024/08/26 19:15:49, go1.22.5 X:nocoverageredesign)
(1 row)

postgres=# select version();
                                                     version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.7 (Debian 12.7-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)

Additional context Two suggested SQL workarounds:

Using equality comparisons with OR instead of IN

select * from frame_in_test where status = array['a', 'b', 'c'] or status = array['b'] or status = array['c'];

or using a common table expression (CTE)

WITH inset (a) AS (VALUES (ARRAY['a', 'b', 'c']), (ARRAY['b']), (ARRAY['d'])) SELECT frame_in_test.* FROM inset JOIN frame_in_test ON status = a;

Jira issue: CRDB-42350

[blathers-crl[bot]]

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

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