Re: Nested JOINs - upgrade to 7.1.2

Hello,
I am trying to execute an SQL query that contains nested joins, but I
get parser error for some reason.
Below is the query I am trying to execute and the error I am getting:
SELECT media
FROM (dtrowmedia a INNER JOIN dtrow b ON (a.dtrow = b.objectid)) c
INNER JOIN dtcol d ON (c.dtcol = d.objectid)

psql: ERROR: parser: parse error at or near "inner"

Note that the following query executes with no errors:
SELECT media
FROM dtrowmedia a INNER JOIN dtrow b ON (a.dtrow = b.objectid)
INNER JOIN dtcol d ON (b.dtcol = d.objectid)

What am I doing wrong?
Please help me get it to work.
Thanks

Oleg

Oleg Lebedev <olebedev(at)waterford(dot)org> writes:
> What am I doing wrong?

Using 7.0, perhaps? The query parses fine for me in 7.1.

regards, tom lane

Oleg,

> Below is the query I am trying to execute and the error I am getting:
> SELECT media
> FROM (dtrowmedia a INNER JOIN dtrow b ON (a.dtrow = b.objectid)) c
> INNER JOIN dtcol d ON (c.dtcol = d.objectid)

First, the "INNER" keyword is not required on Postgres; you may omit it
with impunity. (If you are doing this on MS SQL Server or MS Access and
have joined this list by mistake, our advice won't be very useful to
you).

Second, you can't alias a JOINed set of tables; you may alias a
subselect, or alias a table:
FROM dtrow b ... is legal
FROM (SELECT * FROM dtrow) b ... is also legal
FROM (dtrow JOIN dtrowmedia ON dtrow.objectid = dtrowmedia.dtrow) b
... is not legal.

Thus, the query above is best expressed simply as:

SELECT media
FROM dtrowmedia a JOIN dtrow b ON a.dtrow = b.objectid
JOIN dtcol d ON b.dtcol = d.objectid;

If you actually did need to put some extra criteria into the first join,
then you would use a subselect:

SELECT media
FROM (SELECT * FROM dtrowmedia a JOIN dtrow b ON a.dtrow = b.objectid
WHERE dtrow > 3000) c
JOIN dtcol d ON c.dtcol = d.objectid;

But keep in mind in this case that you cannot reference a. or b. in the
SELECT list at the top, just c. because a. and b. exist only in the
subselect.

Now, go out and buy a copy of "SQL for Smarties". You'll be glad you
did.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco

"Josh Berkus" <josh(at)agliodbs(dot)com> writes:
> Second, you can't alias a JOINed set of tables;

Actually you can, according to my reading of SQL92:

<table reference> ::=
<table name> [ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
| <derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <joined table>

<derived table> ::= <table subquery>

<table subquery> ::= <subquery>

<subquery> ::= <left paren> <query expression> <right paren>

<query expression> ::=
<non-join query expression>
| <joined table>

<joined table> ::=
<cross join>
| <qualified join>
| <left paren> <joined table> <right paren>

<qualified join> ::=
<table reference> [ NATURAL ] [ <join type> ] JOIN
<table reference> [ <join specification> ]

So a parenthesized <qualified join> is a <derived table> and may
be followed by [ AS ] <correlation name>.

7.1 gets this right. 7.0's very preliminary implementation of JOIN
syntax did not. (It had a number of other bugs too, IIRC.)

The spec's grammar is horribly badly written: it's ambiguous whether the
parentheses should be parsed as part of a second-level <joined table>
which would imply that a correlation name would NOT be accepted.
It took a fair amount of work to derive a grammar that was unambiguous
and still accepted everything...

regards, tom lane

I think Tom was right and the problem with nested joins is caused by the
outdated installation of my PostgreSQL.
So, I am trying to upgrade to 7.1.2 and when I use:
pg_dumpall -o > file.bac
I get an error saying:
dumpRules(): SELECT failed for table setmedias. Explanation from backend:
'ERROR: cache lookup of attribute 5 in relation 23945 failed"

As I understand setmedias table corresponds to relation 23945, but I dropped
it a long time ago. How should remove this relation pointer?
thanks,

Oleg

Tom Lane wrote:

> "Josh Berkus" <josh(at)agliodbs(dot)com> writes:
> > Second, you can't alias a JOINed set of tables;
>
> Actually you can, according to my reading of SQL92:
>
> <table reference> ::=
> <table name> [ [ AS ] <correlation name>
> [ <left paren> <derived column list> <right paren> ] ]
> | <derived table> [ AS ] <correlation name>
> [ <left paren> <derived column list> <right paren> ]
> | <joined table>
>
> <derived table> ::= <table subquery>
>
> <table subquery> ::= <subquery>
>
> <subquery> ::= <left paren> <query expression> <right paren>
>
> <query expression> ::=
> <non-join query expression>
> | <joined table>
>
> <joined table> ::=
> <cross join>
> | <qualified join>
> | <left paren> <joined table> <right paren>
>
> <qualified join> ::=
> <table reference> [ NATURAL ] [ <join type> ] JOIN
> <table reference> [ <join specification> ]
>
> So a parenthesized <qualified join> is a <derived table> and may
> be followed by [ AS ] <correlation name>.
>
> 7.1 gets this right. 7.0's very preliminary implementation of JOIN
> syntax did not. (It had a number of other bugs too, IIRC.)
>
> The spec's grammar is horribly badly written: it's ambiguous whether the
> parentheses should be parsed as part of a second-level <joined table>
> which would imply that a correlation name would NOT be accepted.
> It took a fair amount of work to derive a grammar that was unambiguous
> and still accepted everything...
>
> regards, tom lane