From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: postgresql@mscott.org
Subject: BUG #16400: IN (query) allows for reference to column that doesn't exist
Date: Wed, 29 Apr 2020 02:17:04 +0000
Message-ID: <16400-84f68bb46ba963e5@postgresql.org> (raw)
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, postgresql@mscott.org
Subject: Re: BUG #16400: IN (query) allows for reference to column that doesn't exist
In-Reply-To: <16400-84f68bb46ba963e5@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox