Re: query returns incorrect results.

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); }