Thread: 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