Re: variable not found in subplan target list? + test case - Mailing list pgsql-bugs

On Sun, 23 Nov 2003 22:48:17 -0800 (PST), Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> On Mon, 24 Nov 2003, Klint Gore wrote:
>
> > I've got the message "ERROR:  variable not found in subplan target lists"
> > when I have a union between 2 views.  Both selects from the views run
> > without the union.  Same result using intersect and except.  Same result
> > using any of them with the all qualifier.  Explain of the union gives
> > the error, explain of each individual select gives a result.
> >
> > The view definitions contain unions and cross joins.
> >
> > The error still occurs if I simplify the selects down to
> >
> >    select 'abc' from view1
> >    union
> >    select 'cba' from view2
> >
> > "PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC 2.96"
> > redhat 7.2 (2.4.7-10)
>
> Can you send a test case with table and view definitions, it's hard to
> generate one from just the information above.

ok.  the last select generates the error.  I used pgadmin3 and did
create database and only gave it a name.  Then opened a sql window and
ran the creates.  Then ran the final select and the error happened.  I
didnt put any data in the tables.

This test I ran on w2k(server) sp4, using "PostgreSQL 7.4 on
i686-pc-cygwin, compiled by GCC gcc (GCC) 3.2 20020927 (prerelease)"

Same result on the linux from first post.

---- SETUP -------------------------------
CREATE TABLE "component_params" (
    "soc_code" character varying(7) NOT NULL,
    "form_name" character varying(64) NOT NULL,
    "com_name" character varying(64) NOT NULL,
    "com_label" character varying(64),
    "com_data_type" character varying(1) DEFAULT 'V',
    "com_type" character varying(3),
    "com_enabled" character varying(1) DEFAULT 'Y',
    "com_enable_on_add" character varying(1) DEFAULT 'Y',
    "com_visible" character varying(1) DEFAULT 'Y',
    "com_default_value" character varying(64),
    "com_min_value" numeric(12,4) DEFAULT 0,
    "com_max_value" numeric(12,4) DEFAULT 0,
    "com_min_length" integer DEFAULT 0,
    "com_max_length" integer DEFAULT 0,
    "com_case" character varying(1) DEFAULT 'U',
    "com_unique" boolean DEFAULT 'f'::bool,
    "com_align" character varying(1) DEFAULT 'L',
    "com_top" integer DEFAULT 0,
    "com_left" integer DEFAULT 0,
    "com_width" integer DEFAULT 0,
    "com_hint_level" integer DEFAULT 1,
    "com_reqd" character varying(1) DEFAULT 'N',
    "com_pair_name" character varying(64),
    "com_pair_type" character varying(1),
    "com_pair_null_values" character varying(32),
    "com_combo_size" integer,
    "com_hint" character varying(255),
    "com_label_hint" character varying(255),
    "com_default_sql" text,
    "com_combo_sql" text,
    "com_reqd_sql" character varying(255),
    "com_help" text,
    "create_method" integer,
    "create_date" timestamp(0) with time zone,
    "create_user_id" character varying(8),
    "last_upd_method" integer,
    "last_upd_date" timestamp(0) with time zone,
    "last_upd_user_id" character varying(8),
    Constraint "component_params_pkey" Primary Key ("soc_code", "form_name", "com_name")
);


CREATE TABLE "societies" (
    "soc_code" character varying(7) NOT NULL,
    "soc_short_name" character varying(32),
    "soc_package_list" character varying,
    "create_date" timestamp with time zone,
    "create_user_id" character varying(8),
    "last_upd_date" timestamp with time zone,
    "last_upd_user_id" character varying(8),
    Constraint "societies_pkey" Primary Key ("soc_code")
);


create view component_params_v as
 select
    soc_code,
        form_name,
    com_name,
    com_label,
    com_data_type,
    com_type,
    com_enabled,
    com_visible,
    com_default_value,
    com_min_value,
    com_max_value,
    com_min_length,
    com_max_length,
    com_case,
    com_align,
    com_top,
    com_left,
    com_width,
    com_help,
    com_reqd,
    com_pair_name,
    com_pair_type,
    com_pair_null_values,
    com_combo_size,
    com_hint,
    com_label_hint,
    com_default_sql,
    cast(com_combo_sql as text) as com_combo_sql,
    com_reqd_sql,
    create_method,
    create_date,
    create_user_id,
    last_upd_method,
    last_upd_date,
    last_upd_user_id
 from component_params
 where soc_code <> 'GENERIC'
union
 select
    societies.soc_code,
        p.form_name,
    p.com_name,
    p.com_label,
    p.com_data_type,
    p.com_type,
    p.com_enabled,
    p.com_visible,
    p.com_default_value,
    p.com_min_value,
    p.com_max_value,
    p.com_min_length,
    p.com_max_length,
    p.com_case,
    p.com_align,
    p.com_top,
    p.com_left,
    p.com_width,
    p.com_help,
    p.com_reqd,
    p.com_pair_name,
    p.com_pair_type,
    p.com_pair_null_values,
    p.com_combo_size,
    p.com_hint,
    p.com_label_hint,
    p.com_default_sql,
    cast(p.com_combo_sql as text) as com_combo_sql,
    p.com_reqd_sql,
    p.create_method,
    p.create_date,
    p.create_user_id,
    p.last_upd_method,
    p.last_upd_date,
    p.last_upd_user_id

 from component_params p
 cross join societies
 where p.soc_code = 'GENERIC'
 and not exists (select * from component_params p1 where p1.soc_code = societies.soc_code and p1.form_name =
p.form_nameand p1.com_name = p.com_name); 


CREATE TABLE "params" (
    "soc_code" character varying(7) NOT NULL,
    "param_key" character varying(32) NOT NULL,
    "param_code" character varying(12) NOT NULL,
    "param_group_code" character varying(32),
    "param_key_grouping" character varying(32),
    "param_column_name" character varying(32),
    "param_value" integer,
    "param_value2" integer,
    "param_value3" integer,
    "param_date" date,
    "param_date2" date,
    "param_date3" date,
    "param_abbrev" character varying(32),
    "param_desc" character varying(512),
    "param_string" text,
    "param_sort_seq" integer,
    "param_start_date" date,
    "param_end_date" date,
    "create_method" integer,
    "create_date" timestamp(0) with time zone,
    "create_user_id" character varying(8),
    "last_upd_method" integer,
    "last_upd_date" timestamp(0) with time zone,
    "last_upd_user_id" character varying(8),
    Constraint "params_pkey" Primary Key ("soc_code", "param_key", "param_code")
);


create view params_v as

select
    soc_code,
    param_key,
    param_code,
    param_group_code,
    param_key_grouping,
    param_column_name,
    param_value,
    param_value2,
    param_value3,
param_date,
param_date2,
param_date3,
param_abbrev,
param_desc,
param_sort_seq,
param_start_date,
param_end_date,
create_method,
create_date,
create_user_id,
last_upd_method,
last_upd_date,
last_upd_user_id
 from params
where soc_code <> 'GENERIC'
union
select
societies.soc_code,
p.param_key,
p.param_code,
p.param_group_code,
p.param_key_grouping,
p.param_column_name,
p.param_value,
p.param_value2,
p.param_value3,
p.param_date,
p.param_date2,
p.param_date3,
p.param_abbrev,
p.param_desc,
p.param_sort_seq,
p.param_start_date,
p.param_end_date,
p.create_method,
p.create_date,
p.create_user_id,
p.last_upd_method,
p.last_upd_date,
p.last_upd_user_id
 from params p
 cross join societies
where p.soc_code = 'GENERIC'
and not exists (select * from params p1 where p1.soc_code = societies.soc_code and p1.param_key = p.param_key)
and p.param_key <> 'ERRORS'
and p.param_key <> 'MESSAGES'

union

select
societies.soc_code,
p.param_key,
p.param_code,
p.param_group_code,
p.param_key_grouping,
p.param_column_name,
p.param_value,
p.param_value2,
p.param_value3,
p.param_date,
p.param_date2,
p.param_date3,
p.param_abbrev,
p.param_desc,
p.param_sort_seq,
p.param_start_date,
p.param_end_date,
p.create_method,
p.create_date,
p.create_user_id,
p.last_upd_method,
p.last_upd_date,
p.last_upd_user_id
 from params p
 cross join societies
where p.soc_code = 'GENERIC'
and not exists (select * from params p1 where p1.soc_code = societies.soc_code and p1.param_key = p.param_key and
p1.param_code= p.param_code) 
and p.param_key = 'ERRORS'

union

select
societies.soc_code,
p.param_key,
p.param_code,
p.param_group_code,
p.param_key_grouping,
p.param_column_name,
p.param_value,
p.param_value2,
p.param_value3,
p.param_date,
p.param_date2,
p.param_date3,
p.param_abbrev,
p.param_desc,
p.param_sort_seq,
p.param_start_date,
p.param_end_date,
p.create_method,
p.create_date,
p.create_user_id,
p.last_upd_method,
p.last_upd_date,
p.last_upd_user_id
 from params p
 cross join societies
where p.soc_code = 'GENERIC'
and not exists (select * from params p1 where p1.soc_code = societies.soc_code and p1.param_key = p.param_key and
p1.param_code= p.param_code) 
and p.param_key = 'MESSAGES';

---- END SETUP -------------------------------


---- ERROR PRODUCING STATEMENT  -------------------------------

select cast('cp' as varchar(2)) as alias,
soc_code, count(*) as rec_cnt,max(coalesce(last_upd_date, create_date)) as max_upd_date
>from component_params_v
where soc_code in ('ALP') group by soc_code, alias
union
select cast('pa' as varchar(2)) as alias,
soc_code, count(*) as rec_cnt,max(coalesce(last_upd_date, create_date)) as max_upd_date
>from params_v
where soc_code in ('ALP') group by soc_code, alias;

---- END ERROR PRODUCING STATEMENT  -------------------------------


+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+