Search Postgresql Archives

Re: Curious unnest behavior

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello

2013/1/3 Jeff Trout <threshar@xxxxxxxxxxxxxxxxxx>:
> 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@xxxxxxxxxxxxx>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux