Обсуждение: Subquery with toplevel reference used to work in pg 8.4
I agree the query is a little odd, but I like backwards compatibility! Postgres 8.4.1 ---------------------- CREATE VIEW v_members AS SELECT 1 as member_id, 100 as tenant_id, 3732 as conference_id, 200 as uid FROM (select 1 as uid_user, 2 as uid_contact) as m; SELECT u.tenant_id, u.uid FROM (select 100 as tenant_id, 200 as uid) u LEFT JOIN v_members m ON (m.uid = u.uid AND m.conference_id = 3732) WHERE ( SELECT 1 FROM (select 3732 as conference_id) c WHERE (c.conference_id = 3732) AND (m.uid IS NOT NULL) ) = 1; tenant_id | uid -----------+----- 100 | 200 (1 row) postgres 9.1.3 -------------------------- CREATE VIEW v_members AS SELECT 1 as member_id, 100 as tenant_id, 3732 as conference_id, 200 as uid FROM (select 1 as uid_user, 2 as uid_contact) as m; SELECT u.tenant_id, u.uid FROM (select 100 as tenant_id, 200 as uid) u LEFT JOIN v_members m ON (m.uid = u.uid AND m.conference_id = 3732) WHERE ( SELECT 1 FROM (select 3732 as conference_id) c WHERE (c.conference_id = 3732) AND (m.uid IS NOT NULL) ) = 1; ERROR: Upper-level PlaceHolderVar found where not expected
On Fri, Mar 23, 2012 at 11:04 PM, Mark Murawski <markm-lists@intellasoft.net> wrote: > > ERROR: =A0Upper-level PlaceHolderVar found where not expected > This is part of commit c1d9579dd8bf3c921ca6bc2b62c40da6d25372e5 which as stated in the commit log: """ tightened the error checking in this area a bit: if it was ever valid to see an uplevel Var, Aggref, or PlaceHolderVar here, that was a long time ago, so complain instead of ignoring them. """ the query seems useless but valid to me... just removing this check and the assert in find_placeholder_info():placeholder.c seems to make this query behave normally again. --=20 Jaime Casanova=A0 =A0 =A0 =A0=A0 www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitaci=F3n
Mark Murawski <markm-lists@intellasoft.net> writes: > I agree the query is a little odd, but I like backwards compatibility! AFAICT, 8.4 is broken too --- did you try any cases where the WHERE-condition should filter rows? I created this similar test case using the regression database: select * from int8_tbl t1 left join (select q1 as x, 42 as y from int8_tbl t2) ss on t1.q2 = ss.x where 1 = (select 1 from int8_tbl t3 where ss.y is not null limit 1); The raw output without any WHERE clause is q1 | q2 | x | y ------------------+-------------------+------------------+---- 123 | 456 | | 123 | 4567890123456789 | 4567890123456789 | 42 123 | 4567890123456789 | 4567890123456789 | 42 123 | 4567890123456789 | 4567890123456789 | 42 4567890123456789 | 123 | 123 | 42 4567890123456789 | 123 | 123 | 42 4567890123456789 | 4567890123456789 | 4567890123456789 | 42 4567890123456789 | 4567890123456789 | 4567890123456789 | 42 4567890123456789 | 4567890123456789 | 4567890123456789 | 42 4567890123456789 | -4567890123456789 | | (10 rows) The WHERE clause ought to be effectively just the same as "where ss.y is not null", ie it should eliminate the two null-extended rows. And in 8.3 and before, that's what you get: q1 | q2 | x | y ------------------+------------------+------------------+---- 123 | 4567890123456789 | 4567890123456789 | 42 123 | 4567890123456789 | 4567890123456789 | 42 123 | 4567890123456789 | 4567890123456789 | 42 4567890123456789 | 123 | 123 | 42 4567890123456789 | 123 | 123 | 42 4567890123456789 | 4567890123456789 | 4567890123456789 | 42 4567890123456789 | 4567890123456789 | 4567890123456789 | 42 4567890123456789 | 4567890123456789 | 4567890123456789 | 42 (8 rows) but 8.4 and 9.0 produce all 10 rows, ie no filtering happens. And 9.1 and HEAD produce ERROR: Upper-level PlaceHolderVar found where not expected After investigating, I believe the problem is that SS_replace_correlation_vars needs to replace outer PlaceHolderVars just as if they were outer Vars. What is getting pushed into the subquery is a PlaceHolderVar wrapping the constant 42, and if that's left alone then the subquery WHERE clause ends up as just "42 is not null", which is not what we need. That has to be converted into a Param referencing a value from the outer query. 9.1 and HEAD are correctly bleating about the fact that this outer-level PlaceHolderVar shouldn't be there by the time the complaining code runs. Kinda surprising that this bug escaped detection this long ... regards, tom lane