Thread: join_references: variable not in subplan target lists

The cited error message appears when loading the attached file (a cut-down
version of a local development version of the information schema) and then
running

select * from problem_schema.element_types;

On the last seven lines of the file I've marked a part that, when removed,
makes the problem disappear, which might give a hint.  Besides that, I'm
clueless.

My local tree was last updated June 28.

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:
> On the last seven lines of the file I've marked a part that, when removed,
> makes the problem disappear, which might give a hint.  Besides that, I'm
> clueless.

Looks like I must have broken this as a side-effect of IN-subselect
optimizations --- 7.3 doesn't fail.  Will fix it, thanks for the test
case.
        regards, tom lane

Peter Eisentraut <peter_e@gmx.net> writes:
> The cited error message appears when loading the attached file (a cut-down
> version of a local development version of the information schema)

I've been able to reduce the problem to this test case:

drop view x1;

CREATE VIEW x1 AS   SELECT 1   FROM pg_type t, pg_proc p   WHERE        t.oid = p.prorettype AND        p.pronamespace
IN         (SELECT n.nspname           FROM pg_namespace n, pg_proc p2           WHERE n.oid = p2.pronamespace);
 

select * from x1;

While I now need to burrow into the IN-as-join code and find out where
it's dropping the ball, I don't think this need stop you from making
progress on the information schema.  The reason the problem is appearing
seems to be the implied cast that's getting introduced in the IN
comparison, because pronamespace (an OID) isn't directly comparable
to nspname (a NAME).  (They're both getting coerced to TEXT, which once
again points up my opinion that we are way too loose with implicit
coercions to TEXT, but never mind that right now.)  In short, the bug is
being triggered only because you're comparing the wrong pair of columns,
and so you'll need to change the query anyway.
        regards, tom lane

I said:
> While I now need to burrow into the IN-as-join code and find out where
> it's dropping the ball, I don't think this need stop you from making
> progress on the information schema.  The reason the problem is appearing
> seems to be the implied cast that's getting introduced in the IN
> comparison,

This theory turns out to be mistaken, or at least only marginally
relevant; it's possible to make the problem happen with no casts
at all.  I think the reason that adding or removing a cast made the
problem appear/disappear for me was just that it changed which plan
got selected (since the estimation routines are not very bright about
coping with joins on expressions).  The failure occurs when the planner
chooses to implement an IN by unique-ifying the result of the sub-select
and then using that as the outer side of a join.  If that join is not
the topmost join of the whole plan, we fail --- because the unique-ified
plan node only emits the variables that are supposed to be used in the
IN comparison, but the upper join generates a targetlist that includes
every variable used anywhere in the query.

If you hit the problem again, you might find that turning off
enable_hashagg lets you make progress.  A real solution seems to require
some honest accounting for which variables need to appear in join
targetlists and which don't; I'm off to look at that now ...
        regards, tom lane