Обсуждение: BUG #8226: Inconsistent unnesting of arrays

The following bug has been logged on the website:

Bug reference:      8226
Logged by:          Denis de Bernardy
Email address:      ddebernardy@yahoo.com
PostgreSQL version: 9.2.4
Operating system:   OSX
Description:        =


It looks like unnest() in a select statement doesn't behave consistently
based on the number of elements in the array.

This works as expected:

denis=3D# select 1 as a, unnest('{2,3}'::int[]) as b, unnest('{4,5,6}'::int=
[])
as c order by a, b, c;
 a | b | c =

---+---+---
 1 | 2 | 4
 1 | 2 | 5
 1 | 2 | 6
 1 | 3 | 4
 1 | 3 | 5
 1 | 3 | 6
(6 rows)

This doesn't (it's missing (1,3,4) and (1,2,5)):

denis=3D# select 1 as a, unnest('{2,3}'::int[]) as b, unnest('{4,5}'::int[])
as c order by a, b, c;
 a | b | c =

---+---+---
 1 | 2 | 4
 1 | 3 | 5
(2 rows)

On Wed, Jun 12, 2013 at 9:58 AM,  <ddebernardy@yahoo.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

On Wed, Jun 12, 2013 at 9:58 AM,  <ddebernardy@yahoo.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

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

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

On Wed, Jun 12, 2013 at 12:19:51PM +0200, Denis de Bernardy wrote:
> 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).

It is consistent. You just assume it does something else than what it
does.

It reads values from both sources, until both of them will at the same
time.
If any of them ends earlier, it is scanned again from beginning.

This can be seen, for example, with:

select unnest('{a,b,c,d,e,f}'::text[]), unnest('{1,2,3}'::text[]);

Or, perhaps better, with this one:

select unnest('{a,b,c,d,e,f}'::text[]), unnest('{1,2,3,4}'::text[]);

It doesn't show 6 rows (as first array), or 4 (as the other). or 24 (6
* 4). It shows 12, because this is the smallest common multiple of 6 and
4.

depesz

On Wed, Jun 12, 2013 at 12:19:51PM +0200, Denis de Bernardy wrote:
> … 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:

Missed this one.

You don't need to unpack to join.

you can do:

select * from tablea join tableb on tableb.id = any (tablea.array_column);

depesz