UNNEST Question

[Homer <lo...@gmail.com>]

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

Re: UNNEST Question

[Julian Hyde <jh...@apache.org>]

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 <lo...@gmail.com> 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