Re: BUG #2130: missing FROM-clause entry for table "mnu_task" - Mailing list pgsql-bugs

From Tom Lane
Subject Re: BUG #2130: missing FROM-clause entry for table "mnu_task"
Date
Msg-id 9558.1135610486@sss.pgh.pa.us
Whole thread Raw
In response to BUG #2130: missing FROM-clause entry for table "mnu_task"  ("Tony Marston" <tony@marston-home.demon.co.uk>)
List pgsql-bugs
"Tony Marston" <tony@marston-home.demon.co.uk> writes:
> An SQL query which has worked in previous versions of PostgreSQL now fails,
> and the error message is not logical. The error message is:

> The query is:

> SELECT count(*) FROM mnu_task, mnu_task_field
> LEFT JOIN mnu_role_task ON (mnu_role_task.task_id=mnu_task.task_id AND
                                                    ^^^^^^^^
> mnu_role_task.role_id='DEMO')
> WHERE mnu_task_field.task_id=mnu_task.task_id
> GROUP BY mnu_task.task_id, task_desc

It's complaining about the illegal reference to mnu_task from inside the
JOIN of the other two relations.  I'm not sure what you consider "work"
to mean for this query, but what you were getting before was a fairly
strange behavior involving two joins to independent copies of mnu_task.

Perhaps what you really want is

SELECT ... FROM (mnu_task CROSS JOIN mnu_task_field) LEFT JOIN mnu_role_task ON ...

The code looks to me like it was ported from MySQL, which (before 5.x)
improperly interprets the first syntax as meaning the second.

I'm not sure whether it's practical to get the parser to issue a more
specific error message in this situation.

            regards, tom lane

pgsql-bugs by date:

Previous
From: "Tony Marston"
Date:
Subject: BUG #2130: missing FROM-clause entry for table "mnu_task"
Next
From: "Konstantin S. Zhinko [tIT]"
Date:
Subject: BUG #2123: join between stored procedures