Thread: Re: [HACKERS] subselect bug (was Re: DBLink: interesting issue)

I fixed the subselect bug in v7.3b and now the following statement works
fine:
delete from tablea
where not exists (
    select remoteid from
        (select remoteid from dblink(' hostaddr=12.34.5.6
port=5433
            dbname=webspec user=user password=pass',
        'SELECT objectid  FROM tablea WHERE objectid = ' ||
tablea.objectid)
        AS dblink_rec(remoteid int8)) a1);

 var | val | objectid | objectversion
-----+-----+----------+---------------
 o   |   5 |        7 |             0
(1 row)

However, I am still getting an error when running the following UPDATE
statement:
UPDATE tablea
SET    objectid=a1.objectid,
    objectversion=a1.objectversion,
    val=a1.val,
    var=a1.var
FROM (
    SELECT *
    FROM dblink(' hostaddr=12.34.5.6 port=5433
            dbname=webspec user=user password=pass',
            'SELECT objectid, objectversion, val, var
            FROM tablea
            WHERE objectid=' || tablea.objectid  ||
            ' AND objectversion<' || tablea.objectversion)
            AS dblink_rec(objectid int8, objectversion int4,
val int4, var varchar)
    ) a1;
ERROR:  FROM function expression may not refer to other relations of
same query level

I think a similar bug needs to be fixed for FromExpr case.
Thanks.

Oleg

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, September 24, 2002 12:49 PM
To: Joe Conway
Cc: pgsql-hackers; Oleg Lebedev
Subject: Re: [HACKERS] subselect bug (was Re: [GENERAL] DBLink:
interesting issue)


Joe Conway <mail@joeconway.com> writes:
> replica=# create table foo(f1 int);
> CREATE TABLE
> replica=# SELECT * FROM foo t WHERE NOT EXISTS (SELECT remoteid FROM
> (SELECT f1 as remoteid FROM foo WHERE f1 = t.f1) AS t1); server closed

> the connection unexpectedly

Got it --- this bug has been there awhile :-(, ever since we had the
pull-up-subquery logic, which was in 7.1 IIRC.  The pullup code
neglected to adjust references to uplevel Vars.  Surprising that no one
reported this sooner.

The attached patch is against CVS tip.  It will not apply cleanly to 7.2
because pull_up_subqueries() has been modified since then, but if
anyone's desperate for a fix in 7.2 it could probably be adapted.

            regards, tom lane

*** src/backend/optimizer/plan/planner.c.orig    Wed Sep  4 17:30:30 2002
--- src/backend/optimizer/plan/planner.c    Tue Sep 24 14:02:54 2002
***************
*** 337,352 ****

              /*
               * Now make a modifiable copy of the subquery
that we can run
!              * OffsetVarNodes on.
               */
              subquery = copyObject(subquery);

              /*
!              * Adjust varnos in subquery so that we can
append its
               * rangetable to upper query's.
               */
              rtoffset = length(parse->rtable);
              OffsetVarNodes((Node *) subquery, rtoffset, 0);

              /*
               * Replace all of the top query's references to
the subquery's
--- 337,358 ----

              /*
               * Now make a modifiable copy of the subquery
that we can run
!              * OffsetVarNodes and IncrementVarSublevelsUp
on.
               */
              subquery = copyObject(subquery);

              /*
!              * Adjust level-0 varnos in subquery so that we
can append its
               * rangetable to upper query's.
               */
              rtoffset = length(parse->rtable);
              OffsetVarNodes((Node *) subquery, rtoffset, 0);
+
+             /*
+              * Upper-level vars in subquery are now one
level closer to their
+              * parent than before.
+              */
+             IncrementVarSublevelsUp((Node *) subquery, -1,
1);

              /*
               * Replace all of the top query's references to
the subquery's

"Oleg Lebedev" <oleg.lebedev@waterford.org> writes:
> However, I am still getting an error when running the following UPDATE
> statement:
> UPDATE tablea
> SET    objectid=a1.objectid,
>     objectversion=a1.objectversion,
>     val=a1.val,
>     var=a1.var
> FROM (
>     SELECT *
>     FROM dblink(' hostaddr=12.34.5.6 port=5433
>             dbname=webspec user=user password=pass',
>             'SELECT objectid, objectversion, val, var
>             FROM tablea
>             WHERE objectid=' || tablea.objectid  ||
>             ' AND objectversion<' || tablea.objectversion)
>             AS dblink_rec(objectid int8, objectversion int4,
> val int4, var varchar)
>     ) a1;
> ERROR:  FROM function expression may not refer to other relations of
> same query level

> I think a similar bug needs to be fixed for FromExpr case.

No, I think this is correct.  You have a FROM item (the a1 subselect)
that is trying to refer to the values of another FROM item --- the
target table of the UPDATE.  Consider the structurally-equivalent

    SELECT * FROM
        tablea,
        (SELECT * FROM dblink(... tablea.objectid ...) ...) a1;

This is not meaningful because each FROM expression has to be
independent.

You could probably make it work with something along the lines of

UPDATE tablea
SET    objectid=a1.objectid,
    objectversion=a1.objectversion,
    val=a1.val,
    var=a1.var
FROM (
    SELECT *
    FROM dblink(' hostaddr=12.34.5.6 port=5433
            dbname=webspec user=user password=pass',
            'SELECT objectid, objectversion, val, var
            FROM tablea')
            AS dblink_rec(objectid int8, objectversion int4,
                                      val int4, var varchar)
    ) a1
WHERE a1.objectid=tablea.objectid
AND a1.objectversion<tablea.objectversion;

ie, pull the join conditions out into the outer WHERE clause, where
they're supposed to be...

            regards, tom lane