BUG #5548: ERROR: invalid attnum ## for rangetable entry on EXPLAIN VERBOSE, not on EXPLAIN

The following bug has been logged online:

Bug reference: 5548
Logged by: Daniel Grace
Email address: dgrace(at)wingsnw(dot)com
PostgreSQL version: 9.0beta2
Operating system: Windows XP 32-bit
Description: ERROR: invalid attnum ## for rangetable entry on
EXPLAIN VERBOSE, not on EXPLAIN
Details:

I apologize for not including detailed schema information. It took a lot to
get this to reduce to the point it did, and hopefully this is enough
information to find a bug. If not, email me back and I'll see how much
schema information I can provide.

When using EXPLAIN [ANALYZE] VERBOSE on the below query:

SELECT t.*
FROM (
SELECT
TRUE AS is_enrolled,

acal.weight::REAL / (SELECT SUM(acal2.weight) FROM allocation_calendar AS
acal2 WHERE acal.year=acal2.year)::REAL AS calc_weight,
(
TRUNC(EXTRACT(EPOCH FROM LENGTH(
PERIOD(
GREATEST(FIRST(acal.daterange), acd.tstime),
LEAST(NEXT(acal.daterange), FIRST_VALUE(acd.tstime) OVER nextdate)
)
))) / TRUNC(EXTRACT(EPOCH FROM LENGTH(acal.daterange)))
) AS calc_duration,

NULL::integer AS group_id
FROM
allocation_calculated_dates AS acd
INNER JOIN allocation_calendar AS acal ON acd.acalid=acal.id
INNER JOIN log_status AS ls ON ls.sid=acd.sid AND ls.tsrange ~ acd.tstime
WINDOW
nextdate AS ( PARTITION BY acd.sid, acd.acalid ORDER BY acd.tstime ASC
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING )
) AS t
CROSS JOIN yearinfo AS yi -- ON t.year=yi.year
LEFT JOIN group_info AS gi ON gi.id=t.group_id
WHERE t.is_enrolled /* AND yi.allocation_lock=0 */

I receive the following result:

ERROR: invalid attnum 5 for rangetable entry t

This appears to be originating from get_rte_attribute_name() in
parse_relation.c (which might need to consider RTE_SUBQUERY?)

Stripping the final WHERE clause out (WHERE t.is_enrolled) causes the error
to go away, as do most modifications to joined tables.

When ran as a regular SELECT, the query runs fine and produces correct
outputs.

A regular EXPLAIN yields:
"Nested Loop (cost=1136.45..1146.96 rows=16 width=17)"
" -> Subquery Scan on t (cost=1136.45..1145.71 rows=4 width=17)"
" Filter: t.is_enrolled"
" -> WindowAgg (cost=1136.45..1145.63 rows=8 width=38)"
" -> Sort (cost=1136.45..1136.47 rows=8 width=38)"
" Sort Key: s.id, wings_demo.allocation_calendar.id,
(GREATEST(first(ls.tsrange), first(CASE WHEN
(wings_demo.allocation_calendar.countdate IS NULL) THEN
wings_demo.allocation_calendar.daterange ELSE
period_cc((wings_demo.allocation_calendar.countdate)::timestamp with time
zone, (wings_demo.allocation_calendar.countdate)::timestamp with time zone)
END)))"
" -> Hash Join (cost=1055.63..1136.33 rows=8
width=38)"
" Hash Cond: (ls.sid = s.id)"
" Join Filter: (ls.tsrange ~
(GREATEST(first(ls.tsrange), first(CASE WHEN
(wings_demo.allocation_calendar.countdate IS NULL) THEN
wings_demo.allocation_calendar.daterange ELSE
period_cc((wings_demo.allocation_calendar.countdate)::timestamp with time
zone, (wings_demo.allocation_calendar.countdate)::timestamp with time zone)
END))))"
" -> Seq Scan on log_status ls (cost=0.00..76.26
rows=1126 width=20)"
" -> Hash (cost=1055.56..1055.56 rows=6
width=38)"
" -> Hash Join (cost=1055.07..1055.56
rows=6 width=38)"
" Hash Cond:
(wings_demo.allocation_calendar.id = acal.id)"
" -> HashAggregate
(cost=1053.93..1054.11 rows=18 width=46)"
" -> Append
(cost=561.01..1053.75 rows=18 width=46)"
" -> Merge Left Join
(cost=561.01..596.61 rows=17 width=46)"
" Merge Cond: ((s.id
= ao.sid) AND (wings_demo.allocation_calendar.year = ao.year))"
" Filter: (ao.amount
IS NULL)"
" -> Sort
(cost=472.83..481.28 rows=3378 width=46)"
" Sort Key:
s.id, wings_demo.allocation_calendar.year"
" -> Nested
Loop (cost=30.02..274.85 rows=3378 width=46)"
" Join
Filter: (CASE WHEN (wings_demo.allocation_calendar.countdate IS NULL) THEN
wings_demo.allocation_calendar.daterange ELSE
period_cc((wings_demo.allocation_calendar.countdate)::timestamp with time
zone, (wings_demo.allocation_calendar.countdate)::timestamp with time zone)
END && ls.tsrange)"
" ->
Hash Join (cost=30.02..121.77 rows=1126 width=20)"
"
Hash Cond: (ls.sid = s.id)"
"
-> Seq Scan on log_status ls (cost=0.00..76.26 rows=1126 width=20)"
"
-> Hash (cost=25.01..25.01 rows=401 width=4)"
"
-> Seq Scan on student s (cost=0.00..25.01 rows=401 width=4)"
" ->
Materialize (cost=0.00..1.09 rows=6 width=26)"
"
-> Seq Scan on allocation_calendar (cost=0.00..1.06 rows=6 width=26)"
" -> Sort
(cost=88.17..91.35 rows=1270 width=19)"
" Sort Key:
ao.sid, ao.year"
" -> Seq Scan
on allocation_override ao (cost=0.00..22.70 rows=1270 width=19)"
" -> Hash Left Join
(cost=350.15..456.97 rows=1 width=54)"
" Hash Cond: (lg.sid
= lgprior.sid)"
" Join Filter:
((lgprior.id <> lg.id) AND (COALESCE(lgprior.allocation_priority,
giprior.allocation_priority) > COALESCE(lg.allocation_priority,
gi.allocation_priority)) AND (lgprior.tsrange ~ first(lg.tsrange)))"
" Filter: (((ao.sid
IS NULL) AND (lgprior.id IS NULL)) OR ((lgnext.id IS NULL) AND (NOT
lg.ignore_allocation_method) AND (gi.allocation_method_id IS NOT NULL)))"
" -> Hash Left Join
(cost=313.79..382.24 rows=963 width=75)"
" Hash Cond:
(lg.sid = lgnext.sid)"
" Join Filter:
((lgnext.id <> lg.id) AND (COALESCE(lgnext.allocation_priority,
ginext.allocation_priority) > COALESCE(lg.allocation_priority,
gi.allocation_priority)) AND (lgnext.tsrange ~ last(lg.tsrange)))"
" -> Hash Join
(cost=277.42..307.56 rows=963 width=71)"
" Hash
Cond: (lg.gid = gi.id)"
" ->
Merge Left Join (cost=276.33..293.23 rows=963 width=67)"
"
Merge Cond: ((s.id = ao.sid) AND (wings_demo.allocation_calendar.year =
ao.year))"
"
-> Sort (cost=188.16..190.57 rows=963 width=63)"
"
Sort Key: lg.sid, wings_demo.allocation_calendar.year"
"
-> Hash Join (cost=35.64..140.44 rows=963 width=63)"
"
Hash Cond: (lg.sid = s.id)"
"
-> Nested Loop (cost=5.61..97.17 rows=963 width=59)"
"
Join Filter: (NOT (lg.tsrange @> CASE WHEN
(wings_demo.allocation_calendar.countdate IS NULL) THEN
wings_demo.allocation_calendar.daterange ELSE
period_cc((wings_demo.allocation_calendar.countdate)::timestamp with time
zone, (wings_demo.allocation_calendar.countdate)::timestamp with time zone)
END))"
"
-> Seq Scan on allocation_calendar (cost=0.00..1.06 rows=6
width=26)"
"
-> Bitmap Heap Scan on log_group lg (cost=5.61..15.93
rows=3 width=33)"
"
Recheck Cond: (CASE WHEN
(wings_demo.allocation_calendar.countdate IS NULL) THEN
wings_demo.allocation_calendar.daterange ELSE
period_cc((wings_demo.allocation_calendar.countdate)::timestamp with time
zone, (wings_demo.allocation_calendar.countdate)::timestamp with time zone)
END && lg.tsrange)"
"
-> Bitmap Index Scan on log_group_gix_tsrange
(cost=0.00..5.61 rows=3 width=0)"
"
Index Cond: (CASE WHEN
(wings_demo.allocation_calendar.countdate IS NULL) THEN
wings_demo.allocation_calendar.daterange ELSE
period_cc((wings_demo.allocation_calendar.countdate)::timestamp with time
zone, (wings_demo.allocation_calendar.countdate)::timestamp with time zone)
END && lg.tsrange)"
"
-> Hash (cost=25.01..25.01 rows=401 width=4)"
"
-> Seq Scan on student s (cost=0.00..25.01 rows=401
width=4)"
"
-> Sort (cost=88.17..91.35 rows=1270 width=8)"
"
Sort Key: ao.sid, ao.year"
"
-> Seq Scan on allocation_override ao (cost=0.00..22.70 rows=1270
width=8)"
" ->
Hash (cost=1.04..1.04 rows=4 width=12)"
"
-> Seq Scan on group_info gi (cost=0.00..1.04 rows=4 width=12)"
" -> Hash
(cost=28.34..28.34 rows=642 width=32)"
" ->
Hash Join (cost=1.09..28.34 rows=642 width=32)"
"
Hash Cond: (lgnext.gid = ginext.id)"
"
-> Seq Scan on log_group lgnext (cost=0.00..18.42 rows=642 width=32)"
"
Filter: (NOT ignore_allocation_method)"
"
-> Hash (cost=1.04..1.04 rows=4 width=8)"
"
-> Seq Scan on group_info ginext (cost=0.00..1.04 rows=4 width=8)"
"
Filter: (allocation_method_id IS NOT NULL)"
" -> Hash
(cost=28.34..28.34 rows=642 width=32)"
" -> Hash Join
(cost=1.09..28.34 rows=642 width=32)"
" Hash
Cond: (lgprior.gid = giprior.id)"
" -> Seq
Scan on log_group lgprior (cost=0.00..18.42 rows=642 width=32)"
"
Filter: (NOT ignore_allocation_method)"
" ->
Hash (cost=1.04..1.04 rows=4 width=8)"
"
-> Seq Scan on group_info giprior (cost=0.00..1.04 rows=4 width=8)"
"
Filter: (allocation_method_id IS NOT NULL)"
" -> Hash (cost=1.06..1.06 rows=6
width=26)"
" -> Seq Scan on
allocation_calendar acal (cost=0.00..1.06 rows=6 width=26)"
" SubPlan 1"
" -> Aggregate (cost=1.08..1.09 rows=1 width=4)"
" -> Seq Scan on allocation_calendar acal2
(cost=0.00..1.08 rows=2 width=4)"
" Filter: ($0 = year)"
" -> Materialize (cost=0.00..1.06 rows=4 width=0)"
" -> Seq Scan on yearinfo yi (cost=0.00..1.04 rows=4 width=0)"