[Zope] duplicate column name

[Andy Dustman]

On Thu, 7 Dec 2000, Olaf Zanger wrote:

> 
> 
> Ivan Cornell schrieb:
> > 
> > Olaf Zanger wrote:
> > 
> > > i work with postgreSQL 7.0.2, ZPyGreSQLDA-0-0-3 and zope 2.2.2 on suse
> > > 7.0 linux
> > >
> > > when i try the line
> > >
> > > -> select adr.id,fac.id from fac, adr
> > >
> > 
> > You probably need to alias the columns:
> > select adr.id as adr_id, fac.id as fac_id from fac, adr
> > and then refer to adr_id & fac_id from zope.
> > 
> > [can't remember why, but think it is due to the way columns are addressed
> > in libpq]
> 
> thanks, 
> worked out fine, gets a bit tricky though :-)
> as visible further down it is not needed in "where" and "order by"
> clauses *ç%&?!
> 
> select
> fac.id as fac_id,
> adr.id as adr_id,
> fac.name as fac_name,
> adr.id as adr_id,
> owner_adr_id,
> 
> from
> fac,adr
> 
> where
> fac.name like '%'
> and adr.id=owner_adr_id
> and fac.mod_id=mod.id
> 
> order by
> fac.name

I suspect a lot of databases are like this, and it depends on
implementation of the DA. As an example, the original ZMySQLDA (circa
1.1.3), using MySQLmodule-1.4, probably returned all column names as
table.column. Versions of ZMySQLDA that use MySQLdb (i.e. the patched
1.1.3, 1.2, and 2.0.x) use just the column name. adr.id and fac.id have
the same column name, of course. I'm a little surprised that some DAs
would return column names with periods in them, since this would require
using the special namespace object to get at them, i.e. _['adr.id'].

The WHERE and ORDER BY clauses can use table.column references without any
problem. The issue is the names of the columns returned by a SELECT.

-- 
andy dustman  |  programmer  |  comstar.net is part of the Globix network
telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d
"Therefore, sweet knights, if you may doubt your strength or courage, 
come no further, for death awaits you all, with nasty, big, pointy teeth!"