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

The following bug has been logged on the website:

Bug reference:      16400
Logged by:          Scott Marcy
Email address:      postgresql@mscott.org
PostgreSQL version: 12.2
Operating system:   Mac OS
Description:

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. A standalone SELECT on the missing column generates
an error as it should. I've attached two examples, one using a view, one
using a temporary table, both of which exhibit the same problem. Note,
however, that using another column name like "foo" does properly generate an
error at the IN subquery.

-- Sample using view
BEGIN;

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.able@example.com'), ('Bob', 'Bubble', 'b.bubble@example.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.

SELECT id FROM test_view; -- Proof "id" does not exist in test_view

ROLLBACK;


-- Sample using temporary table
BEGIN;

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.able@example.com'), ('Bob', 'Bubble', 'b.bubble@example.com');

SELECT COUNT(*) FROM test_table;  -- 2

CREATE TEMPORARY TABLE temp_test_table AS SELECT id AS user_id, email FROM
test_table WHERE first_name LIKE 'A%';

SELECT COUNT(*) FROM temp_test_table; -- 1

SELECT COUNT(*) FROM test_table WHERE id IN (SELECT id FROM
temp_test_table);   -- Should be error, "id" does not exist in
temp_test_table, but outputs 2.

SELECT id FROM temp_test_table; -- Proof "id" does not exist in
temp_test_table, will generate error as expected

ROLLBACK;

On Wed, 29 Apr 2020 at 23:23, PG Bug reporting form
<noreply@postgresql.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.able@example.com'), ('Bob', 'Bubble', 'b.bubble@example.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