BUG #7535: ERROR: variable not found in subplan target list

The following bug has been logged on the website:

Bug reference:      7535
Logged by:          Louis-David Mitterrand
Email address:      ldm@apartia.fr
PostgreSQL version: 9.2.0
Operating system:   debian testing
Description:        =


/*error in 9.2*/
drop database error92;
create database error92;
\c error92
CREATE TABLE price (
    id_price serial primary key
);
CREATE TABLE cabin_class (
    id_cabin_class serial primary key,
    cabin_class_name text not null
);
CREATE TABLE cabin_type (
    id_cabin_type serial primary key,
    id_cabin_class integer references cabin_class not null,
    cabin_type_name text not null,
    cabin_type_code text not null
);
CREATE TABLE cabin_category (
    id_cabin_category serial primary key,
    id_cabin_type integer references cabin_type not null,
    cabin_cat_name text,
    cabin_cat_code text
);
CREATE TABLE alert_cruise (
    id_alert_cruise serial primary key,
/*    id_cruise integer references cruise not null,
    id_currency integer references currency,*/
    enabled boolean default true not null,
    md5_code text DEFAULT md5(now()::text || random()::text)
);
CREATE TABLE alert_cabin_category (
    id_alert_cruise integer references alert_cruise,
    id_cabin_category integer references cabin_category not null
);
CREATE TABLE alert_cabin_type (
    id_alert_cruise integer references alert_cruise,
    id_cabin_type integer references cabin_type not null
);
CREATE TABLE alert_cabin_class (
    id_alert_cruise integer references alert_cruise,
    id_cabin_class integer references cabin_class not null
);
CREATE VIEW alert_cruise_all AS
    select c.*, ac.enabled
    from (
        -- ac.id_cabin_class is the first one, so other UNION parts will
        -- use that column name as well
        select 'class' as type,cl.cabin_class_name as type_name,ac.*
        from alert_cabin_class ac
        join cabin_class cl using (id_cabin_class)
    union all
    select 'type' as type,cl.cabin_class_name||case when cl.cabin_class_name =
=3D
ct.cabin_type_name then '' else ' ~ '||ct.cabin_type_name end as
type_name,ac.*
        from alert_cabin_type ac
        join cabin_type ct using (id_cabin_type)
        join cabin_class cl using (id_cabin_class)
    union all
        select 'category' as type,cl.cabin_class_name||
        case when cl.cabin_class_name =3D ct.cabin_type_name
        then '' else ' ~ '||ct.cabin_type_name end||' ~ '||cc.cabin_cat_code
        as type_name,ac.*
        from alert_cabin_category ac
        join cabin_category cc
        join cabin_type ct using (id_cabin_type)
        join cabin_class cl using (id_cabin_class)
        using (id_cabin_category)
        )
    as c join alert_cruise ac using (id_alert_cruise);
create view alert_to_category as
    select ac.*, cl.cabin_class_name, ct.cabin_type_name, cc.cabin_cat_name,
    cc.id_cabin_category, cc.cabin_cat_code,
    case when type=3D'class' then cl.cabin_class_name
    when type=3D'type' then ct.cabin_type_name
    when type=3D'category' then ct.cabin_type_name||' '||cc.cabin_cat_code
    end as cabin_name
    from alert_cruise_all ac
    left join cabin_class cl on (
        (ac.type =3D 'class' and cl.id_cabin_class=3Dac.id_cabin_class)
        or (ac.type =3D 'type' and cl.id_cabin_class=3D(select id_cabin_cla=
ss
from cabin_type where id_cabin_type=3Dac.id_cabin_class))
        or (ac.type =3D 'category' and cl.id_cabin_class=3D(select
ct2.id_cabin_class from cabin_type ct2 join cabin_category cc2 using
(id_cabin_type) where cc2.id_cabin_category=3Dac.id_cabin_class))
    )
    join cabin_type ct on (
        (ac.type =3D 'class' and  ct.id_cabin_class=3Dcl.id_cabin_class)
        or (ac.type =3D 'type' and ct.id_cabin_type=3Dac.id_cabin_class)
        or (ac.type =3D 'category' and ct.id_cabin_type=3D(select id_cabin_=
type
from cabin_category where id_cabin_category=3Dac.id_cabin_class))
    )
    join cabin_category cc on (
        (ac.type =3D 'category' and cc.id_cabin_category=3Dac.id_cabin_class)
        or (ac.type !=3D 'category' and ct.id_cabin_type=3Dcc.id_cabin_type)
    );
select =

first_value(max(p.id_price)) over () as id_price1
,ac.cabin_name
        from alert_to_category ac
        join price p on (p.id_price=3Dac.id_alert_cruise)
        group by ac.cabin_name;
/*
select =

first_value(max(p.id_price)) over () as id_price1
,ac.cabin_name
        from alert_to_category ac
        join price p on (p.id_cruise=3Dac.id_cruise)
        group by ac.cabin_name;
*/
/*EOF*/