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-column-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(at)yahoo(dot)com>:
> The actual query was something like:
>
> select id, person, unnest(groups) as grp from people
>
> … 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 — if I may — that's entirely besides the point of the bug report. The Stack Overflow question got me curious about what occurred when two separate arrays 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 joining 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 the 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 (presumably 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(at)yahoo(dot)com> wrote:
>>> denis=# select 1 as a, unnest('{2,3}'::int[]) as b, unnest('{4,5}'::int[])
>>
>> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs