[SQL-CVS] [ sqlobject-Bugs-1517116 ] Multiple join of table with itself fails with Postgres

[SourceForge.net <no...@so...>]

Bugs item #1517116, was opened at 2006-07-04 22:55
Message generated for change (Comment added) made by phd
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1517116&group_id=74338

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: Postgres
Group: SQLObject release (specify)
>Status: Closed
>Resolution: Wont Fix
Priority: 5
Submitted By: Henrik Weber (hweber)
>Assigned to: Oleg Broytmann (phd)
Summary: Multiple join of table with itself fails with Postgres

Initial Comment:
With SQLobject 0.7.0 using Postgres I have tried to 
join a table with itself more than once. The code 
looks something like this:

alias1 = Alias(Tbl, "T1")
alias2 = Alias(Tbl, "T2")

joins = [INNERJOINOn(Tbl, alias1, Tbl.q.tblid == 
alias1.q.tblid), INNERJOINOn(Tbl, alias2, Tbl.q.tblid 
== alias2.q.tblid)]

result = Tbl.select(somewherecrit, join=joins)

The resulting SQL statement looks like this:

SELECT Tbl.id, Tbl.tblid FROM Tbl INNER JOIN Tbl AS T1 
ON (Tbl.tblid = T2.tblid), Tbl INNER JOIN Tbl AS T2 ON 
(Tbl.tblid = T2.tblid)
WHERE ...

The resulting error message is:
psycopg2.ProgrammingError: table name "Tbl" specified 
more than once


The correct statement would actually have been:

SELECT Tbl.id, Tbl.tblid FROM Tbl INNER JOIN Tbl AS T1 
ON (Tbl.tblid = T2.tblid) INNER JOIN Tbl AS T2 ON 
(Tbl.tblid = T2.tblid)
WHERE ...

I tried defining the join recursively, but you can't 
use a Join object as parameter for a new join.

----------------------------------------------------------------------

>Comment By: Oleg Broytmann (phd)
Date: 2006-08-22 18:36

Message:
Logged In: YES 
user_id=4799

If you don't want the second Tbl, do not name it. Use None
instead:

INNERJOINOn(None, alias2...

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1517116&group_id=74338