Обсуждение: query returns incorrect results.

Hello,

I've run into a really strange problem that's taken a while for
me to track down, but I think I have enough information here
for people to verify what I'm saying and hopefully enough information
to point someone in the right direction for fixing.

My basic problem is that several queries are returning less results
than expected, more results than expected, or no results at all.  I've
been able to create a test case that causes the failure which
produces no results.  All of the failures involve this basic type of
query:

    select * from foo where
        exists (select * from bar1..barN where join1..joinN)

I have attached a test database with enough for me to reproduce a
failure all of the time.  I'm running RedHat-7.0/i686 and PG-7.0.2
I've also tested this on RedHat-6.2

To reproduce the failure:

    1) create a test db
    2) load database from attachment
    3) ---> VACUUM ANALYZE <----
    4) check plan; run query --> you get 0 rows
    5) drop index c_key
    6) check plan; run query --> you get 4 rows

You MUST VACUUM before running the query otherwise a different plan
will be choosen and the problem won't be observed.

QUERY that fails:
    select g.id from g where exists ( select * from a, b, c, c_c, j
    where a.v = 2 and a.id = g.id and substr(b.v,1,4) = '1990' and
        b.id = g.id and c.v = c_c.id and c_c.v = 1 and c.v = 2 and
        c.id = g.id and j.v = 2 and j.id = g.id);


This is the plan that is known to fail on my machine:

Seq Scan on g  (cost=0.00..22619.63 rows=2402 width=4)
  SubPlan
    ->  Nested Loop  (cost=0.00..9.40 rows=1 width=52)
          ->  Nested Loop  (cost=0.00..7.37 rows=1 width=44)
                ->  Index Scan using c_key on c  (cost=0.00..2.02 rows=1 width=8)
                ->  Materialize  (cost=5.35..5.35 rows=1 width=36)
                      ->  Nested Loop  (cost=0.00..5.35 rows=1 width=36)
                            ->  Nested Loop  (cost=0.00..3.78 rows=1 width=28)
                                  ->  Seq Scan on j  (cost=0.00..2.12 rows=1 width=12)
                                  ->  Seq Scan on b  (cost=0.00..1.65 rows=1 width=16)
                            ->  Seq Scan on a  (cost=0.00..1.55 rows=1 width=8)
          ->  Index Scan using c_c_key on c_c  (cost=0.00..2.02 rows=1 width=8)

Other plans not using Materialize seem to work okay.

Please contact me if I can help someone solve this problem or supply more
information.  I want to help out since I rely heavily on postgres!


--Brian Hirt

--
The world's most ambitious and comprehensive PC game database project.

                      http://www.mobygames.com

Brian Hirt <bhirt@mobygames.com> writes:
> I've run into a really strange problem that's taken a while for 
> me to track down, but I think I have enough information here
> for people to verify what I'm saying and hopefully enough information
> to point someone in the right direction for fixing.

Nasty.  Looks like the problem is that Materialize isn't paying
attention to chgParam (boo hiss), so when the subplan is re-executed
with successive g.id values, it keeps handing back the same rows
it computed on the first time through.  You only see a failure if
one of the subplans under the Materialize pays attention to g.id
(else the result *should* be the same each time), which is why the
failure comes and goes with different plans. 

This actually works correctly in current sources, because I had
rewritten nodeMaterial awhile back and put in the same ReScan
logic that I saw in other plan node types --- I just did that on
general principles, not thinking that it was a live bug fix.
So it hadn't occurred to me to try to back-patch that change,
but clearly we need to do something about it for 7.0.3.

I'll see what I can do about it...
        regards, tom lane

Try the attached patch against 7.0.2 --- not well tested but it
seems to work...
        regards, tom lane


*** src/backend/executor/nodeMaterial.c.orig    Wed Mar  1 23:06:39 2000
--- src/backend/executor/nodeMaterial.c    Thu Oct  5 21:28:47 2000
***************
*** 8,14 ****  *  *  * IDENTIFICATION
!  *      $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/executor/nodeMaterial.c,v 1.30 2000/03/02 04:06:39
tglExp $  *  *-------------------------------------------------------------------------  */
 
--- 8,14 ----  *  *  * IDENTIFICATION
!  *      $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/executor/nodeMaterial.c,v 1.30.2.2 2000/10/06
01:28:47tgl Exp $  *  *-------------------------------------------------------------------------  */
 
***************
*** 39,50 ****  *        calls to ExecMaterial return successive tuples from the temp  *        relation.  *
-  *        Initial State:
-  *
-  *        ExecMaterial assumes the temporary relation has been
-  *        created and opened by ExecInitMaterial during the prior
-  *        InitPlan() phase.
-  *  * ----------------------------------------------------------------  */ TupleTableSlot *                /* result
tuplefrom subplan */
 
--- 39,44 ----
***************
*** 78,96 ****      if (matstate->mat_Flag == false)     {         /* ----------------
!          *    set all relations to be scanned in the forward direction
!          *    while creating the temporary relation.          * ----------------          */
!         estate->es_direction = ForwardScanDirection;          /* ----------------          *     if we couldn't
createthe temp relation then          *     we print a warning and return NULL.          * ----------------
*/
-         tempRelation = matstate->mat_TempRelation;         if (tempRelation == NULL)         {
elog(DEBUG,"ExecMaterial: temp relation is NULL! aborting...");
 
--- 72,105 ----      if (matstate->mat_Flag == false)     {
+         TupleDesc    tupType;
+          /* ----------------
!          *    get type information needed for ExecCreatR          * ----------------          */
!         tupType = ExecGetScanType(&matstate->csstate);
! 
!         /* ----------------
!          *    ExecCreatR wants its second argument to be an object id of
!          *    a relation in the range table or a _NONAME_RELATION_ID
!          *    indicating that the relation is not in the range table.
!          *
!          *    In the second case ExecCreatR creates a temp relation.
!          *    (currently this is the only case we support -cim 10/16/89)
!          * ----------------
!          */
!         /* ----------------
!          *    create the temporary relation
!          * ----------------
!          */
!         tempRelation = ExecCreatR(tupType, _NONAME_RELATION_ID_);          /* ----------------          *     if we
couldn'tcreate the temp relation then          *     we print a warning and return NULL.          * ----------------
     */         if (tempRelation == NULL)         {             elog(DEBUG, "ExecMaterial: temp relation is NULL!
aborting...");
***************
*** 98,103 ****
--- 107,126 ----         }          /* ----------------
+          *    save the relation descriptor in the sortstate
+          * ----------------
+          */
+         matstate->mat_TempRelation = tempRelation;
+         matstate->csstate.css_currentRelation = NULL;
+ 
+         /* ----------------
+          *    set all relations to be scanned in the forward direction
+          *    while creating the temporary relation.
+          * ----------------
+          */
+         estate->es_direction = ForwardScanDirection;
+ 
+         /* ----------------          *     retrieve tuples from the subplan and          *     insert them in the
temporaryrelation          * ----------------
 
***************
*** 135,143 ****         matstate->csstate.css_currentRelation = currentRelation;
matstate->csstate.css_currentScanDesc= currentScanDesc; 
 
-         ExecAssignScanType(&matstate->csstate,
-                            RelationGetDescr(currentRelation));
-          /* ----------------          *    finally set the sorted flag to true          * ----------------
--- 158,163 ----
***************
*** 178,187 **** {     MaterialState *matstate;     Plan       *outerPlan;
-     TupleDesc    tupType;
-     Relation    tempDesc;
- 
-     /* int                        len; */      /* ----------------      *    assign the node's execution state
--- 198,203 ----
***************
*** 226,237 ****     ExecInitNode(outerPlan, estate, (Plan *) node);      /* ----------------
-      *    initialize matstate information
-      * ----------------
-      */
-     matstate->mat_Flag = false;
- 
-     /* ----------------      *    initialize tuple type.    no need to initialize projection      *    info because
thisnode doesn't do projections.      * ----------------
 
--- 242,247 ----
***************
*** 239,277 ****     ExecAssignScanTypeFromOuterPlan((Plan *) node, &matstate->csstate);
matstate->csstate.cstate.cs_ProjInfo= NULL; 
 
-     /* ----------------
-      *    get type information needed for ExecCreatR
-      * ----------------
-      */
-     tupType = ExecGetScanType(&matstate->csstate);
- 
-     /* ----------------
-      *    ExecCreatR wants its second argument to be an object id of
-      *    a relation in the range table or a _NONAME_RELATION_ID
-      *    indicating that the relation is not in the range table.
-      *
-      *    In the second case ExecCreatR creates a temp relation.
-      *    (currently this is the only case we support -cim 10/16/89)
-      * ----------------
-      */
-     /* ----------------
-      *    create the temporary relation
-      * ----------------
-      */
-     tempDesc = ExecCreatR(tupType, _NONAME_RELATION_ID_);
- 
-     /* ----------------
-      *    save the relation descriptor in the sortstate
-      * ----------------
-      */
-     matstate->mat_TempRelation = tempDesc;
-     matstate->csstate.css_currentRelation = NULL;
- 
-     /* ----------------
-      *    return relation oid of temporary relation in a list
-      *    (someday -- for now we return LispTrue... cim 10/12/89)
-      * ----------------
-      */     return TRUE; } 
--- 249,254 ----
***************
*** 343,386 **** {     MaterialState *matstate = node->matstate;      if (matstate->mat_Flag == false)         return;

!     matstate->csstate.css_currentScanDesc = ExecReScanR(matstate->csstate.css_currentRelation,
!                                    matstate->csstate.css_currentScanDesc,
!                                 node->plan.state->es_direction, 0, NULL);  } 
- #ifdef NOT_USED                    /* not used */ /* ----------------------------------------------------------------
*        ExecMaterialMarkPos  * ----------------------------------------------------------------  */
 
! List                            /* nothing of interest */
! ExecMaterialMarkPos(Material node) {
!     MaterialState matstate;     HeapScanDesc scan;      /* ----------------
!      *    if we haven't materialized yet, just return NIL.      * ----------------      */
!     matstate = get_matstate(node);
!     if (get_mat_Flag(matstate) == false)
!         return NIL;      /* ----------------
!      *    XXX access methods don't return positions yet so
!      *        for now we return NIL.    It's possible that
!      *        they will never return positions for all I know -cim 10/16/89      * ----------------      */
!     scan = get_css_currentScanDesc((CommonScanState) matstate);     heap_markpos(scan);
- 
-     return NIL; }  /* ----------------------------------------------------------------
--- 320,384 ---- {     MaterialState *matstate = node->matstate; 
+     /*
+      * If we haven't materialized yet, just return. If outerplan' chgParam is
+      * not NULL then it will be re-scanned by ExecProcNode, else - no
+      * reason to re-scan it at all.
+      */     if (matstate->mat_Flag == false)         return; 
!     /*
!      * If subnode is to be rescanned then we forget previous stored results;
!      * we have to re-read the subplan and re-store.
!      *
!      * Otherwise we can just rewind and rescan the stored output.
!      */
!     if (((Plan *) node)->lefttree->chgParam != NULL)
!     {
!         Relation    tempRelation = matstate->mat_TempRelation; 
+         matstate->csstate.css_currentRelation = NULL;
+         ExecCloseR((Plan *) node);
+         ExecClearTuple(matstate->csstate.css_ScanTupleSlot);
+         if (tempRelation != NULL)
+             heap_drop(tempRelation);
+         matstate->mat_TempRelation = NULL;
+         matstate->mat_Flag = false;
+     }
+     else
+     {
+         matstate->csstate.css_currentScanDesc =
+             ExecReScanR(matstate->csstate.css_currentRelation,
+                         matstate->csstate.css_currentScanDesc,
+                         node->plan.state->es_direction, 0, NULL);
+     } }  /* ----------------------------------------------------------------  *        ExecMaterialMarkPos  *
---------------------------------------------------------------- */
 
! void
! ExecMaterialMarkPos(Material *node) {
!     MaterialState *matstate;     HeapScanDesc scan;      /* ----------------
!      *    if we haven't materialized yet, just return.      * ----------------      */
!     matstate = node->matstate;
!     if (matstate->mat_Flag == false)
!         return;      /* ----------------
!      *    mark the scan position      * ----------------      */
!     scan = matstate->csstate.css_currentScanDesc;     heap_markpos(scan); }  /*
----------------------------------------------------------------
***************
*** 388,412 ****  * ----------------------------------------------------------------  */ void
! ExecMaterialRestrPos(Material node) {
!     MaterialState matstate;     HeapScanDesc scan;      /* ----------------      *    if we haven't materialized yet,
justreturn.      * ----------------      */
 
!     matstate = get_matstate(node);
!     if (get_mat_Flag(matstate) == false)         return;      /* ----------------      *    restore the scan to the
previouslymarked position      * ----------------      */
 
!     scan = get_css_currentScanDesc((CommonScanState) matstate);     heap_restrpos(scan); }
- 
- #endif
--- 386,408 ----  * ----------------------------------------------------------------  */ void
! ExecMaterialRestrPos(Material *node) {
!     MaterialState *matstate;     HeapScanDesc scan;      /* ----------------      *    if we haven't materialized
yet,just return.      * ----------------      */
 
!     matstate = node->matstate;
!     if (matstate->mat_Flag == false)         return;      /* ----------------      *    restore the scan to the
previouslymarked position      * ----------------      */
 
!     scan = matstate->csstate.css_currentScanDesc;     heap_restrpos(scan); }