BUG #16400: IN (query) allows for reference to column that doesn't exist - Mailing list pgsql-bugs

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;