Re: variable not found in subplan target list? + test case

On Sun, 23 Nov 2003 22:48:17 -0800 (PST), Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)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_name and 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(at)kgb(dot)une(dot)edu(dot)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 : :
+---------------------------------------+-----------------+