Re: BUG #16400: IN (query) allows for reference to column that doesn't exist

On Wed, 29 Apr 2020 at 23:23, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> The following code should generate an error for an unknown column in the
> SELECT query that uses IN (), but it does not, and it behaves as though all
> rows pass the IN test.

> CREATE TABLE test_table (
> id SERIAL PRIMARY KEY,
> first_name text,
> last_name text,
> email text
> );
>
> INSERT INTO test_table (first_name, last_name, email) VALUES ('Alice',
> 'Able', 'a(dot)able(at)example(dot)com'), ('Bob', 'Bubble', 'b(dot)bubble(at)example(dot)com');
>
> SELECT COUNT(*) FROM test_table; -- 2
>
> CREATE VIEW test_view AS SELECT id AS user_id, email FROM test_table WHERE
> first_name LIKE 'A%';
>
> SELECT COUNT(*) FROM test_view; -- 1
>
> SELECT COUNT(*) FROM test_table WHERE id IN (SELECT id FROM test_view); --
> Should be error, "id" does not exist in test_view, but outputs 2.

The query you've shown is perfectly valid. test_view does not have a
column named "id", but "test_table" does, and all the query planner
sees here is a valid correlated subquery. So, this just seems to be a
lesson on why you should alias your tables and prefix your columns
with the alias. Not doing so leaves you open to queries doing the
wrong thing when you drop columns. I tend to use short alias, and
your query converted to use those looks like SELECT COUNT(*) FROM
test_table tt WHERE id IN (SELECT tt.id FROM test_view tv); basically
will filter out NULLs providing test_view produces at least 1 row,
otherwise it'll return nothing... which is perfectly valid SQL,
although perhaps just a strange way to express it.

David