Re: BUG #2130: missing FROM-clause entry for table "mnu_task"

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Tony Marston" <tony(at)marston-home(dot)demon(dot)co(dot)uk>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2130: missing FROM-clause entry for table "mnu_task"
Date: 2005-12-26 15:21:26
Message-ID: 9558.1135610486@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"Tony Marston" <tony(at)marston-home(dot)demon(dot)co(dot)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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message kenichi nakanishi 2005-12-26 15:47:36 BUG #2131: SQL Query Bug ?
Previous Message Tony Marston 2005-12-26 15:09:51 BUG #2130: missing FROM-clause entry for table "mnu_task"