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