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.
