Re: Curious unnest behavior

Hello

2013/1/3 Jeff Trout <threshar(at)real(dot)jefftrout(dot)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 of use for 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 results can 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(at)jefftrout(dot)com>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general