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
