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