The following bug has been logged online:

Bug reference: 2218
Logged by: Nicholas
Email address: hb (AT) pg (DOT) x256.org
PostgreSQL version: 8.1.2
Operating system: Gentoo Linux x86
Description: Variables selected in VIEWs under different names break
queries using those views
Details:

DROP VIEW meeting_versions; CREATE VIEW meeting_versions AS SELECT mi.id AS
iid, m.id, m.default_start, m.default_duration, m.default_location, m.team,
m.recurrence, m.discontinued, m.purpose, m.lead, m.queue, m.meetingtype,
m.monthly_what, m.monthly_ordinal, m."owner", m.version, m.original_id,
m.test_email_mode FROM meeting_instances mi JOIN meetings m ON m.id = ((
SELECT latestitz(ROW(m.id, m.version)) AS id FROM meetings m WHERE
m.original_id = mi.meetingid AND m.version < mi."start"));


SELECT cgm.id, g.name, mi.invitees FROM meeting_instances as mi JOIN
meeting_versions as m ON m.iid = '33' JOIN groups as g ON g.id = m.team LEFT
JOIN cachedgroupmembers as cgm ON cgm.groupid = m.team and not cgm.disabled
and cgm.memberid = '55' WHERE mi.id = '33';
ERROR: variable not found in subplan target list


DROP VIEW meeting_versions; CREATE VIEW meeting_versions AS SELECT 33 AS
iid, m.id, m.default_start, m.default_duration, m.default_location, m.team,
m.recurrence, m.discontinued, m.purpose, m.lead, m.queue, m.meetingtype,
m.monthly_what, m.monthly_ordinal, m."owner", m.version, m.original_id,
m.test_email_mode FROM meeting_instances mi JOIN meetings m ON m.id = ((
SELECT latestitz(ROW(m.id, m.version)) AS id FROM meetings m WHERE
m.original_id = mi.meetingid AND m.version < mi."start"));


SELECT cgm.id, g.name, mi.invitees FROM meeting_instances as mi JOIN
meeting_versions as m ON m.iid = '33' JOIN groups as g ON g.id = m.team LEFT
JOIN cachedgroupmembers as cgm ON cgm.groupid = m.team and not cgm.disabled
and cgm.memberid = '55' WHERE mi.id = '33';

id | name | invitees
--------+----------------------+----------
....
(31 rows)


Note that the significant difference between the two views is that the first
one selects "mi.id as iid" and that the view's join joins upon mi.id and
further that the LEFT JOIN's condition depends upon m.iid. If I stop
renaming mi.id to iid (as in the second example), OR if I change the LEFT
JOIN in the query to a JOIN, OR if I replace the view with a table, OR if I
remove the cgm.groupid = m.team condition from the LEFT JOIN, OR if I
replace my custom aggregate latestitz() with MAX(), the query succeeds.

I have no reason to suspect my custom aggregate is causing this error - it's
relatively simple and I have been using it heavily for months with no hint
of trouble. However, something about it being a custom aggregate does seem
to trigger this off. Maybe you can think of something I might be doing in
this function which can cause the error,

Unfortunately, I tried to come up with a test case which exposes this bug
but failed. Sorry. I also tried to come up with a minimal set of my data
which exposes it and also failed. I'm not sure what, specifically, about my
schema/data seems to expose this. However, it seems like everything which
adds complexity to this query is necessary for it to occur If you can
tell me what to do, I can probably build a debug version of postgres and run
GDB on it and get you some state information, or I can enable whatever debug
features might help shed some light on it.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"Nicholas" <hb (AT) x256 (DOT) com> writes:
Could we see a *complete* example please? I have no time or interest
to guess about the tables underlying this view.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"Nicholas" <hb (AT) x256 (DOT) com> writes:
This is a poor description, the problem actually seems to be related to
using sub-SELECTs in bitmap index scan conditions. Patch is here if you
need it before 8.1.3 comes out:

http://archives.postgresql.org/pgsql...1/msg00382.php

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match