select * from table1 left join table2 on table1.id=table2.id Error: duplicate column name, id Is this an error or a feature? \Oliver
How do you guys/girls do joins? Am I missing something about Zope? \Oliver
-----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Oliver Marx Sent: 29. maj 2002 19:18 To: Zope@Zope. Org Subject: [Zope] ZmysqlDA
select * from table1 left join table2 on table1.id=table2.id
Error: duplicate column name, id
Is this an error or a feature?
\Oliver
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
This has nothing to do with Zope other than the fact that you're carrying out a SQL query via a ZSQL method. This error message comes from MySQL. Apparently there's a problem with your syntax. Maybe MySQL doesn't support this statement? - C On Wed, 2002-05-29 at 13:34, Oliver Marx wrote:
How do you guys/girls do joins? Am I missing something about Zope?
\Oliver
-----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Oliver Marx Sent: 29. maj 2002 19:18 To: Zope@Zope. Org Subject: [Zope] ZmysqlDA
select * from table1 left join table2 on table1.id=table2.id
Error: duplicate column name, id
Is this an error or a feature?
\Oliver
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
This is *not* the case. The query returns a perfect result in mysql. http://www.mysql.com/doc/J/O/JOIN.html \Oliver
-----Original Message----- From: Chris McDonough [mailto:chrism@zope.com] Sent: 29. maj 2002 19:38 To: Oliver Marx Cc: Zope@Zope. Org Subject: Re: URGENT RE: [Zope] ZmysqlDA
This has nothing to do with Zope other than the fact that you're carrying out a SQL query via a ZSQL method. This error message comes from MySQL. Apparently there's a problem with your syntax. Maybe MySQL doesn't support this statement?
- C
On Wed, 2002-05-29 at 13:34, Oliver Marx wrote:
How do you guys/girls do joins? Am I missing something about Zope?
\Oliver
-----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Oliver Marx Sent: 29. maj 2002 19:18 To: Zope@Zope. Org Subject: [Zope] ZmysqlDA
select * from table1 left join table2 on table1.id=table2.id
Error: duplicate column name, id
Is this an error or a feature?
\Oliver
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
On Wed, 2002-05-29 at 13:38, Chris McDonough wrote:
This has nothing to do with Zope other than the fact that you're carrying out a SQL query via a ZSQL method. This error message comes from MySQL. Apparently there's a problem with your syntax. Maybe MySQL doesn't support this statement?
Actually, it does, or would, once he got past the duplicate column problem, since you can't return a column named id to Zope. -- Andy Dustman PGP: 0x930B8AB6 @ .net http://dustman.net/andy "Cogito, ergo sum." -- Rene Descartes "I yam what I yam and that's all that I yam." -- Popeye
On Wed, 2002-05-29 at 13:45, Andy Dustman wrote:
On Wed, 2002-05-29 at 13:38, Chris McDonough wrote:
This has nothing to do with Zope other than the fact that you're carrying out a SQL query via a ZSQL method. This error message comes from MySQL. Apparently there's a problem with your syntax. Maybe MySQL doesn't support this statement?
Actually, it does, or would, once he got past the duplicate column problem, since you can't return a column named id to Zope.
In fact, upon further consideration, the duplicate column error really is a Zope/ZMySQLDA error and not a MySQL error. ZMySQLDA (and likely many other DAs) will raise an exception if the same column appears twice in the result set. It's not possible to know (without parsing SQL) that the two columns are the same. The old ZMySQLDA (1.x series) would return all column names as table.col; the new one (2.x series) does not. -- Andy Dustman PGP: 0x930B8AB6 @ .net http://dustman.net/andy "Cogito, ergo sum." -- Rene Descartes "I yam what I yam and that's all that I yam." -- Popeye
So the DA's are using the column names as id for objects? Then problem only arises with a select * ... in other cases one could just use select table1.postal_id, table2.postal_id ... problem gone! But but but..... ZmysqlDA should *not* abstruct the SQL92 definition... \Oliver
-----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Andy Dustman Sent: 29. maj 2002 19:57 To: Zope@Zope. Org Subject: Re: URGENT RE: [Zope] ZmysqlDA
On Wed, 2002-05-29 at 13:45, Andy Dustman wrote:
On Wed, 2002-05-29 at 13:38, Chris McDonough wrote:
This has nothing to do with Zope other than the fact that you're carrying out a SQL query via a ZSQL method. This error message comes from MySQL. Apparently there's a problem with your syntax. Maybe MySQL doesn't support this statement?
Actually, it does, or would, once he got past the duplicate column problem, since you can't return a column named id to Zope.
In fact, upon further consideration, the duplicate column error really is a Zope/ZMySQLDA error and not a MySQL error. ZMySQLDA (and likely many other DAs) will raise an exception if the same column appears twice in the result set. It's not possible to know (without parsing SQL) that the two columns are the same. The old ZMySQLDA (1.x series) would return all column names as table.col; the new one (2.x series) does not.
-- Andy Dustman PGP: 0x930B8AB6 @ .net http://dustman.net/andy "Cogito, ergo sum." -- Rene Descartes "I yam what I yam and that's all that I yam." -- Popeye
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
On Wed, 2002-05-29 at 14:11, Oliver Marx wrote:
So the DA's are using the column names as id for objects?
Then problem only arises with a select * ... in other cases one could just use select table1.postal_id, table2.postal_id ...
problem gone!
Don't think so: You still have two columns with the same name.
But but but.....
ZmysqlDA should *not* abstruct the SQL92 definition...
The "problem" is, Zope requires a DA to return each row as a dictionary, and a dictionary cannot have two items with the same key. You could overwrite one value with another, but then you'd have no deterministic way of knowing which column you actually got. In this case, they are actually the same value, due to the JOIN condition, but that's not true in the general case. So an exception is raised to alert you to this condition. -- Andy Dustman PGP: 0x930B8AB6 @ .net http://dustman.net/andy "Cogito, ergo sum." -- Rene Descartes "I yam what I yam and that's all that I yam." -- Popeye
i don't think this has anything to do with zope or ZMySQLDA. you should read up on MySQL and especially about the SQL it uses. jens On Wednesday, May 29, 2002, at 01:34 , Oliver Marx wrote:
How do you guys/girls do joins? Am I missing something about Zope?
\Oliver
-----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Oliver Marx Sent: 29. maj 2002 19:18 To: Zope@Zope. Org Subject: [Zope] ZmysqlDA
select * from table1 left join table2 on table1.id=table2.id
Error: duplicate column name, id
Is this an error or a feature?
\Oliver
I'm not a novice when it comes to MySQL http://www.mysql.com/doc/J/O/JOIN.html select * from table1 left join table2 on table1.id=table2.id Is valid MySQL-code... but ok id is reserved i Zope so i tried: select * from table1 left join table2 on table1.postal_id=table2.postal_id Still the same problem. The cause must be that ZmysqlDA return column_name rather than table.column_name. I'm still very novice when it comes to Zope, but my guess is that Zope or ZmysqlDA generates a list of object based on the result set using the column names as id's if you have two columns with the same name there would be a conflict. \Oliver
-----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Jens Vagelpohl Sent: 29. maj 2002 19:43 To: Oliver Marx Cc: Zope@Zope. Org Subject: Re: URGENT RE: [Zope] ZmysqlDA
i don't think this has anything to do with zope or ZMySQLDA.
you should read up on MySQL and especially about the SQL it uses.
jens
On Wednesday, May 29, 2002, at 01:34 , Oliver Marx wrote:
How do you guys/girls do joins? Am I missing something about Zope?
\Oliver
-----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Oliver Marx Sent: 29. maj 2002 19:18 To: Zope@Zope. Org Subject: [Zope] ZmysqlDA
select * from table1 left join table2 on table1.id=table2.id
Error: duplicate column name, id
Is this an error or a feature?
\Oliver
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Oliver, Check your MySQL documentation and the version of your running MySQL. The Zope DB connection products simply pass the SQL query on to the database management system and report what errors it finds.... On Wed, 29 May 2002, Oliver Marx wrote:
How do you guys/girls do joins? Am I missing something about Zope?
\Oliver
Oliver Marx Sent: 29. maj 2002 19:18 To: Zope@Zope. Org Subject: [Zope] ZmysqlDA
select * from table1 left join table2 on table1.id=table2.id
Error: duplicate column name, id
Is this an error or a feature?
\Oliver
This syntaxt is perfetly ok: select * from table1 left join table2 on table1.postal_id=table2.postal_id http://www.mysql.com/doc/J/O/JOIN.html Id is reserved in Zope, so I altered the tables. But Zope (ZmysqlDA) is still complaining about duplicate column names. \Oliver
-----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Dennis Allison Sent: 29. maj 2002 19:52 To: Oliver Marx Cc: Zope@Zope. Org Subject: Re: URGENT RE: [Zope] ZmysqlDA
Oliver, Check your MySQL documentation and the version of your running MySQL. The Zope DB connection products simply pass the SQL query on to the database management system and report what errors it finds....
On Wed, 29 May 2002, Oliver Marx wrote:
How do you guys/girls do joins? Am I missing something about Zope?
\Oliver
Oliver Marx Sent: 29. maj 2002 19:18 To: Zope@Zope. Org Subject: [Zope] ZmysqlDA
select * from table1 left join table2 on table1.id=table2.id
Error: duplicate column name, id
Is this an error or a feature?
\Oliver
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Have you tried select table1.postal_id as id1, table2.postal_id as id2 ...... then Zope sees them as id1 and id2, I think Nitin Borwankar Oliver Marx wrote:
This syntaxt is perfetly ok:
select * from table1 left join table2 on table1.postal_id=table2.postal_id
Id is reserved in Zope, so I altered the tables.
But Zope (ZmysqlDA) is still complaining about duplicate column names.
\Oliver
-----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Dennis Allison Sent: 29. maj 2002 19:52 To: Oliver Marx Cc: Zope@Zope. Org Subject: Re: URGENT RE: [Zope] ZmysqlDA
Oliver, Check your MySQL documentation and the version of your running MySQL. The Zope DB connection products simply pass the SQL query on to the database management system and report what errors it finds....
On Wed, 29 May 2002, Oliver Marx wrote:
How do you guys/girls do joins? Am I missing something about Zope?
\Oliver
Oliver Marx Sent: 29. maj 2002 19:18 To: Zope@Zope. Org Subject: [Zope] ZmysqlDA
select * from table1 left join table2 on table1.id=table2.id
Error: duplicate column name, id
Is this an error or a feature?
\Oliver
_______________________________________________ Zope maillist - Zope@zope.org http://rd.mailshell.com/lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://rd.mailshell.com/lists.zope.org/mailman/listinfo/zope-announce http://rd.mailshell.com/lists.zope.org/mailman/listinfo/zope-dev )
_______________________________________________ Zope maillist - Zope@zope.org http://rd.mailshell.com/lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://rd.mailshell.com/lists.zope.org/mailman/listinfo/zope-announce http://rd.mailshell.com/lists.zope.org/mailman/listinfo/zope-dev )
On Wed, 2002-05-29 at 13:18, Oliver Marx wrote:
select * from table1 left join table2 on table1.id=table2.id
Error: duplicate column name, id
Is this an error or a feature?
You have id in both tables, so it appears twice in the result of the JOIN (feature); using * returns it twice. Also, id is a reserved name in Zope, so you need to do something like this: SELECT col1, col2, ... coln, id AS id_ FROM table1 LEFT JOIN table2 USING(id) (USING is just a shortcut in this case for your condition). -- Andy Dustman PGP: 0x930B8AB6 @ .net http://dustman.net/andy "Cogito, ergo sum." -- Rene Descartes "I yam what I yam and that's all that I yam." -- Popeye
participants (6)
-
Andy Dustman -
Chris McDonough -
Dennis Allison -
Jens Vagelpohl -
nitin＠borwankar.com -
Oliver Marx