Re: New Instance of Variable Not Found in Subplan Bug

Tom,

Further information:

> CREATE VIEW "sv_cases" as
> SELECT cases.case_id, cases.case_name, cases.docket, status.status_label,
> cases.opp_counsel_name, trial_groups.tgroup_name, cases.tgroup_id,
> cases.status, cases.lead_case_docket, cases.lead_case_id,
> cases.priority, tpr.rollup1 as pr_element, tpr.rollup2 as pr_label
> FROM status,
> ( SELECT vchar_to_int2(list_value) as priority, rollup1, rollup2
> from text_list_values WHERE list_name = 'Case Priority' ) tpr,
> cases LEFT JOIN trial_groups on cases.tgroup_id = trial_groups.tgroup_id
> WHERE (cases.status = status.status AND status.relation =
'cases'::"varchar")
> AND cases.priority = tpr.priority;

In the above view, text_list_values is another, simple view. Removing that
view from the equation fixed it, becuase it turns out that the issue is with
the text_list_value view:

CREATE VIEW text_list_values AS
SELECT text_lists.list_id, text_lists.list_name, text_lists.list_group,
text_lists.app_id,
text_lists.status AS list_status, s1.status_label AS list_status_label,
text_lists.list_format,
text_lists.item_length, list_values.value_id, list_values.list_value,
list_values.description,
list_values.rollup1, list_values.rollup2, list_values.status AS value_status,
s2.status AS value_status_label
FROM text_lists
JOIN list_values USING (list_id)
JOIN status s1 ON text_lists.status = s1.status
AND s1.relation::text = 'text_lists'::character varying::text
JOIN status s2 ON list_values.status = s2.status
AND s2.relation::text = 'list_values'::character varying::text;

RELOADING the view fixed the error. Here's the EXPLAIN plan:

QUERY PLAN
------------------------------------------------------------------------------------------------
Merge Join (cost=14.51..15.69 rows=66 width=130)
Merge Cond: ("outer".status = "inner".status)
-> Sort (cost=1.94..1.94 rows=3 width=2)
Sort Key: s2.status
-> Seq Scan on status s2 (cost=0.00..1.91 rows=3 width=2)
Filter: ((relation)::text = 'list_values'::text)
-> Sort (cost=12.57..12.83 rows=102 width=128)
Sort Key: list_values.status
-> Hash Join (cost=4.11..9.17 rows=102 width=128)
Hash Cond: ("outer".list_id = "inner".list_id)
-> Seq Scan on list_values (cost=0.00..3.36 rows=136
width=69)
-> Hash (cost=4.06..4.06 rows=18 width=63)
-> Merge Join (cost=3.74..4.06 rows=18 width=63)
Merge Cond: ("outer".status = "inner".status)
-> Sort (cost=1.95..1.96 rows=4 width=16)
Sort Key: s1.status
-> Seq Scan on status s1 (cost=0.00..1.91
rows=4 width=16)
Filter: ((relation)::text =
'text_lists'::text)
-> Sort (cost=1.79..1.85 rows=24 width=49)
Sort Key: text_lists.status
-> Seq Scan on text_lists (cost=0.00..1.24
rows=24 width=49)

Suggestions on how to diagnose this, before I erase all evidence of it?

--
-Josh Berkus
Aglio Database Solutions
San Francisco