SQL has two kinds of values: relations and scalars. Relations can only appear in a FROM clause, scalars can appear anywhere else. Arrays and multisets represent collections but are scalars, so can't appear in a FROM clause.
UNNEST's purpose is to do magic: convert a collection scalar into a relation. So, the argument to unnest is a scalar expression. Try this *: SELECT t.rec_num, d.detail FROM table1 AS t, UNNEST (t.detail) AS d There is no WHERE clause, so it looks like a cartesian product, but actually the value of "t.detail" changes for each row of t. (UNNEST the closely related LATERAL keyword basically create correlated tables.) Julian * I haven't tried it. On Wed, Dec 23, 2015 at 6:56 PM, Homer <[email protected]> wrote: > 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
