variable not found in subplan target list?

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)

klint.

+---------------------------------------+-----------------+
: 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 : :
+---------------------------------------+-----------------+

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.

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 : :
+---------------------------------------+-----------------+

On Tue, 25 Nov 2003 11:39:03 +1100, Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au> wrote:
> 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"

Had a quick look thru the source code and saw that there are 2 very
similar messages and checked that it is definitely the plural "lists".

klint.

+---------------------------------------+-----------------+
: 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 : :
+---------------------------------------+-----------------+

Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au> writes:
>> 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.

Hoo, that was a fun one. Here's the patch.

regards, tom lane

*** src/backend/optimizer/plan/subselect.c.orig Tue Nov 25 16:00:54 2003
--- src/backend/optimizer/plan/subselect.c Tue Nov 25 18:25:32 2003
***************
*** 118,123 ****
--- 118,128 ----
* well, I believe that this sort of aliasing will cause no trouble.
* The correct field should get stored into the Param slot at
* execution in each part of the tree.
+ *
+ * We also need to demand a match on vartypmod. This does not matter
+ * for the Param itself, since those are not typmod-dependent, but it
+ * does matter when make_subplan() instantiates a modified copy of the
+ * Var for a subplan's args list.
*/
i = 0;
foreach(ppl, PlannerParamList)
***************
*** 129,135 ****

if (pvar->varno == var->varno &&
pvar->varattno == var->varattno &&
! pvar->vartype == var->vartype)
break;
}
i++;
--- 134,141 ----

if (pvar->varno == var->varno &&
pvar->varattno == var->varattno &&
! pvar->vartype == var->vartype &&
! pvar->vartypmod == var->vartypmod)
break;
}
i++;

On Tue, 25 Nov 2003 19:02:54 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au> writes:
> >> 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.
>
> Hoo, that was a fun one. Here's the patch.
>
> regards, tom lane
>

works on linux and cygwin.

thanks for the fix.

klint.

+---------------------------------------+-----------------+
: 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 : :
+---------------------------------------+-----------------+