Обсуждение: Bug #526: Three levels deeply nested SELECT goes wrong

Maarten Fokkinga (fokkinga@cs.utwente.nl) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Three levels deeply nested SELECT goes wrong

Long Description
A 3 levels deeply nested SELECT with "R(a)" in the top-most FROM part and "R.a=S.a AND XXX" in the middle WHERE part
maygive different results if in a SELECT within XXX the term "S.a" is replaced by "R.a". 

Since the innermost SELECT lies in the "scope" of the conjunct "R.a=S.a", it should not make any difference if "R.a" is
usedor "S.a". 

See the simple example code to reproduce the error.

Version: PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.96


Sample Code
create table R(a int);
create table S(a int, b int);
create table T(a int, b int);
insert into R values (1);
insert into R values (2);
insert into S values (2,20);
insert into S values (1,10);
insert into T values (2,20);
insert into T values (1,10);
-- the order of the rows in R, S, T is significant

-- first query:
select a from R where
exists (select b from S where
        S.a=R.a AND S.b in (select b from T where a=S.a));
-- gives two rows (rows "(1)" and "(2)")

-- second query:
select a from R where
exists (select b from S where
        S.a=R.a AND S.b in (select b from T where a=R.a));
-- gives one row ("(1)" only)

No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> Three levels deeply nested SELECT goes wrong

Seems to still behave the same in current sources :-(

Thanks for the report!

            regards, tom lane

pgsql-bugs@postgresql.org writes:
> A 3 levels deeply nested SELECT with "R(a)" in the top-most FROM part
> and "R.a=S.a AND XXX" in the middle WHERE part may give different
> results if in a SELECT within XXX the term "S.a" is replaced by "R.a".

> Version: PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.96

I have committed a fix for this problem into current sources
(7.2-to-be).  The fix would not apply cleanly in 7.0.*, but if you
don't care to run CVS-tip code you could update to 7.1.3 and apply
the attached patch to it.

            regards, tom lane


*** src/backend/optimizer/plan/subselect.c.orig    Wed Mar 21 22:59:37 2001
--- src/backend/optimizer/plan/subselect.c    Fri Nov 30 14:11:52 2001
***************
*** 324,329 ****
--- 324,335 ----
           * is anything more complicated than a plain sequential scan, and
           * we do it even for seqscan if the qual appears selective enough
           * to eliminate many tuples.
+          *
+          * XXX It's pretty ugly to be inserting a MATERIAL node at this
+          * point.  Since subquery_planner has already run SS_finalize_plan
+          * on the subplan tree, we have to kluge up parameter lists for
+          * the MATERIAL node.  Possibly this could be fixed by postponing
+          * SS_finalize_plan processing until setrefs.c is run.
           */
          if (node->parParam == NIL)
          {
***************
*** 362,369 ****
              }
              if (use_material)
              {
!                 plan = (Plan *) make_material(plan->targetlist, plan);
!                 node->plan = plan;
              }
          }

--- 368,380 ----
              }
              if (use_material)
              {
!                 Plan   *matplan;
!
!                 matplan = (Plan *) make_material(plan->targetlist, plan);
!                 /* kluge --- see comments above */
!                 matplan->extParam = listCopy(plan->extParam);
!                 matplan->locParam = listCopy(plan->locParam);
!                 node->plan = plan = matplan;
              }
          }