sql: support string literal -> tuple[] parsing

[nvanbenschoten]

Needed for #69010.

In Postgres:

> SELECT COALESCE(ARRAY[ROW(1, 2)], '{}');
 coalesce
-----------
 {"(1,2)"}

In CockroachDB:

> SELECT COALESCE(ARRAY[ROW(1, 2)], '{}');
ERROR: incompatible COALESCE expressions: expected '{}' to be of type tuple{int, int}[], found type string

Prototype:

diff --git a/pkg/sql/sem/tree/constant.go b/pkg/sql/sem/tree/constant.go
index ea3efa19e5..a982c27887 100644
--- a/pkg/sql/sem/tree/constant.go
+++ b/pkg/sql/sem/tree/constant.go
@@ -493,6 +493,7 @@ var (
                types.AnyEnum,
                types.INetArray,
                types.VarBitArray,
+               types.AnyTupleArray,
        }
        // StrValAvailBytes is the set of types convertible to byte array.
        StrValAvailBytes = []*types.T{types.Bytes, types.Uuid, types.String, types.AnyEnum}

Epic CRDB-10300

[rafiss]

related: #63995

(i just rescoped that issue to be about improving the error message, but maybe we actually should support it after all)

[rafiss]

interestingly, it seems like Postgres only allows this if the string literal is an empty array {}

Postgres:

rafiss@127:postgres> SELECT COALESCE(ARRAY[ROW(1, 2)], '{"(3,4)"}');
input of anonymous composite types is not implemented
LINE 1: SELECT COALESCE(ARRAY[ROW(1, 2)], '{"(3,4)"}')

[nvanbenschoten]

I think it does support parsing string literals as named composite types:

nathan=# CREATE TYPE complex AS (real INT, imag INT);
CREATE TYPE

nathan=# SELECT pg_typeof(COALESCE(ARRAY[ROW(1, 2)], '{"(3,4)"}'));
ERROR:  input of anonymous composite types is not implemented
LINE 1: SELECT pg_typeof(COALESCE(ARRAY[ROW(1, 2)], '{"(3,4)"}'));
                                                    ^

nathan=# SELECT COALESCE(ARRAY[ROW(1, 2)::complex], '{"(3,4)"}');
 coalesce
-----------
 {"(1,2)"}
(1 row)

nathan=# SELECT pg_typeof(COALESCE(ARRAY[ROW(1, 2)::complex], '{"(3,4)"}'));
 pg_typeof
-----------
 complex[]
(1 row)