Thread: variable not found in subplan target list?

variable not found in subplan target list?

From
Klint Gore
Date:
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@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             :                 :
+---------------------------------------+-----------------+

Re: variable not found in subplan target list?

From
Stephan Szabo
Date:
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.

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

From
Klint Gore
Date:
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             :                 :
+---------------------------------------+-----------------+

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

From
Klint Gore
Date:
On Tue, 25 Nov 2003 11:39:03 +1100, Klint Gore <kg@kgb.une.edu.au> wrote:
> 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"

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

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

From
Tom Lane
Date:
Klint Gore <kg@kgb.une.edu.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++;

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

From
Klint Gore
Date:
On Tue, 25 Nov 2003 19:02:54 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Klint Gore <kg@kgb.une.edu.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@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             :                 :
+---------------------------------------+-----------------+