ERROR: invalid cast: when using CAST(ROW(t.*) AS t)

[lukaseder]

Describe the problem

This works in PostgreSQL, but fails in CockroachDB:

create table t (a int, b int);
select cast(row (t.*) as t) from t;

The error is:

SQL Error [42846]: ERROR: invalid cast: tuple{tuple{int AS a, int AS b}} -> tuple{int AS a, int AS b}

Note that the functionality seems to have been made available with #77430 (?)

Environment:

• CockroachDB version: CockroachDB CCL v22.1.2 (x86_64-pc-linux-gnu, built 2022/06/22 15:54:12, go1.17.11)
• Client app: JDBC

Jira issue: CRDB-17336

[blathers-crl]

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

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

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

• @rafiss (commented on Support using table references as nested types #77430)

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

[rafiss]

I wonder if this would relate to any of the work that's in flight in #82435. do you happen to know @jordanlewis oor @mgartner ?

[jordanlewis]

I don't think it's related, no. I think it would probably be possible to figure this one out as a one-off.

Here is an observation:

[email protected]:26257/defaultdb> create table t (a int, b int);
CREATE TABLE


Time: 9ms total (execution 9ms / network 0ms)

[email protected]:26257/defaultdb> insert into t values(1,2);
INSERT 0 1

[email protected]:26257/defaultdb> select row(t.*) from t;
     row
-------------
  ("(1,2)")

[email protected]:26257/defaultdb> select pg_typeof(row(t.*)) from t;
  pg_typeof
-------------
  record
(1 row)

PG:

jordan=> select row(t.*) from t;
  row
-------
 (1,2)
(1 row)

jordan=> select pg_typeof(row(t.*)) from t;
 pg_typeof
-----------
 record
(1 row)

jordan=> select row(t) from t;
    row
-----------
 ("(1,2)")
(1 row)


jordan=> select row(t)::t from t;
ERROR:  cannot cast type record to t
LINE 1: select row(t)::t from t;
                     ^
DETAIL:  Cannot cast type t to integer in column 1.
jordan=>

I think the issue is that we incorrectly handle row(t.*) as opposed to row(t).

[e-mbrown]

The value of the expression seems to be what we want ((a,b) as a,b) up until the tuple walk function. It seems like the extra wrapping happens when the exprs are attached to the copy of the tuple expr. ((a,b) as a,b) becomes (((a,b) as a,b),).

The other thing i've noticed is that t.* is represented as t.*, which could be the issue.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!