Support index acess for Row Type

[wenleix]

Today we only support access by field for Row Type. For row types without field types, we have to first cast it into a Row with field names:

presto > select CAST(v AS ROW(f1 BIGINT, f2 VARCHAR)).f1 
from (VALUES ROW(ROW(1, 'a')), ROW(ROW(2, 'b'))) AS t(v);

 _col0
-------
     1
     2
(2 rows)

It will be nice to support index access, i.e. v[1] will return the first field of the row.

[geraint0923]

Good idea! But I think we should only allow constant index, otherwise it will be hard to infer the field type.

[wenleix]

Related PR for . operator:

#3636
#2378

[wenleix]

Looking at how PostgreSQL and Oracle solve the problem. In Postgre SQL, the row constructor will construct an record, while the composite type stored in table has its own type name.

wxie=# select pg_typeof(ROW('x',1));                                                     

pg_typeof 
-----------
 record

If I understand correctly, the only way to extract specific field from an unnamed record is to cast it into a defined composite type , e.g. http://stackoverflow.com/questions/4700661/how-can-i-extract-the-values-from-a-record-as-individual-columns-in-postgresql :

CREATE TYPE my_type AS (
   f1 VARCHAR,
   f2 INTEGER
);

WITH tmp AS
(
	SELECT ROW('x', 1) x
	UNION ALL
	SELECT ROW('y', 2) x
	UNION ALL
	SELECT ROW('z', 3) x
)
SELECT (x::text::my_type).f2 FROM tmp

Would like to hear any comments/suggestions from people who are more familiar with PostgreSQL :).

[wenleix]

In Oracle, the RECORD type always have FieldName: https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/record_definition.htm#LNPLS01338, https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm#BABHHJGA.

Previous SQL engine doesn't have the same flexibility as Presto to get unnamed RowType. As such, accessing field in unnamed RowType is not a pain point for them.

We plan to add constant subscript access support for RowType in Presto

[electrum]

Index access seems strange given that it must be constant. It's really a tuple, not an array. What about generating well-known synthetic field names? For example, f1, f2, or c1, c2, etc. This makes it obvious they are constant field accesses and not array accesses.

[wenleix]

@electrum We probably want to use something that cannot be a legal field name then :). What about ."$field1", ."$field2" then? (Similar to the hidden bucket column name).

[electrum]

No, you want it to be easy to access. There's no problem with conflicts since you can't have a partially anonymous row. They're either all named or none are.

[martint]

That's not true. They type of a "relation" is multiset(row(...)). If that relation is a subquery, some fields may be unnamed (e.g., if they are the result of some complex expression and don't have an alias)

[martint]

I think we should hold off adding this feature for now. I really don't like the idea of adding synthetic fields with some arbitrary name, and the subscript operator version has enough edge cases and potential complications that we need to think about it very carefully.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.