Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group