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 tgl Exp $
*
*-------------------------------------------------------------------------
*/
--- 8,14 ----
*
*
* IDENTIFICATION
! * $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/executor/nodeMaterial.c,v 1.30.2.2 2000/10/06 01:28:47 tgl 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 tuple from 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 create the 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't create 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 temporary relation
* ----------------
***************
*** 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 this node 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, just return.
* ----------------
*/
! matstate = get_matstate(node);
! if (get_mat_Flag(matstate) == false)
return;

/* ----------------
* restore the scan to the previously marked 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 previously marked position
* ----------------
*/
! scan = matstate->csstate.css_currentScanDesc;
heap_restrpos(scan);
}