IN predicate nested tuple type support

[msirek]

Is your feature request related to a problem? Please describe. Postgres supports IN subqueries such as SELECT * FROM ab WHERE ROW(ROW(a, b)) IN (SELECT (x, y) FROM xy); while this fails in CRDB during type checking. Similar query SELECT * FROM ab WHERE (a,b) IN (SELECT (x, y) FROM xy); works in CRDB, but fails in Postgres with: ERROR: subquery has too few columns.

Describe the solution you'd like Add support for any level of nested tuples in IN subqueries, eg.

CREATE TABLE xy (x INT, y INT);
CREATE TABLE ab (a INT, b INT);

SELECT * FROM ab WHERE ROW(ROW(a, b)) IN (SELECT (x, y) FROM xy);
SELECT * FROM ab WHERE ROW(ROW(ROW(a, b))) IN (SELECT ROW(ROW(x, y)) FROM xy);
SELECT * FROM ab WHERE ROW(ROW(ROW(ROW(a, b)))) IN (SELECT ROW(ROW(ROW(x, y))) FROM xy);
etc...

Describe alternatives you've considered Also, consider whether we want to match Postgres behavior for other subquery forms. For example, should we error out the following as Postgres does: SELECT * FROM ab WHERE (a,b) IN (SELECT (x, y) FROM xy);

Additional context This issue is related to https://github.com/cockroachdb/cockroach/pull/101975

Jira issue: CRDB-27220

[msirek]

Another test case:

create type p as (x int, y int);
create table t11 (a int, b p);

-- This should not fail
explain select * from t11 where b in (SELECT b from t11);
ERROR: subquery has too few columns
SQLSTATE: 42601

[DrewKimball]

See also https://github.com/cockroachdb/cockroach/pull/97948#issuecomment-1812534472.