9.2rc1 produces incorrect results

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.

Responses

Browse pgsql-hackers by date

  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