Обсуждение: What's wrong with this query?

Here's the query:

SELECT R.RecipeId, R.Title, R.Description, R.ImageUrl, R.Rating,
R.PrepTime, R.CookTime, R.OwnerId, U.Alias, ts_rank_cd(R.TextSearch,
query) as Rank
FROM Recipes R, plainto_tsquery('veggie burgers') query
INNER JOIN Users U ON U.UserId = R.OwnerId
WHERE (R.TextSearch @@ query)
AND NOT EXISTS (select 1 from RecipeIngredients inner join
IngredientBlacklist using (IngredientId) where RecipeId = r.RecipeId
and IngredientBlacklist.UserId =
'affaa328-5b53-430e-991a-22674ede6faf' limit 1)
ORDER BY Rank DESC LIMIT 100;

Here's the error:

ERROR:  invalid reference to FROM-clause entry for table "r"
LINE 3: INNER JOIN Users U ON U.UserId = R.OwnerId
                                         ^
HINT:  There is an entry for table "r", but it cannot be referenced
from this part of the query.
********** Error **********
ERROR: invalid reference to FROM-clause entry for table "r"
SQL state: 42P01
Hint: There is an entry for table "r", but it cannot be referenced
from this part of the query.
Character: 239

This seems to happen when I add the fulltext functions.  Thanks!
Mike

Mike Christensen <mike@kitchenpc.com> writes:
> Here's the query:
> SELECT R.RecipeId, R.Title, R.Description, R.ImageUrl, R.Rating,
> R.PrepTime, R.CookTime, R.OwnerId, U.Alias, ts_rank_cd(R.TextSearch,
> query) as Rank
> FROM Recipes R, plainto_tsquery('veggie burgers') query
> INNER JOIN Users U ON U.UserId = R.OwnerId
> WHERE (R.TextSearch @@ query)
> AND NOT EXISTS (select 1 from RecipeIngredients inner join
> IngredientBlacklist using (IngredientId) where RecipeId = r.RecipeId
> and IngredientBlacklist.UserId =
> 'affaa328-5b53-430e-991a-22674ede6faf' limit 1)
> ORDER BY Rank DESC LIMIT 100;
> Here's the error:
> ERROR: �invalid reference to FROM-clause entry for table "r"
> LINE 3: INNER JOIN Users U ON U.UserId = R.OwnerId

You've been bitten by the mysql-ish idea that comma and JOIN are
interchangeable.  They are not.  In the SQL standard, and in every
implementation of it other than mysql, JOIN binds tighter than comma
--- so that INNER JOIN's condition can only refer to "query" and "u",
not "r".  Try it like this (or any of several other ways):

FROM Recipes R INNER JOIN Users U ON U.UserId = R.OwnerId,
     plainto_tsquery('veggie burgers') query
WHERE ...

            regards, tom lane

Hi Tom, sorry to be a total n00b, but can you give me a few more pointers?

I've noticed if I swap the two "tables" around, like:

FROM plainto_tsquery('quiche') query, Recipes R

then it works..

So are you saying that a JOIN can only refer to itself and the last
table on the FROM list?

I actually hate multi-table FROM clauses (I never use them, which is
why I'm unfamiliar with this behavior.  I'd love to just add a JOIN on
the plainto_tsquery function, however when I try that, it won't let me
refer to it on the SELECT clause thus I can't get the rank..  I guess
what I'm asking is what's the best way to write this particular query
:)

Mike

On Fri, Sep 17, 2010 at 7:05 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Mike Christensen <mike@kitchenpc.com> writes:
>> Here's the query:
>> SELECT R.RecipeId, R.Title, R.Description, R.ImageUrl, R.Rating,
>> R.PrepTime, R.CookTime, R.OwnerId, U.Alias, ts_rank_cd(R.TextSearch,
>> query) as Rank
>> FROM Recipes R, plainto_tsquery('veggie burgers') query
>> INNER JOIN Users U ON U.UserId = R.OwnerId
>> WHERE (R.TextSearch @@ query)
>> AND NOT EXISTS (select 1 from RecipeIngredients inner join
>> IngredientBlacklist using (IngredientId) where RecipeId = r.RecipeId
>> and IngredientBlacklist.UserId =
>> 'affaa328-5b53-430e-991a-22674ede6faf' limit 1)
>> ORDER BY Rank DESC LIMIT 100;
>> Here's the error:
>> ERROR:  invalid reference to FROM-clause entry for table "r"
>> LINE 3: INNER JOIN Users U ON U.UserId = R.OwnerId
>
> You've been bitten by the mysql-ish idea that comma and JOIN are
> interchangeable.  They are not.  In the SQL standard, and in every
> implementation of it other than mysql, JOIN binds tighter than comma
> --- so that INNER JOIN's condition can only refer to "query" and "u",
> not "r".  Try it like this (or any of several other ways):
>
> FROM Recipes R INNER JOIN Users U ON U.UserId = R.OwnerId,
>     plainto_tsquery('veggie burgers') query
> WHERE ...
>
>                        regards, tom lane
>

Mike Christensen <mike@kitchenpc.com> writes:
> So are you saying that a JOIN can only refer to itself and the last
> table on the FROM list?

Um, I think you've got the wrong mental model of the syntax.  JOIN
is a binary operation between two tables:

    tab1 JOIN tab2 ON some-condition-involving-tab1-and-tab2-only

You can nest these critters (ie, tab1 or tab2 could itself be a
JOIN construct), and you can write them as elements of a comma-separated
FROM list.  But the ON condition can never refer to anything outside
its JOIN construct.

            regards, tom lane