Обсуждение: 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
http://www.uklug.co.uk
http://www.hjackson.org

"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