"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