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
understandwhat's going wrong but if you change any part of this query (or at least any part I tried), the correct
resultis returned.<br /><br />This script will reproduce it:<br /><br />=====<br /><br />create table t1 (id integer
primarykey);<br />create table t2 (id integer primary key references t1 (id));<br /><br />insert into t1 (id) select
generate_series(1,100000); -- size matters<br /> insert into t2 (id) values (1); -- get a known value in the table<br
/>insertinto t2 (id) select g from generate_series(2, 100000) g where random() < 0.01; -- size matters again<br
/><br/>analyze t1;<br />analyze t2;<br /><br /> with<br />A as (<br /> select <a href="http://t2.id">t2.id</a>,<br
/> <a href="http://t2.id">t2.id</a> = 1 as is_something<br /> from t2<br /> join t1 on <a
href="http://t1.id">t1.id</a>= <a href="http://t2.id">t2.id</a><br /> left join pg_class pg_c on pg_c.relname =
t2.id::text-- I haven't tried on a user table<br /> where pg_c.oid is null<br />),<br /><br />B as (<br />
selectA.id,<br /> row_number() over (partition by A.id) as order -- this seems to be important, too<br />
from A<br />)<br /><br />select A.id, array(select B.id from B where B.id = A.id) from A where A.is_something<br
/>unionall<br />select A.id, array(select B.id from B where B.id = A.id) from A where A.is_something;<br /><br />
=====<br/><br />As you can (hopefully) see, the two UNIONed queries are identical but do not return the same values. I
wishI had the skills to attach a patch to this message, but alas I do not.<br />
