BTW, after considerable fooling around with Vik's example, I've been
able to produce a regression test case that fails in all PG versions
with WITH:
with
A as ( select q2 as id, (select q1) as x from int8_tbl ),
B as ( select id, row_number() over (partition by id) as r from A ),
C as ( select A.id, array(select B.id from B where B.id = A.id) from A )
select * from C;
The correct answer to this is
id | array
-------------------+------------------------------------- 456 | {456} 4567890123456789 |
{4567890123456789,4567890123456789} 123 | {123} 4567890123456789 |
{4567890123456789,4567890123456789}-4567890123456789| {-4567890123456789}
(5 rows)
as you can soon convince yourself by inspecting the contents of
int8_tbl:
q1 | q2
------------------+------------------- 123 | 456 123 |
45678901234567894567890123456789| 1234567890123456789 | 45678901234567894567890123456789 |
-4567890123456789
(5 rows)
I got that answer with patched HEAD, but all the back branches
give me
id | array
-------------------+------------------------------------- 456 | {4567890123456789,4567890123456789}
4567890123456789| {4567890123456789,4567890123456789} 123 | {123} 4567890123456789 |
{4567890123456789,4567890123456789}-4567890123456789| {-4567890123456789}
(5 rows)
So this does indeed need to be back-patched as far as 8.4.
regards, tom lane
