Re: BUG #3277: error occurs between different versions

The following bug has been logged online:

Bug reference: 3277
Logged by: Nilay Ceter
Email address: nilayceter(at)gmail(dot)com
PostgreSQL version: 8.2.3
Operating system: windows
Description: error occurs between different versions
Details:

We are developing a network based application, using postgre sql.

Earlier version has been developed using Postgre SQL 8.0 and there was no
problem.

But we use Postgre SQL 8.2 in new version and we are experiencing some
problems about our sql commands.

The command line:

select seri_no, ad as baslik, kull_ref as Kullanc_Referansi, sorumlu
from onay,dokuman where p_id='yonetici' and onay.dokuman_id=dokuman.seri_no
and onay_durum.seri_no=seri_no and onay_durum.p_id='yonetici' and
(dokuman.durum=1 or dokuman.durum=2 or dokuman.durum=6 or dokuman.durum=7)
and onay_durum.durum=0 and seri_no>0

was working properly in Postgre SQL 8.0 but in version Postgre SQL 8.2 the
second "and" gives an error.
And it is :

" Project dym.exe raised exception class EPSQLDatabaseError with message
'PostgreSQL Error Code : (1)
ERROR: missing FROM-clause entry for table "onay_durum" at character 152'
.Process stopped.Use Step or Run to continue. "

would you please help us?

Best Regards

Nilay Ceter wrote:
> The command line:
>
> select seri_no, ad as baslik, kull_ref as Kullanıcı_Referansi, sorumlu
> from onay,dokuman where p_id='yonetici' and onay.dokuman_id=dokuman.seri_no
> and onay_durum.seri_no=seri_no and onay_durum.p_id='yonetici' and
> (dokuman.durum=1 or dokuman.durum=2 or dokuman.durum=6 or dokuman.durum=7)
> and onay_durum.durum=0 and seri_no>0
>
>
> was working properly in Postgre SQL 8.0 but in version Postgre SQL 8.2 the
> second "and" gives an error.
> And it is :
>
> " Project dym.exe raised exception class EPSQLDatabaseError with message
> 'PostgreSQL Error Code : (1)
> ERROR: missing FROM-clause entry for table "onay_durum" at character 152'
> .Process stopped.Use Step or Run to continue. "

I don't understand how that query could have worked on PostgreSQL 8.0
either. There's no table or alias with name "onay_durum" in the FROM clause.

Are you sure this is the exactly same query you tried on PostgreSQL 8.0?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Mon, May 14, 2007 at 09:44:05AM +0100, Heikki Linnakangas wrote:
> Nilay Ceter wrote:
> >The command line:
> >
> >select seri_no, ad as baslik, kull_ref as Kullanıcı_Referansi,
> >sorumlu
> >from onay,dokuman where p_id='yonetici' and onay.dokuman_id=dokuman.seri_no
> >and onay_durum.seri_no=seri_no and onay_durum.p_id='yonetici' and
> >(dokuman.durum=1 or dokuman.durum=2 or dokuman.durum=6 or dokuman.durum=7)
> >and onay_durum.durum=0 and seri_no>0
> >
> >
> >was working properly in Postgre SQL 8.0 but in version Postgre SQL 8.2 the
> >second "and" gives an error.
> >And it is :
> >
> >" Project dym.exe raised exception class EPSQLDatabaseError with message
> >'PostgreSQL Error Code : (1)
> >ERROR: missing FROM-clause entry for table "onay_durum" at character 152'
> >.Process stopped.Use Step or Run to continue. "
>
> I don't understand how that query could have worked on PostgreSQL 8.0
> either. There's no table or alias with name "onay_durum" in the FROM clause.
>
> Are you sure this is the exactly same query you tried on PostgreSQL 8.0?

Seems to be dependant on the config parameter add_missing_from. The default
changed between 8.0 and 8.1 from on to off.

Nilay, you can try setting add_missing_from=on in postgresql.conf and
reload the service to fix this. Or better yet, fix your query to reference
all tables in the from clause.

//Magnus

On Mon, 14 May 2007 09:44:05 +0100, Heikki Linnakangas <heikki(at)enterprisedb(dot)com> wrote:
> Nilay Ceter wrote:
> > The command line:
> >
> > select seri_no, ad as baslik, kull_ref as Kullanıcı_Referansi, sorumlu
> > from onay,dokuman where p_id='yonetici' and onay.dokuman_id=dokuman.seri_no
> > and onay_durum.seri_no=seri_no and onay_durum.p_id='yonetici' and
> > (dokuman.durum=1 or dokuman.durum=2 or dokuman.durum=6 or dokuman.durum=7)
> > and onay_durum.durum=0 and seri_no>0
> >
> >
> > was working properly in Postgre SQL 8.0 but in version Postgre SQL 8.2 the
> > second "and" gives an error.
> > And it is :
> >
> > " Project dym.exe raised exception class EPSQLDatabaseError with message
> > 'PostgreSQL Error Code : (1)
> > ERROR: missing FROM-clause entry for table "onay_durum" at character 152'
> > .Process stopped.Use Step or Run to continue. "
>
> I don't understand how that query could have worked on PostgreSQL 8.0
> either. There's no table or alias with name "onay_durum" in the FROM clause.
>
> Are you sure this is the exactly same query you tried on PostgreSQL 8.0?

Didn't the default in postgresql.conf for add_missing_from change in 8.1?

klint.

+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg(at)kgb(dot)une(dot)edu(dot)au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+

nilay çeter wrote:
> Yes,it is the same query, and had worked on PostgreSQL8.0 ,but although I
> had already added "onay_durum" to from clause and it did not work on 8.2,I
> have changed the 'add_missing_from = off ' and made it on in conf. file but
> it didn't work too.
> I have no idea about what to do:(

Please keep the list CC'd so others can help.

There must be something wrong with the way you added onay_durum to the
FROM-clause, but it's impossible to say what without seeing the modified
query and error message. Just check the query again carefully.

I presume you meant that you changed 'add_missing_from = on', not off,
in the config file.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

I'm suprised it worked in 8.0. You should have got column reference is
ambiguous errors on seri_no since it appears in both dokuman and
onay_durum and there are 3 places where you don't specify which one you
want. (Someone who knows more than I do might comment on what happens
to unqualfied field names when the add missing from brings in another
field with the same name?)

try fully qualifying where everything comes from.

select
??.seri_no,
???.ad as baslik,
????.kull_ref as Kullanıcı_Referansi,
?????.sorumlu

>from onay,
dokuman,
onay_durum
where onay_durum.p_id='yonetici'
and onay.dokuman_id=dokuman.seri_no
and onay_durum.seri_no=??????.seri_no
and onay_durum.p_id='yonetici'
and (
dokuman.durum=1
or dokuman.durum=2
or dokuman.durum=6
or dokuman.durum=7)
and onay_durum.durum=0
and ???????.seri_no>0

klint.

On Mon, 21 May 2007 13:15:01 +0300, "nilay çeter" <nilayceter(at)gmail(dot)com>
wrote:
> Yes,it is the same query, and had worked on PostgreSQL8.0 ,but although I
> had already added "onay_durum" to from clause and it did not work on 8.2,I
> have changed the 'add_missing_from = off ' to on in conf. file but
> it didn't work too.
> I have no idea about what to do:(
>
> 2007/5/14, Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au>:
> >
> > On Mon, 14 May 2007 09:44:05 +0100, Heikki Linnakangas <
> > heikki(at)enterprisedb(dot)com> wrote:
> > > Nilay Ceter wrote:
> > > > The command line:
> > > >
> > > > select seri_no, ad as baslik, kull_ref as Kullanıcı_Referansi, sorumlu
> > > > from onay,dokuman where p_id='yonetici' and
> > onay.dokuman_id=dokuman.seri_no
> > > > and onay_durum.seri_no=seri_no and onay_durum.p_id='yonetici' and
> > > > (dokuman.durum=1 or dokuman.durum=2 or dokuman.durum=6 or
> > dokuman.durum=7)
> > > > and onay_durum.durum=0 and seri_no>0
> > > >
> > > >
> > > > was working properly in Postgre SQL 8.0 but in version Postgre SQL 8.2the
> > > > second "and" gives an error.
> > > > And it is :
> > > >
> > > > " Project dym.exe raised exception class EPSQLDatabaseError with
> > message
> > > > 'PostgreSQL Error Code : (1)
> > > > ERROR: missing FROM-clause entry for table "onay_durum" at character
> > 152'
> > > > .Process stopped.Use Step or Run to continue. "
> > >
> > > I don't understand how that query could have worked on PostgreSQL 8.0
> > > either. There's no table or alias with name "onay_durum" in the FROM
> > clause.
> > >
> > > Are you sure this is the exactly same query you tried on PostgreSQL 8.0?
> >
> > Didn't the default in postgresql.conf for add_missing_from change in 8.1?
> >
> > klint.

+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg(at)kgb(dot)une(dot)edu(dot)au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+

Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au> writes:
> (Someone who knows more than I do might comment on what happens
> to unqualfied field names when the add missing from brings in another
> field with the same name?)

Nothing --- it's always been the case that unqualified field names have
to reference one of the explicitly-listed FROM tables.

regards, tom lane