Thread: Curious unnest behavior

I just ran into an interesting thing with unnest and empty arrays.

create table x (
    a int,
    b int[]
);

insert into x(a,b) values (1, '{}');
insert into x(a,b) values (1, '{}');
insert into x(a,b) values (1, '{}');

select a, b from x;
select a, unnest(b) from x;

insert into x(a,b) values (2, '{5,6}');
select a, unnest(b) from x;

drop table x;

gives me:
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
 a | b
---+----
 1 | {}
 1 | {}
 1 | {}
(3 rows)

 a | unnest
---+--------
(0 rows)

INSERT 0 1
 a | unnest
---+--------
 2 |      5
 2 |      6
(2 rows)

DROP TABLE

I can understand the likely reasoning behind the behavior but perhaps a note in the documentation about it might be of
usefor others that may get bit by this functionality.  (especially given the structure of the query, had I been doing
select* from unnest(arr) that would be more intuitive, but given the query structure of select with no where the
resultscan be surprising.) 

thanks

--
Jeff Trout <jeff@jefftrout.com>

Hello

2013/1/3 Jeff Trout <threshar@real.jefftrout.com>:
> I just ran into an interesting thing with unnest and empty arrays.
>
> create table x (
>         a int,
>         b int[]
> );
>
> insert into x(a,b) values (1, '{}');
> insert into x(a,b) values (1, '{}');
> insert into x(a,b) values (1, '{}');
>
> select a, b from x;
> select a, unnest(b) from x;
>
> insert into x(a,b) values (2, '{5,6}');
> select a, unnest(b) from x;
>
> drop table x;
>
> gives me:
> CREATE TABLE
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
>  a | b
> ---+----
>  1 | {}
>  1 | {}
>  1 | {}
> (3 rows)
>
>  a | unnest
> ---+--------
> (0 rows)
>
> INSERT 0 1
>  a | unnest
> ---+--------
>  2 |      5
>  2 |      6
> (2 rows)
>
> DROP TABLE
>
> I can understand the likely reasoning behind the behavior but perhaps a note in the documentation about it might be
ofuse for others that may get bit by this functionality.  (especially given the structure of the query, had I been
doingselect * from unnest(arr) that would be more intuitive, but given the query structure of select with no where the
resultscan be surprising.) 
>
> thanks
>

this behave (and it is really strange) is related to using SRF
function in target list - in column list. This functionality is
strange and if you can, don't use it.

originaly this functionality looks like good idea, because anybody can
play like me (or
http://www.mentby.com/Group/pgsql-general/set-returning-functions-in-select-column-list.html
)

postgres=# select unnest(array[1,2]),unnest(array[1,2]);
 unnest │ unnest
────────┼────────
      1 │      1
      2 │      2
(2 rows)

but it usually doesn't working like people expected

postgres=# select unnest(array[1,2]),unnest(array[1,2,3]);
 unnest │ unnest
────────┼────────
      1 │      1
      2 │      2
      1 │      3
      2 │      1
      1 │      2
      2 │      3
(6 rows)

postgres=# select unnest(array[1,2]),unnest(array[1,2,3,4]);
 unnest │ unnest
────────┼────────
      1 │      1
      2 │      2
      1 │      3
      2 │      4
(4 rows)

so result is - don't use SRF (set returning funtion)  in column list
if you don't need.

9.3 will support LATERAL clause, and I hope so we can drop this
functionality (one day)

Regards

Pavel Stehule

> --
> Jeff Trout <jeff@jefftrout.com>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general