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

The following bug has been logged online:

Bug reference: 2130
Logged by: Tony Marston
Email address: tony(at)marston-home(dot)demon(dot)co(dot)uk
PostgreSQL version: 8.1.1
Operating system: Windows XP
Description: missing FROM-clause entry for table "mnu_task"
Details:

An SQL query which has worked in previous versions of PostgreSQL now fails,
and the error message is not logical. The error message is:

missing FROM-clause entry for table "mnu_task"

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

The table structure is:

CREATE TABLE mnu_task (
task_id character varying(40) DEFAULT ''::character varying NOT NULL,
task_desc character varying(50) DEFAULT ''::character varying NOT NULL
);
ALTER TABLE ONLY mnu_task
ADD CONSTRAINT mnu_task_pkey PRIMARY KEY (task_id);

CREATE TABLE mnu_task_field (
task_id character varying(40) DEFAULT ''::character varying NOT NULL,
field_id character varying(40) DEFAULT ''::character varying NOT NULL
);

ALTER TABLE ONLY mnu_task_field
ADD CONSTRAINT mnu_task_field_pkey PRIMARY KEY (task_id, field_id);

CREATE TABLE mnu_role_task (
role_id character varying(16) DEFAULT ''::character varying NOT NULL,
task_id character varying(40) DEFAULT ''::character varying NOT NULL
);

ALTER TABLE ONLY mnu_role_task
ADD CONSTRAINT mnu_role_task_pkey PRIMARY KEY (role_id, task_id);

Sample data is:

INSERT INTO mnu_task (task_id, task_desc) VALUES ('mnu_control(upd)',
'mnu_control(upd)');
INSERT INTO mnu_task (task_id, task_desc) VALUES ('mnu_dialog_type(list)',
'mnu_dialog_type(list)');
INSERT INTO mnu_task (task_id, task_desc) VALUES ('mnu_user(del)',
'mnu_user(del)');
INSERT INTO mnu_task (task_id, task_desc) VALUES ('mnu_user(enq)',
'mnu_user(enq)');
INSERT INTO mnu_task (task_id, task_desc) VALUES ('mnu_user(list)',
'mnu_user(list)');
INSERT INTO mnu_task (task_id, task_desc) VALUES ('mnu_user(search)',
'mnu_user(search)');
INSERT INTO mnu_task (task_id, task_desc) VALUES ('mnu_user(upd)',
'mnu_user(upd)');
INSERT INTO mnu_task (task_id, task_desc) VALUES ('x_option(enq)',
'x_option(enq)');
INSERT INTO mnu_task (task_id, task_desc) VALUES ('x_option(del)',
'x_option(del)');

INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_control(upd)',
'PSWD_CHANGE');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_control(upd)',
'PSWD_COUNT');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_control(upd)',
'PSWD_DAYS');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_control(upd)',
'PSWD_ENCRYPT');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_control(upd)',
'PSWD_FORMAT_DIGITS');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_control(upd)',
'PSWD_FORMAT_LOWER');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_control(upd)',
'PSWD_FORMAT_MINLEN');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_control(upd)',
'PSWD_FORMAT_UPPER');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_control(upd)',
'PSWD_RETRIES');
INSERT INTO mnu_task_field (task_id, field_id) VALUES
('mnu_dialog_type(list)', 'DUMMY1');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_user(del)',
'USER_PASSWORD');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_user(enq)',
'DUMMY1');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_user(enq)',
'USER_PASSWORD');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_user(upd)',
'DUMMY1');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_user(upd)',
'DUMMY2');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_user(upd)',
'DUMMY3');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_user(upd)',
'DUMMY4');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('mnu_user(upd)',
'USER_PASSWORD');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('x_option(enq)',
'DUMMY1');
INSERT INTO mnu_task_field (task_id, field_id) VALUES ('x_option(enq)',
'DUMMY2');

INSERT INTO mnu_role_task (role_id, task_id) VALUES ('DEMO',
'mnu_control(upd)');
INSERT INTO mnu_role_task (role_id, task_id) VALUES ('READONLY',
'mnu_dialog_type(enq)');
INSERT INTO mnu_role_task (role_id, task_id) VALUES ('READONLY',
'mnu_dialog_type(list)');
INSERT INTO mnu_role_task (role_id, task_id) VALUES ('READONLY',
'mnu_dialog_type(search)');
INSERT INTO mnu_role_task (role_id, task_id) VALUES ('READONLY',
'mnu_user(enq)');
INSERT INTO mnu_role_task (role_id, task_id) VALUES ('READONLY',
'mnu_user(list)');
INSERT INTO mnu_role_task (role_id, task_id) VALUES ('READONLY',
'mnu_user(search)');
INSERT INTO mnu_role_task (role_id, task_id) VALUES ('READONLY',
'mnu_user(upd)');
INSERT INTO mnu_role_task (role_id, task_id) VALUES ('READONLY',
'mnu_user(del)');
INSERT INTO mnu_role_task (role_id, task_id) VALUES ('SURVEY',
'mnu_user(enq)');
INSERT INTO mnu_role_task (role_id, task_id) VALUES ('SURVEY',
'mnu_user(list)');
INSERT INTO mnu_role_task (role_id, task_id) VALUES ('SURVEY',
'mnu_user(search)');
INSERT INTO mnu_role_task (role_id, task_id) VALUES ('SURVEY',
'mnu_user(upd)');
INSERT INTO mnu_role_task (role_id, task_id) VALUES ('SURVEY',
'mnu_user(del)');
INSERT INTO mnu_role_task (role_id, task_id) VALUES ('SURVEY',
'x_option(enq)');

"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