From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: gurmokh.sangha@starlingbank.com
Subject: BUG #17754: Subquery IN clause returns row matches where subquery is invalid
Date: Thu, 19 Jan 2023 18:02:11 +0000
Message-ID: <17754-0c04a53e0078e5d6@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17754
Logged by: Gurmokh Sangha
Email address: gurmokh.sangha@starlingbank.com
PostgreSQL version: 15.1
Operating system: Debian GNU/Linux 11 (bullseye)
Description:
A subquery that has an incorrect column name, that happens to match a column
name in the outer query evaluates as true for all rows in the outer query.
setup :
drop table if exists atable ;
drop table if exists btable ;
create table atable (a int, b int) ;
create table btable (c int, d int) ;
insert into atable select generate_series(1,10) as a, generate_series(1,10)
as b ;
insert into btable select generate_series(1,10) as c, generate_series(1,10)
as d ;
Take query:
Select a from btable where c =10;
This evaluates an error as column 'a' is not in 'btable'
However if this query is used as a subquery IN on atable such as:
select count(*)
from atable
where a in ( select a from btable where c = 10) ;
count|
-----+
10|
This evaluates as true for all rows left of IN.
explain plan:
QUERY PLAN
Aggregate (cost=43294.65..43294.66 rows=1 width=8) (actual
time=0.045..0.045 rows=1 loops=1)
-> Seq Scan on atable (cost=0.00..43291.83 rows=1130 width=0) (actual
time=0.019..0.041 rows=10 loops=1)
Filter: (SubPlan 1)
SubPlan 1
-> Seq Scan on btable (cost=0.00..38.25 rows=11 width=4) (actual
time=0.001..0.001 rows=1 loops=10)
Filter: (c = 10)
Rows Removed by Filter: 9
Planning Time: 0.084 ms
Execution Time: 0.082 ms
However if using a column in the subquery that is not in the outer query the
statement will fail as you would expect.
select count(*)
from atable
where a in ( select g from btable where c = 10) ;
SQL Error [42703]: ERROR: column "g" does not exist
I have checked the docks on subquery expressions and not sure if this
expected behaviour, although it doesn't appear so.
https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-IN
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, gurmokh.sangha@starlingbank.com
Subject: Re: BUG #17754: Subquery IN clause returns row matches where subquery is invalid
In-Reply-To: <17754-0c04a53e0078e5d6@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