Обсуждение: BUG #10587: ERROR: variable not found in subplan target list

The following bug has been logged on the website:

Bug reference:      10587
Logged by:          Geoff Speicher
Email address:      geoff@sea-incorporated.com
PostgreSQL version: 9.2.8
Operating system:   FreeBSD
Description:

The following SQL has been verified to yield a planner error in 9.2.2 and
9.2.8 (maybe others), but the query succeeds in 9.3.4.

create table t1 (
  id int primary key,
  a1 boolean,
  a2 boolean
);
create table t2 (
  id int primary key,
  t1_id int,
  b1 boolean,
  b2 boolean,
  foreign key (t1_id) references t1(id)
);
create table t3 (
  id int primary key,
  t2_id int,
  c1 boolean,
  foreign key (t2_id) references t2(id)
);

insert into t1 values (1,true,true);
insert into t1 values (2,true,false);
insert into t1 values (3,false,false);
insert into t2 values (1,1,true,true);
insert into t2 values (2,2,true,false);
insert into t2 values (3,3,false,false);
insert into t3 values (1,1,true);
insert into t3 values (2,2,false);
insert into t3 values (3,3,true);

select t3.id
  from t3 as t3
  left join
    (select t2.*, (t2.b1 and t1.a3) AS b3
      from t2 as t2
      left join
        (select t1.*, (t1.id is not null) as a3
          from t1) as t1
        on t1.id=t2.t1_id
      ) as t2
    on t2.id=t3.t2_id
  where t3.id=1 and t2.b3
  ;

geoff@sea-incorporated.com writes:
> The following SQL has been verified to yield a planner error in 9.2.2 and
> 9.2.8 (maybe others), but the query succeeds in 9.3.4.

Thanks for the compact test case!  I believe this patch fixes it:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=187ae17300776f48b2bd9d0737923b1bf70f606e

            regards, tom lane

On Mon, Jun 9, 2014 at 9:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> geoff@sea-incorporated.com writes:
> > The following SQL has been verified to yield a planner error in 9.2.2 and
> > 9.2.8 (maybe others), but the query succeeds in 9.3.4.
>
> Thanks for the compact test case!  I believe this patch fixes it:
>
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=187ae17300776f48b2bd9d0737923b1bf70f606e
>
>                         regards, tom lane
>

Thanks Tom! Your patch fixes the test case that I submitted, and it also
fixes a more complicated query of a similar structure that was returning
incorrect results rather than throwing an error.

You and I met briefly in San Diego at OSCON 2002. Greetings from Scranton
and I hope you are well!

Geoff