From: | Vik Reykja <vikreykja(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | 9.2rc1 produces incorrect results |
Date: | 2012-09-04 12:38:41 |
Message-ID: | CALDgxVvXrG3gxu0szPQ1XHkNb3bCUUDQCojnW=zg+nSAF6iaQQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hello. It took me a while to get a version of this that was independent of
my data, but here it is. I don't understand what's going wrong but if you
change any part of this query (or at least any part I tried), the correct
result is returned.
This script will reproduce it:
=====
create table t1 (id integer primary key);
create table t2 (id integer primary key references t1 (id));
insert into t1 (id) select generate_series(1, 100000); -- size matters
insert into t2 (id) values (1); -- get a known value in the table
insert into t2 (id) select g from generate_series(2, 100000) g where
random() < 0.01; -- size matters again
analyze t1;
analyze t2;
with
A as (
select t2.id,
t2.id = 1 as is_something
from t2
join t1 on t1.id = t2.id
left join pg_class pg_c on pg_c.relname = t2.id::text -- I haven't
tried on a user table
where pg_c.oid is null
),
B as (
select A.id,
row_number() over (partition by A.id) as order -- this seems
to be important, too
from A
)
select A.id, array(select B.id from B where B.id = A.id) from A where
A.is_something
union all
select A.id, array(select B.id from B where B.id = A.id) from A where
A.is_something;
=====
As you can (hopefully) see, the two UNIONed queries are identical but do
not return the same values. I wish I had the skills to attach a patch to
this message, but alas I do not.
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2012-09-04 12:48:06 | Re: [COMMITTERS] pgsql: Make a cut at a major-features list for 9.2. |
Previous Message | Kevin Grittner | 2012-09-04 11:47:49 | Re: index-only scans versus serializable transactions |