Is this a bug?
The following sql statement fails because the column "user_id" does
not exist in the users table.
=# select user_id from users WHERE username = 'blah';
ERROR: column "user_id" does not exist
LINE 1: select user_id from users WHERE username = 'blah......
^
The following shows a valid statement where I want to delete one user
entry from the "map_users_roles" table
=# delete from map_users_roles where user_id = (select id from users
WHERE username = 'blah');
DELETE 2
If I made a mistake and changed the "id" column to "user_id" then from
some the statement executes.
=# delete from map_users_roles where user_id = (select user_id from
users WHERE username = 'blah');
DELETE 33631
I would have thought that the last statement would fail. Instead it
removed all the entries from the table. This is happening because I
did not qualify the column names as follows...
=# delete from map_users_roles where user_id = (select u.user_id from
users as u WHERE username = 'blah');
ERROR: column u.user_id does not exist
LINE 1: ...lete from map_users_roles where user_id = (select u.user_id ...
Still, this was quite a suprise to me and I would consider this a bug.
Thoughts?
"Harry Jackson" <harryjackson@gmail.com> writes:
If I made a mistake and changed the "id" column to "user_id" then from
some the statement executes.
=# delete from map_users_roles where user_id = (select user_id from
users WHERE username = 'blah');
This is a standard "outer reference" construction, ie, user_id refers to
the field exposed in the outer-level query if there's no match in the
inner query. Possibly the SQL spec authors should have made it a little
harder to invoke an outer reference, but they didn't.
regards, tom lane
Harry Jackson wrote:
The following sql statement fails because the column "user_id" does
not exist in the users table.=# select user_id from users WHERE username = 'blah';
ERROR: column "user_id" does not exist
LINE 1: select user_id from users WHERE username = 'blah......
^
[...]
=# delete from map_users_roles where user_id = (select user_id from
users WHERE username = 'blah');
DELETE 33631
[...]
Still, this was quite a suprise to me and I would consider this a bug.
Thoughts?
It is not a bug, but it is one of the best examples why it is good to
always qualify columns that I have ever seen!
Yours,
Laurenz Albe