Обсуждение: 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