From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | collin(dot)peters(at)gmail(dot)com |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #12276: Using old name of a renamed or dropped column in a sub-query does not throw error |
Date: | 2014-12-18 21:53:44 |
Message-ID: | 5330.1418939624@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
collin(dot)peters(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2014-12-18 21:54:58 | Re: BUG #12276: Using old name of a renamed or dropped column in a sub-query does not throw error |
Previous Message | Tom Lane | 2014-12-18 21:46:03 | Re: BUG #12275: configure incorrectly tests libxml2 version |