Thread: subselect on nonexistent column succeeds!!?!

Given:

orig_sav=# \d realtycompany_contacts
  Table "public.realtycompany_contacts"
       Column      |  Type  | Modifiers
------------------+--------+-----------
  realtycompany_id | bigint | not null
  contact_id       | bigint | not null


and

orig_sav=# \d users
                     Table "public.users"
       Column       |            Type             | Modifiers
-------------------+-----------------------------+-----------
  id                | bigint                      | not null
  name              | text                        |
  password          | text                        | not null
  version           | integer                     | not null
  contact           | bigint                      |
  comment           | text                        |
  organization      | bigint                      |
  pwd_storage_style | integer                     |
  old_name          | text                        |
  deleted           | boolean                     | not null
  deleted_date      | timestamp without time zone |


Why in the world does this statement parse and run:

orig_sav=# delete from realtycompany_contacts where contact_id in
(select contact_id from users);
DELETE 1634

Since users has *no* column contact_id ? One would expect the statement
to fail, and the transaction to get rolled back.


orig_sav=# select contact_id from users;
ERROR:  column "contact_id" does not exist

This is on 7.4.2.


----
James Robinson
Socialserve.com

Aha. Well, you learn something new every day. Yes, the behavior is
indeed like "contact_id is not null", which was true for all rows in
users, which explains why I lost all my data in realtycompany_contacts.

Thank goodness for backups. Many thanks!

James

On Jul 1, 2004, at 3:35 PM, Stephan Szabo wrote:

> AFAIK the spec requires that subselects like that can reference outer
> columns so contact_id inside the subselect refers to the outer
> contact_id
> column which does exist (which makes the effective behavior of the
> above
> clause the same as contact_id is not null I think)
>
----
James Robinson
Socialserve.com

On Thu, 1 Jul 2004, James Robinson wrote:

> Why in the world does this statement parse and run:
>
> orig_sav=# delete from realtycompany_contacts where contact_id in
> (select contact_id from users);
> DELETE 1634
>
> Since users has *no* column contact_id ? One would expect the statement
> to fail, and the transaction to get rolled back.

AFAIK the spec requires that subselects like that can reference outer
columns so contact_id inside the subselect refers to the outer contact_id
column which does exist (which makes the effective behavior of the above
clause the same as contact_id is not null I think)