Left Join Failures

[howesc]

Hi all,

I'm writing several queries with multiple tables, and left joins, and
the generated SQL is not working with postgres:

ProgrammingError: invalid reference to FROM-clause entry for table
"concert"
LINE 1: ..., artist LEFT JOIN audiofile ON
audiofile.concert=concert.id...
^
HINT: There is an entry for table "concert", but it cannot be
referenced from this part of the query.

my query is:
query = db((db.concert_purchases.auth_user == auth.user.id) &
(db.concert.id == db.concert_purchases.concert) &
(db.concert_purchases.expires > now) &
(db.artist.id == db.concert.artist) &
(db.venue.id == db.concert.venue))
rows = query.select(db.concert.name, db.concert.id,
db.concert_purchases.date,
db.artist.name,
db.venue.name,
db.concert.date,
db.audiofile.file,
left=db.audiofile.on(db.audiofile.concert ==
db.concert.id),
orderby=~db.concert_purchases.date)

I have web2py Version 1.63.5 (2009-06-03 23:35:00)

so, is there a way i can write the query to make the left join work,
or should i just do multiple queries?

thanks for the help,

christian

[mdipierro]

Please try a new version of web2py (I remember addressing a bug about
this in the past). if you still have a problem, try print the _select
and let us see the generate sql. This should definitively be possible.

Massimo

[howesc]

Massimo,

I downloaded the latest released version today: Version 1.65.11
(2009-08-04 16:42:46), and it still fails. here is the generated SQL:

SELECT concert.name, concert.id, concert_purchases.date, artist.name,
venue.name, concert.date, audiofile.file FROM venue, concert,
concert_purchases, artist LEFT JOIN audiofile ON
audiofile.concert=concert.id WHERE ((((concert_purchases.auth_user=3
AND concert.id=concert_purchases.concert) AND
concert_purchases.expires>'2009-08-08 09:46:23') AND
artist.id=concert.artist) AND venue.id=concert.venue) ORDER BY
concert_purchases.date DESC;

Thanks,

Christian

[mdipierro]

Is this sqlite? The generated SQL looks right to me. Do you see
anything wrong with it?

[howesc]

This is in Postgres.

I did a little digging, and apparently mixing the JOIN syntax is a
problem. So if i re-write the query in this fashion it works:

SELECT c.name, c.id, cp.date, a.name, v.name, c.date, af.file
FROM venue v
JOIN concert c ON c.venue=v.id
JOIN concert_purchases cp ON cp.concert=c.id
JOIN artist a ON a.id=c.artist
LEFT JOIN audiofile af ON af.concert=c.id
WHERE cp.auth_user=3
AND c.id=cp.concert
AND cp.expires>'2009-08-08 09:46:23'
ORDER BY cp.date DESC;

some others have suggested that mixing the join syntax has operator
precedence issues, and that Postgres follows the standard while other
databases let you get away with the "incorrect" syntax:
http://archives.postgresql.org/pgsql-general/2006-09/msg01046.php
http://stackoverflow.com/questions/187146/inner-join-outer-join-is-the-order-of-tables-in-from-important

based on their analysis a standard compliant SQL parser will interpret
the web2py generated query like this:

SELECT concert.name, concert.id, concert_purchases.date, artist.name,
venue.name, concert.date, audiofile.file FROM venue, concert,

concert_purchases, (artist LEFT JOIN audiofile ON
audiofile.concert=concert.id) WHERE ((((concert_purchases.auth_user=3

AND concert.id=concert_purchases.concert) AND
concert_purchases.expires>'2009-08-08 09:46:23') AND
artist.id=concert.artist) AND venue.id=concert.venue) ORDER BY
concert_purchases.date DESC;

note the added parens around the last item in the from clause and the
LEFT JOIN item - hence concert is not visible. if i re-write it
knowing the operator precedence like this:

SELECT concert.name, concert.id, concert_purchases.date, artist.name,
venue.name, concert.date, audiofile.file

FROM venue, artist,
concert_purchases, concert LEFT JOIN audiofile ON

audiofile.concert=concert.id WHERE ((((concert_purchases.auth_user=3
AND concert.id=concert_purchases.concert) AND
concert_purchases.expires>'2009-08-08 09:46:23') AND
artist.id=concert.artist) AND venue.id=concert.venue) ORDER BY
concert_purchases.date DESC;

it also works...which explains to me why some of my left joins work
and some don't - if i'm lucky and the last table in the from clause is
what i am left joining on it works.

that being said, it looks like i'm the only one with that problem so i
don't expect you to try and fix that right away.

thanks,

Christian

[Richard]

hi Christian,
I also just got this error message when testing a migration from
sqlite to postgres. Thanks for the diagnosis of the problem.
Have you found a work around yet using the DAL?
Richard

On Aug 10, 4:56 pm, howesc <how...@umich.edu> wrote:
> This is in Postgres.
>
> I did a little digging, and apparently mixing the JOIN syntax is a

> problem.  So if i re-write thequeryin this fashion it works:

>
> SELECT c.name, c.id, cp.date, a.name, v.name, c.date, af.file
> FROM venue v
> JOIN concert c ON c.venue=v.id
> JOIN concert_purchases cp ON cp.concert=c.id
> JOIN artist a ON a.id=c.artist
> LEFT JOIN audiofile af ON af.concert=c.id
> WHERE cp.auth_user=3
>   AND c.id=cp.concert
>   AND cp.expires>'2009-08-08 09:46:23'
> ORDER BY cp.date DESC;
>
> some others have suggested that mixing the join syntax has operator
> precedence issues, and that Postgres follows the standard while other

> databases let you get away with the "incorrect" syntax:http://archives.postgresql.org/pgsql-general/2006-09/msg01046.phphttp://stackoverflow.com/questions/187146/inner-join-outer-join-is-th...

>
> based on their analysis a standard compliant SQL parser will interpret

> the web2py generatedquerylike this:

>  SELECT concert.name, concert.id, concert_purchases.date, artist.name,
>  venue.name, concert.date, audiofile.file FROM venue, concert,
>  concert_purchases, (artist LEFT JOIN audiofile ON
>  audiofile.concert=concert.id) WHERE ((((concert_purchases.auth_user=3
>  AND concert.id=concert_purchases.concert) AND
>  concert_purchases.expires>'2009-08-08 09:46:23') AND
>  artist.id=concert.artist) AND venue.id=concert.venue) ORDER BY
>  concert_purchases.date DESC;
>
> note the added parens around the last item in the from clause and the
> LEFT JOIN item - hence concert is not visible.  if i re-write it
> knowing the operator precedence like this:
>
> SELECT concert.name, concert.id, concert_purchases.date, artist.name,
> venue.name, concert.date, audiofile.file
> FROM venue, artist,
> concert_purchases, concert LEFT JOIN audiofile ON
> audiofile.concert=concert.id WHERE ((((concert_purchases.auth_user=3
> AND concert.id=concert_purchases.concert) AND
> concert_purchases.expires>'2009-08-08 09:46:23') AND
> artist.id=concert.artist) AND venue.id=concert.venue) ORDER BY
> concert_purchases.date DESC;
>
> it also works...which explains to me why some of my left joins work

> and some don't - if i'm lucky and the lasttablein the from clause is

> > > > >    query= db((db.concert_purchases.auth_user == auth.user.id) &

> > > > >                (db.concert.id == db.concert_purchases.concert) &
> > > > >                (db.concert_purchases.expires > now) &
> > > > >                (db.artist.id == db.concert.artist) &
> > > > >                (db.venue.id == db.concert.venue))

> > > > >     rows =query.select(db.concert.name, db.concert.id,

> > > > >                         db.concert_purchases.date,
> > > > >                         db.artist.name,
> > > > >                         db.venue.name,
> > > > >                         db.concert.date,
> > > > >                         db.audiofile.file,
> > > > >                         left=db.audiofile.on(db.audiofile.concert ==
> > > > > db.concert.id),
> > > > >                         orderby=~db.concert_purchases.date)
>
> > > > > I have web2py Version 1.63.5 (2009-06-03 23:35:00)
>

> > > > > so, istherea way i can write thequeryto make the left join work,

[mdipierro]

Are you sure this does not work?

c=db.concert
cp=db.concert_purchases
a=db.artist
db(cp.auth_user==3)(c.id==cp.concert)(cp.expires>'2009-08-08
09:46:23').select(c.name, c.id, cp.date, a.name, v.name, c.date,
af.file,left=(c.on(c.value==v.id),cp.on(cp.concert==c.id),a.on
(a.id==c.artist)), orderby=~cp.date)

Massimo

On Aug 12, 9:09 pm, Richard <richar...@gmail.com> wrote:
> hi Christian,
> I also just got this error message when testing a migration from
> sqlite to postgres. Thanks for the diagnosis of the problem.
> Have you found a work around yet using the DAL?
> Richard
>
> On Aug 10, 4:56 pm, howesc <how...@umich.edu> wrote:
>
> > This is in Postgres.
>
> > I did a little digging, and apparently mixing the JOIN syntax is a
> > problem.  So if i re-write thequeryin this fashion it works:
>
> > SELECT c.name, c.id, cp.date, a.name, v.name, c.date, af.file
> > FROM venue v
> > JOIN concert c ON c.venue=v.id
> > JOIN concert_purchases cp ON cp.concert=c.id
> > JOIN artist a ON a.id=c.artist
> > LEFT JOIN audiofile af ON af.concert=c.id
> > WHERE cp.auth_user=3
> >   AND c.id=cp.concert
> >   AND cp.expires>'2009-08-08 09:46:23'
> > ORDER BY cp.date DESC;
>
> > some others have suggested that mixing the join syntax has operator
> > precedence issues, and that Postgres follows the standard while other

> > databases let you get away with the "incorrect" syntax:http://archives.postgresql.org/pgsql-general/2006-09/msg01046.phphttp......

[howesc]

Thanks for the tip! A little tweaking to your suggestion makes it
work:

c=db.concert
cp=db.concert_purchases
a=db.artist
v=db.venue
af=db.audiofile
test = db(cp.auth_user==3)(c.id==cp.concert)(cp.expires>'2009-08-08

09:46:23').
select(c.name, c.id, cp.date, a.name, v.name, c.date,

af.file,left=(c.on((c.venue==v.id) & (v.name != None)),
cp.on((cp.concert==c.id) & (cp.concert !=
None)),
a.on((a.id==c.artist) & (a.name != None)),
af.on(af.concert==c.id)), orderby=~cp.date)

I put in where clause restrictions to make some of the left joins not
really left joins, I probably don't need to do that for this
particular query.

thanks again for the suggestion to help me re-craft the query!

christian

[Richard]

hi Massimo,

you mentioned that the initial query was invalid (http://
code.google.com/p/web2py/issues/detail?id=52).
Could you clarify what was invalid and the proper way to do multiple
joins?

Richard

[mdipierro]

howesc explained it in his posts: "that mixing the join syntax has

operator
precedence issues, and that Postgres follows the standard while other
databases let you get away with the "incorrect" syntax:
http://archives.postgresql.org/pgsql-general/2006-09/msg01046.php "

Massimo

[Richard]

yeah I read howesc's diagnosis of the problem (and filed a bug report
with that explanation), but howesc was not able to solve the problem
until you posted a solution.
You said his query was fundamentally wrong. So I was wondering how to
do multiple joins properly in web2py, because I am also facing the
same problem.

Richard

[mdipierro]

The bottom line is that you cannot inner-join a table, then left-join
that table to another.
In the original query you are left joining audiofile to concert while
concert is inner joined to venue.

web2py has a problem: when you perform an outer join, and then a left
join, it does not let you specify which is the table (from the inner
join) to left join to.

Reading this again and again I realized I close this ticket too soon.
The problem is more complex than I originally understood but better
scoped. Since we cannot change the web2py syntax we need to figure out
which table to left join to from the on(query) expression. I now think
this is easier to fix than I thought.

I will reopen the ticket.

Richard. thanks for insisting on this issue.

Massimo