Re: BUG #12276: Using old name of a renamed or dropped column in a sub-query does not throw error - Mailing list pgsql-bugs

From Alvaro Herrera
Subject Re: BUG #12276: Using old name of a renamed or dropped column in a sub-query does not throw error
Date
Msg-id 20141218215458.GR1768@alvh.no-ip.org
Whole thread Raw
In response to BUG #12276: Using old name of a renamed or dropped column in a sub-query does not throw error  (collin.peters@gmail.com)
List pgsql-bugs
collin.peters@gmail.com wrote:

> Basically if you rename a column, and then use the original name in a
> subquery, that sub-query does not complain that the column no longer exists,
> and seems to complete ok. This will cause whatever SELECT or DELETE that is
> using the sub-query to continue on its merry way and cause havoc.

It's not using the old name of the column --- it's using the name of the
OTHER column, that is, the column of the same name in the other table.
In essence, your subquery always returns true.

> -- select on value of 'bar' and orders for user 1
> -- FAIL - returns all three order lines of 'bar'
> SELECT     *
> FROM    order_lines
> WHERE   value = 'bar'
> AND     order_id IN (
>     -- THIS SHOULD FAIL!! THIS COLUMN NAME DOES NOT EXIST ANYMORE
>     SELECT  order_id
>     FROM    users
>     WHERE   user_id = 1
> );
>
> -- same delete as above, except value of 'bar'
> DELETE
> FROM    order_lines
> WHERE   value = 'bar'
> AND     order_id IN (
>     -- THIS SHOULD FAIL!! THIS COLUMN NAME DOES NOT EXIST ANYMORE
>     SELECT  order_id
>     FROM    users
>     WHERE   user_id = 1
> );


--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #12276: Using old name of a renamed or dropped column in a sub-query does not throw error
Next
From: Collin Peters
Date:
Subject: Re: BUG #12276: Using old name of a renamed or dropped column in a sub-query does not throw error