Re: BUG #8226: Inconsistent unnesting of arrays

The actual query was something like:

select id, person, unnest(groups) as grp from people

=85 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 =97 if I may =
=97 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@yahoo.com> wrote:
>> denis=3D# select 1 as a, unnest('{2,3}'::int[]) as b, =
unnest('{4,5}'::int[])
>=20
> 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:
>=20
> select 1 as a, b, c from unnest('{2,3}'::int[]) as b(b),
> unnest('{4,5}'::int[]) as c(c)
>=20
>=20
> --=20
> greg