[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
Re: [BUGS] BUG #8226: Inconsistent unnesting of arrays
On Wed, Jun 12, 2013 at 9:58 AM, ddeberna...@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
--
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, ddeberna...@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
--
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, ddeberna...@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
--
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 ddeberna...@yahoo.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, ddeberna...@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
--
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
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
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
[BUGS] BUG #8227: referential integrity problem
The following bug has been logged on the website: Bug reference: 8227 Logged by: Greg Brzezinski Email address: g...@antrez.pl PostgreSQL version: 9.2.4 Operating system: linux Description: postgres=# CREATE TABLE test( id int primary key, name text ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index test_pkey for table test CREATE TABLE postgres=# CREATE TABLE test_item( id int primary key, test_id int references test(id) match full on update cascade on delete cascade, content text ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index test_item_pkey for table test_item CREATE TABLE postgres=# CREATE OR REPLACE FUNCTION test_item_delete_lock() returns trigger AS $f$ postgres$# begin postgres$# return null; postgres$# end; postgres$# $f$ language plpgsql; CREATE FUNCTION postgres=# CREATE TRIGGER test_item_delete_lock BEFORE DELETE ON test_item FOR EACH ROW EXECUTE PROCEDURE test_item_delete_lock(); CREATE TRIGGER postgres=# postgres=# insert into test values (1, 'one'); INSERT 0 1 postgres=# insert into test_item values (1, 1, 'for one'); INSERT 0 1 postgres=# delete from test where id = 1; DELETE 1 postgres=# select * from test_item; id | test_id | content +-+- 1 | 1 | for one (1 row) postgres=# select * from test; id | name +-- (0 rows) Is it ok that we loose referential integrity by locking DELETE on table test_item ? -- 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 #8227: referential integrity problem
g...@antrez.pl writes: Is it ok that we loose referential integrity by locking DELETE on table test_item ? Yes. If you put a trigger on a table involved in an FK constraint, it's your responsibility that the trigger doesn't break FK update operations. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
