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 Tom Lane
Subject Re: BUG #12276: Using old name of a renamed or dropped column in a sub-query does not throw error
Date
Msg-id 5330.1418939624@sss.pgh.pa.us
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 writes:
> 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 is not a bug, it's just a SQL-standard outer query reference.
You dropped order_id from the "users" table, but there's still a
column by that name in "order_lines", so what you've got in

> 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
> );

is effectively ... IN (SELECT order_lines.order_id FROM users ...
so the IN condition will succeed as long as there's at least one users
row satisfying user_id = 1.

This is a widely known SQL gotcha, which unfortunately we can't do
anything about without rejecting useful and standard-compliant queries.
The usual advice for protecting yourself against this type of mistake
is to always table-qualify every column reference in a sub-SELECT.

            regards, tom lane

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #12275: configure incorrectly tests libxml2 version
Next
From: Alvaro Herrera
Date:
Subject: Re: BUG #12276: Using old name of a renamed or dropped column in a sub-query does not throw error