Subquery with toplevel reference used to work in pg 8.4

From: Mark Murawski <markm-lists(at)intellasoft(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Subquery with toplevel reference used to work in pg 8.4
Date: 2012-03-24 04:04:56
Message-ID: 4F6D47E8.2040209@intellasoft.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jaime Casanova 2012-03-24 04:47:44 Re: Subquery with toplevel reference used to work in pg 8.4
Previous Message Alvaro Herrera 2012-03-23 15:32:22 Re: BUG #6510: A simple prompt is displayed using wrong charset