bug: array of struct

[xiangjinwu]

Describe the bug

If we defined a column with type struct<..>[], it is hard to insert or select from it.

To Reproduce

CREATE TABLE t (data struct<id varchar, name varchar>[]);

INSERT INTO t VALUES (ARRAY[ROW('a', 'b'), ROW('c', 'd')]); -- binder error

select data from t;

select data[1] from t;

select (data[1]).id from t; -- parser error

select (data[1]).* from t; -- binder panic

Expected behavior

No response

Additional context

No response

[xiangjinwu]

The issue on insert will be fixed by #7202.

The panic is also mitigated by #7024 (See https://github.com/risingwavelabs/risingwave/pull/6999#discussion_r1054652000). But the .* does not expand to multiple columns as expected.

For the latter parser issues, the current workaround is CTE:

with z(first) as (select data[1] from t) select (first).id from z;
with z(first) as (select data[1] from t) select (first).* from z;

[kwannoel]

The issue on insert will be fixed by https://github.com/risingwavelabs/risingwave/pull/7202.

Seems like not fixed yet, will continue investigating along with https://github.com/risingwavelabs/risingwave/issues/7189#issuecomment-1371846545.

dev=> CREATE TABLE t (data struct<id varchar, name varchar>[]);

INSERT INTO t VALUES (ARRAY[ROW('a', 'b'), ROW('c', 'd')]);
CREATE_TABLE
ERROR:  QueryError: Bind error: cannot cast type "record[]" to "struct<id varchar,name varchar>[]" in Assign context

Edit this is already fixed, I was mistakenly testing on old branch.

[xiangjinwu]

Remaining issues:

• select (data[1]).* from t; should return 2 columns of fields rather than 1 column of struct
• select (t).data from t; / select t from t;