Re: [BUGS] BUG #8226: Inconsistent unnesting of arrays
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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8226: Inconsistent unnesting of arrays
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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] 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 : > 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, 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] 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, 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8226: Inconsistent unnesting of arrays
On Wed, Jun 12, 2013 at 9:58 AM, 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #8226: Inconsistent unnesting of arrays
On Wed, Jun 12, 2013 at 9:58 AM, 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] 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: ddeberna...@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=# 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=# 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) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs