Re: 9.2rc1 produces incorrect results

On 5 September 2012 05:09, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I wrote:
>> I think probably the best fix is to rejigger things so that Params
>> assigned by different executions of SS_replace_correlation_vars and
>> createplan.c can't share PARAM_EXEC numbers. This will result in
>> rather larger ecxt_param_exec_vals arrays at runtime, but the array
>> entries aren't very large, so I don't think it'll matter.
>
> Attached is a draft patch against HEAD for this. I think it makes the
> planner's handling of outer-level Params far less squishy than it's ever
> been, but it is rather a large change. Not sure whether to risk pushing
> it into 9.2 right now, or wait till after we cut 9.2.0 ... thoughts?

Just so someone else has tested the case in question, here's the
result this end:

id | array
----+-------
1 | {1}
1 | {1}
(2 rows)

QUERY PLAN
-------------------------------------------------------------------------------------------
Result (cost=131.45..133.07 rows=8 width=36)
CTE a
-> Nested Loop (cost=87.18..131.09 rows=7 width=4)
-> Merge Right Join (cost=87.18..123.33 rows=7 width=4)
Merge Cond: (((pg_c.relname)::text) = ((t2.id)::text))
Filter: (pg_c.oid IS NULL)
-> Sort (cost=22.82..23.55 rows=291 width=68)
Sort Key: ((pg_c.relname)::text)
-> Seq Scan on pg_class pg_c
(cost=0.00..10.91 rows=291 width=68)
-> Sort (cost=64.36..66.84 rows=993 width=4)
Sort Key: ((t2.id)::text)
-> Seq Scan on t2 (cost=0.00..14.93 rows=993 width=4)
-> Index Only Scan using t1_pkey on t1 (cost=0.00..1.10
rows=1 width=4)
Index Cond: (id = t2.id)
CTE b
-> WindowAgg (cost=0.24..0.36 rows=7 width=4)
-> Sort (cost=0.24..0.26 rows=7 width=4)
Sort Key: a.id
-> CTE Scan on a (cost=0.00..0.14 rows=7 width=4)
-> Append (cost=0.00..1.62 rows=8 width=36)
-> CTE Scan on a (cost=0.00..0.77 rows=4 width=4)
Filter: is_something
SubPlan 3
-> CTE Scan on b (cost=0.00..0.16 rows=1 width=4)
Filter: (id = a.id)
-> CTE Scan on a (cost=0.00..0.77 rows=4 width=4)
Filter: is_something
SubPlan 4
-> CTE Scan on b (cost=0.00..0.16 rows=1 width=4)
Filter: (id = a.id)
(30 rows)

As for shipping without the fix, I'm torn on whether to do so or not.
I imagine most productions will wait for a .1 or .2 release, and use
.0 for migration testing. Plus this bug hasn't been hit (or at least
not noticed) during 5 releases of 9.1, and there isn't enough time
left before shipping to expose the changes to enough testing in the
areas affected, so I'd be slightly inclined to push this into 9.1.6
and 9.2.1.

Regards

Thom