Re: BUG #8226: Inconsistent unnesting of arrays

Hello

it is known old strange feature

http://postgresql.1045698.n5.nabble.com/Set-returning-functions-in-select-c=
olumn-list-td5491544.html

Regards

Pavel Stehule

p.s. don't use this feature, it is strange - and we cannot change
behave due compatibility reasons.



2013/6/12 Denis de Bernardy <ddebernardy@yahoo.com>:
> The actual query was something like:
>
> select id, person, unnest(groups) as grp from people
>
> =E2=80=A6 where groups is a crazy column containing an array that needed =
to be joined with another table. In this case, you cannot do your suggested=
 solution, which would look like this:
>
> select id, person, grp from people, unnest(groups) as grp
>
> Admittedly, there are other ways to rewrite the above, but =E2=80=94 if I=
 may =E2=80=94 that's entirely besides the point of the bug report. The Sta=
ck Overflow question got me curious about what occurred when two separate a=
rrays are unnested.
>
> Testing revealed the inconsistency, which I tend to view as a bug.
>
> This statement works as expected, unnesting the first array, then cross j=
oining the second accordingly:
>
>>> select 1 as a, unnest('{2,3}'::int[]) as b, unnest('{4,5,6}'::int[])
>
>
> This seems to only unnest one of the arrays, and match the element with t=
he same subscript in the other array:
>
>>> select 1 as a, unnest('{2,3}'::int[]) as b, unnest('{4,5}'::int[])
>
>
> Methinks the behavior should be consistent. It should always do one (pres=
umably like in the first statement) or the other (which leads to undefined =
behavior in the first statement).
>
> Or it should raise some kind of warning, e.g. "you're using undocumented/=
unsupported/deprecated/broken syntactic sugar".
>
> Denis
>
>
> On Jun 12, 2013, at 12:05 PM, Greg Stark wrote:
>
>> On Wed, Jun 12, 2013 at 9:58 AM,  <ddebernardy@yahoo.com> wrote:
>>> denis=3D# select 1 as a, unnest('{2,3}'::int[]) as b, unnest('{4,5}'::i=
nt[])
>>
>> set returning functions in the target list of the select don't behave
>> the way you're thinking. What you probably want to do is move the
>> unnest() to the FROM clause:
>>
>> select 1 as a, b, c from unnest('{2,3}'::int[]) as b(b),
>> unnest('{4,5}'::int[]) as c(c)
>>
>>
>> --
>> greg
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs