Re: BUG #8226: Inconsistent unnesting of arrays

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