Hi,

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. A
lot of rows (contained by the database) should be downloaded from

http://www.math.u-szeged.hu/~kovzol/rows.pgsql.gz (25K, uncompressed 305K)

if you want to check this error.

Here are the definitions (rels-views.pgsql) and a RUNME.pgsql file (which
must be loaded with \i in psql), it contains the SELECTs.

I tried it with 7.1beta4 and 7.1.

There ARE workarounds. I am using SQL functions instead of subSELECTs now.

Regards,
Zoltan
Mon, 7 May 2001 18:37:18 +0200 (CEST)
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
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

Tue, 08 May 2001 10:58:27 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: incorrect query result using complex structures (views?) 
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


Tue, 8 May 2001 19:30:19 +0200 (CEST)
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
Re: incorrect query result using complex structures (views?)
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;
  }

Tue, 08 May 2001 15:51:54 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: incorrect query result using complex structures (views?) 
> 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


Wed, 9 May 2001 17:03:00 +0200 (CEST)
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
Re: incorrect query result using complex structures (views?)