incorrect query result using complex structures (views?)

Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:

I cannot decide if this is a serious bug or not --- some queries from
complex views may give strange results. The next few days I will try to
find the point where the problem is but now I can only include the data
structure and the SELECT statements which don't give the correct result.

So ... um ... what do you consider incorrect about the results?

regards, tom lane

On Tue, 8 May 2001, Tom Lane wrote:

Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:

I cannot decide if this is a serious bug or not --- some queries from
complex views may give strange results. The next few days I will try to
find the point where the problem is but now I can only include the data
structure and the SELECT statements which don't give the correct result.

So ... um ... what do you consider incorrect about the results?

regards, tom lane

The SELECTs give something like this:

tir=> select az, (select cikk from szallitolevel_tetele_ervenyes where
cikk = c.az) from cikk c limit 20;
az|?column?
------+--------
100191|
100202|
100203|
100006|
100016|
100027|
100028|
100039|
100080|
100099|
100100|
100102|
100105|
100106|
100107|
100108|
100109|
100110|
100111|
100112|
(20 rows)

But cikk.az and szallitolevel_tetele_ervenyes.cikk should be the same, so
the correct output for this query would be like this:

tir=> select c.az, cikk from cikk c, szallitolevel_tetele_ervenyes s where
c.az=s.cikk limit 20;
az| cikk
------+------
100743|100743
100742|100742
101080|101080
101075|101075
101084|101084
100124|100124
100467|100467
101080|101080
101163|101163
100517|100517
101080|101080
101163|101163
100719|100719
100406|100406
101080|101080
100286|100286
100367|100367
100406|100406
101080|101080
100546|100546
(20 rows)

Thanks in advance. Zoltan

Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:

Thanks in advance. Zoltan

You're welcome ;-)

regards, tom lane

*** src/backend/executor/nodeAppend.c.orig	Thu Mar 22 01:16:12 2001
--- src/backend/executor/nodeAppend.c	Tue May  8 15:48:02 2001
***************
*** 8,14 ****
   *
   *
   * IDENTIFICATION
!  *	  $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/executor/nodeAppend.c,v 1.40 2001/03/22 06:16:12 momjian Exp $
   *
   *-------------------------------------------------------------------------
   */
--- 8,14 ----
   *
   *
   * IDENTIFICATION
!  *	  $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/executor/nodeAppend.c,v 1.40.2.1 2001/05/08 19:48:02 tgl Exp $
   *
   *-------------------------------------------------------------------------
   */
***************
*** 362,375 ****

for (i = 0; i < nplans; i++)
{
! Plan *rescanNode;

! 		appendstate->as_whichplan = i;
! 		rescanNode = (Plan *) nth(i, node->appendplans);
! 		if (rescanNode->chgParam == NULL)
  		{
  			exec_append_initialize_next(node);
! 			ExecReScan((Plan *) rescanNode, exprCtxt, (Plan *) node);
  		}
  	}
  	appendstate->as_whichplan = 0;
--- 362,386 ----

for (i = 0; i < nplans; i++)
{
! Plan *subnode;

! 		subnode = (Plan *) nth(i, node->appendplans);
! 		/*
! 		 * ExecReScan doesn't know about my subplans, so I have to do
! 		 * changed-parameter signaling myself.
! 		 */
! 		if (node->plan.chgParam != NULL)
! 			SetChangedParamList(subnode, node->plan.chgParam);
! 		/*
! 		 * if chgParam of subnode is not null then plan will be re-scanned by
! 		 * first ExecProcNode.
! 		 */
! 		if (subnode->chgParam == NULL)
  		{
+ 			/* make sure estate is correct for this subnode (needed??) */
+ 			appendstate->as_whichplan = i;
  			exec_append_initialize_next(node);
! 			ExecReScan(subnode, exprCtxt, (Plan *) node);
  		}
  	}
  	appendstate->as_whichplan = 0;
*** src/backend/executor/nodeSubqueryscan.c.orig	Thu Mar 22 01:16:13 2001
--- src/backend/executor/nodeSubqueryscan.c	Tue May  8 15:48:02 2001
***************
*** 12,18 ****
   *
   *
   * IDENTIFICATION
!  *	  $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/executor/nodeSubqueryscan.c,v 1.6 2001/03/22 06:16:13 momjian Exp $
   *
   *-------------------------------------------------------------------------
   */
--- 12,18 ----
   *
   *
   * IDENTIFICATION
!  *	  $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/executor/nodeSubqueryscan.c,v 1.6.2.1 2001/05/08 19:48:02 tgl Exp $
   *
   *-------------------------------------------------------------------------
   */
***************
*** 267,273 ****
  		return;
  	}

! ExecReScan(node->subplan, NULL, node->subplan);

  	subquerystate->csstate.css_ScanTupleSlot = NULL;
  }
--- 267,284 ----
  		return;
  	}

! /*
! * ExecReScan doesn't know about my subplan, so I have to do
! * changed-parameter signaling myself.
! */
! if (node->scan.plan.chgParam != NULL)
! SetChangedParamList(node->subplan, node->scan.plan.chgParam);
! /*
! * if chgParam of subnode is not null then plan will be re-scanned by
! * first ExecProcNode.
! */
! if (node->subplan->chgParam == NULL)
! ExecReScan(node->subplan, NULL, node->subplan);

subquerystate->csstate.css_ScanTupleSlot = NULL;
}

You're welcome ;-)

Marvellous, it works! How much time did it take for you to find what have
to be changed?

Thank you very much.

Regards, Zoltan