Обсуждение: 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