From ico@alphasoft-bg.com Tue Dec  3 14:51:47 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 0B0AA475F63
	for <pgsql-sql@postgresql.org>; Tue,  3 Dec 2002 09:51:47 -0500 (EST)
Received: from alpha.alphasoft-bg.com (intbg-alfasoft.internet-bg.net
	[212.124.67.146])
	by postgresql.org (Postfix) with ESMTP id 3F474475E77
	for <pgsql-sql@postgresql.org>; Tue,  3 Dec 2002 09:51:42 -0500 (EST)
Received: from ico (pool07h-cgate.del.bg [62.176.85.134])
	by alpha.alphasoft-bg.com (8.12.3/8.12.1) with SMTP id gB3FG4Ns010905
	for <pgsql-sql@postgresql.org>; Tue, 3 Dec 2002 17:16:13 +0200
Message-ID: <000a01c29adb$066fc9e0$0a0546c0@alpha.bg>
From: "Alphasoft" <ico@alphasoft-bg.com>
To: <pgsql-sql@postgresql.org>
Subject: problem with view in 7.3 
Date: Tue, 3 Dec 2002 16:48:07 +0200
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0007_01C29AEB.C14C7280"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
X-Virus-Scanned: by amavisd-milter (http://amavis.org/)
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200212/39
X-Sequence-Number: 10627

This is a multi-part message in MIME format.

------=_NextPart_000_0007_01C29AEB.C14C7280
Content-Type: text/plain;
	charset="koi8-r"
Content-Transfer-Encoding: quoted-printable

Hi , I am Hristo Neshev From Bulgaria=20

In Version 7.2 this statement works :

create view v_impr_check
(
ead_no,
ead_date,=20
e_no,
e_date,
sid,
number,=20
s_order,
cdt,
cdt_description,=20
original_qty,
used_qty,
waste_qty,
rest_qty,
original_used_qty
)
AS
select distinct md_custom_number,=20
md_custom_date,=20
md_number ,
md_date ,
sni_sid ,
sni_number ,=20
snig_ord_number_in,
cdt_part_number,=20
cdt_description,=20
snig_quantity,
(select sum(impo_quantity) from impr_out where impo_store_note_in =3D snig_=
store_note_in and impo_ord_number_in =3D snig_ord_number_in),
(select sum(impow_quantity) from impr_out_waste where impow_store_note_in=
=3D sni_sid and impow_ord_number_in =3D snig_ord_number_in),
(select sum(iwnog_quantity) from impr_waste_note_out_goods where iwnog_stor=
e_note_in =3D sni_sid and iwnog_ord_number_in =3D snig_ord_number_in and iw=
nog_impr_note_out not in (select impow_iwnog_impr_note_out from impr_out_wa=
ste where impow_iwnog_impr_note_out is not null) ),
snig_used_quantity
from=20
store_note_in join md_header on md_sni_sid =3D sni_sid
join store_note_in_goods join commodity on (snig_commodity =3D cdt_sid)=20
on sni_sid =3D snig_store_note_in=20
Where
sni_type_doc =3D 303;

In 7.3 when i am trying to recreate the view I received the folowing error :
find_expr_references_walker: bogus varno 8=20
The problem is in subselect statements

------=_NextPart_000_0007_01C29AEB.C14C7280
Content-Type: text/html;
	charset="koi8-r"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dkoi8-r">
<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi , I am Hristo Neshev From Bulgaria </FO=
NT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>In Version 7.2 this statement works :</FON=
T></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>create view=20
v_impr_check<BR>(<BR>ead_no,<BR>ead_date,=20
<BR>e_no,<BR>e_date,<BR>sid,<BR>number, <BR>s_order,<BR>cdt,<BR>cdt_descrip=
tion,=20
<BR>original_qty,<BR>used_qty,<BR>waste_qty,<BR>rest_qty,<BR>original_used_=
qty<BR>)<BR>AS<BR>select=20
distinct md_custom_number, <BR>md_custom_date, <BR>md_number ,<BR>md_date=
=20
,<BR>sni_sid ,<BR>sni_number , <BR>snig_ord_number_in,<BR>cdt_part_number,=
=20
<BR>cdt_description, <BR>snig_quantity,<BR>(select sum(impo_quantity) from=
=20
impr_out where impo_store_note_in =3D snig_store_note_in and impo_ord_numbe=
r_in =3D=20
snig_ord_number_in),<BR>(select sum(impow_quantity) from impr_out_waste whe=
re=20
impow_store_note_in=3D sni_sid and impow_ord_number_in =3D=20
snig_ord_number_in),<BR>(select sum(iwnog_quantity) from=20
impr_waste_note_out_goods where iwnog_store_note_in =3D sni_sid and=20
iwnog_ord_number_in =3D snig_ord_number_in and iwnog_impr_note_out not in (=
select=20
impow_iwnog_impr_note_out from impr_out_waste where impow_iwnog_impr_note_o=
ut is=20
not null) ),<BR>snig_used_quantity<BR>from <BR>store_note_in join md_header=
 on=20
md_sni_sid =3D sni_sid<BR>join store_note_in_goods join commodity on=20
(snig_commodity =3D cdt_sid) <BR>on sni_sid =3D snig_store_note_in=20
<BR>Where<BR>sni_type_doc =3D 303;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><STRONG>In 7.3 when i am trying to recreat=
e the=20
view I received the folowing error :</STRONG></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><STRONG>find_expr_references_walker: bogus=
 varno 8=20
</STRONG></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>The problem is in subselect=20
statements</FONT></DIV></BODY></HTML>

------=_NextPart_000_0007_01C29AEB.C14C7280--




From ico@alphasoft-bg.com Tue Dec  3 15:08:29 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id B6FBA47619E
	for <pgsql-sql@postgresql.org>; Tue,  3 Dec 2002 10:08:29 -0500 (EST)
Received: from alpha.alphasoft-bg.com (intbg-alfasoft.internet-bg.net
	[212.124.67.146])
	by postgresql.org (Postfix) with ESMTP id 7F7A3476404
	for <pgsql-sql@postgresql.org>; Tue,  3 Dec 2002 10:08:05 -0500 (EST)
Received: from ico (pool07h-cgate.del.bg [62.176.85.134])
	by alpha.alphasoft-bg.com (8.12.3/8.12.1) with SMTP id gB3FWaNs010982
	for <pgsql-sql@postgresql.org>; Tue, 3 Dec 2002 17:32:37 +0200
Message-ID: <002f01c29add$53ebafc0$0a0546c0@alpha.bg>
From: "Alphasoft" <ico@alphasoft-bg.com>
To: <pgsql-sql@postgresql.org>
Subject: problem with view in 7.3 
Date: Tue, 3 Dec 2002 17:04:39 +0200
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0028_01C29AEE.10B6BAE0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
X-Virus-Scanned: by amavisd-milter (http://amavis.org/)
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200212/31
X-Sequence-Number: 10619

This is a multi-part message in MIME format.

------=_NextPart_000_0028_01C29AEE.10B6BAE0
Content-Type: text/plain;
	charset="koi8-r"
Content-Transfer-Encoding: quoted-printable

Hi , I am Hristo Neshev From Bulgaria=20

In Version 7.2 this statement works :

create view v_impr_check
(
ead_no,
ead_date,=20
e_no,
e_date,
sid,
number,=20
s_order,
cdt,
cdt_description,=20
original_qty,
used_qty,
waste_qty,
rest_qty,
original_used_qty
)
AS
select distinct md_custom_number,=20
md_custom_date,=20
md_number ,
md_date ,
sni_sid ,
sni_number ,=20
snig_ord_number_in,
cdt_part_number,=20
cdt_description,=20
snig_quantity,
(select sum(impo_quantity) from impr_out where impo_store_note_in =3D snig_=
store_note_in and impo_ord_number_in =3D snig_ord_number_in),
(select sum(impow_quantity) from impr_out_waste where impow_store_note_in=
=3D sni_sid and impow_ord_number_in =3D snig_ord_number_in),
(select sum(iwnog_quantity) from impr_waste_note_out_goods where iwnog_stor=
e_note_in =3D sni_sid and iwnog_ord_number_in =3D snig_ord_number_in and iw=
nog_impr_note_out not in (select impow_iwnog_impr_note_out from impr_out_wa=
ste where impow_iwnog_impr_note_out is not null) ),
snig_used_quantity
from=20
store_note_in join md_header on md_sni_sid =3D sni_sid
join store_note_in_goods join commodity on (snig_commodity =3D cdt_sid)=20
on sni_sid =3D snig_store_note_in=20
Where
sni_type_doc =3D 303;

In 7.3 when i am trying to recreate the view I received the folowing error :
find_expr_references_walker: bogus varno 8=20
The problem is in subselect statements

------=_NextPart_000_0028_01C29AEE.10B6BAE0
Content-Type: text/html;
	charset="koi8-r"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dkoi8-r">
<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>
<DIV><FONT face=3DArial size=3D2>Hi , I am Hristo Neshev From Bulgaria </FO=
NT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>In Version 7.2 this statement works :</FON=
T></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>create view=20
v_impr_check<BR>(<BR>ead_no,<BR>ead_date,=20
<BR>e_no,<BR>e_date,<BR>sid,<BR>number, <BR>s_order,<BR>cdt,<BR>cdt_descrip=
tion,=20
<BR>original_qty,<BR>used_qty,<BR>waste_qty,<BR>rest_qty,<BR>original_used_=
qty<BR>)<BR>AS<BR>select=20
distinct md_custom_number, <BR>md_custom_date, <BR>md_number ,<BR>md_date=
=20
,<BR>sni_sid ,<BR>sni_number , <BR>snig_ord_number_in,<BR>cdt_part_number,=
=20
<BR>cdt_description, <BR>snig_quantity,<BR>(select sum(impo_quantity) from=
=20
impr_out where impo_store_note_in =3D snig_store_note_in and impo_ord_numbe=
r_in =3D=20
snig_ord_number_in),<BR>(select sum(impow_quantity) from impr_out_waste whe=
re=20
impow_store_note_in=3D sni_sid and impow_ord_number_in =3D=20
snig_ord_number_in),<BR>(select sum(iwnog_quantity) from=20
impr_waste_note_out_goods where iwnog_store_note_in =3D sni_sid and=20
iwnog_ord_number_in =3D snig_ord_number_in and iwnog_impr_note_out not in (=
select=20
impow_iwnog_impr_note_out from impr_out_waste where impow_iwnog_impr_note_o=
ut is=20
not null) ),<BR>snig_used_quantity<BR>from <BR>store_note_in join md_header=
 on=20
md_sni_sid =3D sni_sid<BR>join store_note_in_goods join commodity on=20
(snig_commodity =3D cdt_sid) <BR>on sni_sid =3D snig_store_note_in=20
<BR>Where<BR>sni_type_doc =3D 303;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><STRONG>In 7.3 when i am trying to recreat=
e the=20
view I received the folowing error :</STRONG></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><STRONG>find_expr_references_walker: bogus=
 varno 8=20
</STRONG></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>The problem is in subselect=20
statements</FONT></DIV></FONT></DIV></BODY></HTML>

------=_NextPart_000_0028_01C29AEE.10B6BAE0--




From tgl@sss.pgh.pa.us Tue Dec  3 15:19:20 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 0E0A3475FDD
	for <pgsql-sql@postgresql.org>; Tue,  3 Dec 2002 10:19:20 -0500 (EST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP id 9B700475F63
	for <pgsql-sql@postgresql.org>; Tue,  3 Dec 2002 10:19:18 -0500 (EST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id gB3FIgpd023563;
	Tue, 3 Dec 2002 10:18:42 -0500 (EST)
To: "Alphasoft" <ico@alphasoft-bg.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: problem with view in 7.3 
In-reply-to: <002f01c29add$53ebafc0$0a0546c0@alpha.bg> 
References: <002f01c29add$53ebafc0$0a0546c0@alpha.bg>
Comments: In-reply-to "Alphasoft" <ico@alphasoft-bg.com>
	message dated "Tue, 03 Dec 2002 17:04:39 +0200"
Date: Tue, 03 Dec 2002 10:18:42 -0500
Message-ID: <23562.1038928722@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200212/32
X-Sequence-Number: 10620

"Alphasoft" <ico@alphasoft-bg.com> writes:
> In 7.3 when i am trying to recreate the view I received the folowing error :
> find_expr_references_walker: bogus varno 8=20

This is a bug, but I can't fix it unless you give me a complete test
case.  I don't have time to try to reverse-engineer your schema...

"pg_dump -s" is a quick way to make a script that just creates your
tables without any data.

			regards, tom lane



From tgl@sss.pgh.pa.us Wed Dec  4 20:11:15 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 582D2476340
	for <pgsql-sql@postgresql.org>; Wed,  4 Dec 2002 15:11:15 -0500 (EST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP id D3F5B476B42
	for <pgsql-sql@postgresql.org>; Wed,  4 Dec 2002 15:02:51 -0500 (EST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id gB4K2ipd017790;
	Wed, 4 Dec 2002 15:02:44 -0500 (EST)
To: "Alphasoft" <ico@alphasoft-bg.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: problem with view in 7.3 
In-reply-to: <000a01c29adb$066fc9e0$0a0546c0@alpha.bg> 
References: <000a01c29adb$066fc9e0$0a0546c0@alpha.bg>
Comments: In-reply-to "Alphasoft" <ico@alphasoft-bg.com>
	message dated "Tue, 03 Dec 2002 16:48:07 +0200"
Date: Wed, 04 Dec 2002 15:02:43 -0500
Message-ID: <17789.1039032163@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200212/55
X-Sequence-Number: 10643

"Alphasoft" <ico@alphasoft-bg.com> writes:
> In 7.3 when i am trying to recreate the view I received the folowing error :
> find_expr_references_walker: bogus varno 8

Found it --- thanks for the test case.  The patch is attached, if you
want to apply it locally.

			regards, tom lane


*** src/backend/catalog/dependency.c.orig	Sat Sep 21 20:37:09 2002
--- src/backend/catalog/dependency.c	Wed Dec  4 14:45:58 2002
***************
*** 789,794 ****
--- 789,799 ----
  		else if (rte->rtekind == RTE_JOIN)
  		{
  			/* Scan join output column to add references to join inputs */
+ 			List   *save_rtables;
+ 
+ 			/* We must make the context appropriate for join's level */
+ 			save_rtables = context->rtables;
+ 			context->rtables = rtables;
  			if (var->varattno <= 0 ||
  				var->varattno > length(rte->joinaliasvars))
  				elog(ERROR, "find_expr_references_walker: bogus varattno %d",
***************
*** 796,801 ****
--- 801,807 ----
  			find_expr_references_walker((Node *) nth(var->varattno - 1,
  													 rte->joinaliasvars),
  										context);
+ 			context->rtables = save_rtables;
  		}
  		return false;
  	}