Hi,
I am trying to come to terms with the UNNEST moved from the postgress
style to Calcite style.
So assume I have a table declared like this
Create table1 (rec_num int, detail[] text)
I insert some records like this
insert into table1 values (1, ["hello", "world"])
insert into table1 values (2, ["goodbye", "cruel", "world"])
Now in the postgress style of UNNESTI can do this
select rec_num , UNNEST(detail) from table1
and expect to get something like
1 hello
1 world
2 goodbye
2 cruel
2 world
But in the calcite style I thought I could say something like
select * from UNNEST(select rec_num, detail from table1)
but it doesn't like a non array column in the UNNEST, which I can understand
so then I suspected I needed something more like
select rec_num, det from table1, UNNEST(select rec_num, detail from
table1) as t(det)
but now I am really confused because I have no column to specify from
the UNNEST to join the two FROM entities on and am afraid i would
produce a cross products?
What is the correct way to think of calcite UNNEST?
P.S. I like the 'style' of the calcite UNNEST better than postgress
style as UNNEST semantically seems more like a 'row creator' than a
'projection specification'
Thanks
Homer
