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

The following bug has been logged online:

Bug reference:      2130
Logged by:          Tony Marston
Email address:      tony@marston-home.demon.co.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)');