pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17754: Subquery IN clause returns row matches where subquery is invalid
2+ messages / 2 participants
[nested] [flat]
* BUG #17754: Subquery IN clause returns row matches where subquery is invalid
@ 2023-01-19 18:02 PG Bug reporting form <noreply@postgresql.org>
2023-01-19 19:42 ` Re: BUG #17754: Subquery IN clause returns row matches where subquery is invalid Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2023-01-19 18:02 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: gurmokh.sangha@starlingbank.com
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #17754: Subquery IN clause returns row matches where subquery is invalid
2023-01-19 18:02 BUG #17754: Subquery IN clause returns row matches where subquery is invalid PG Bug reporting form <noreply@postgresql.org>
@ 2023-01-19 19:42 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2023-01-19 19:42 UTC (permalink / raw)
To: gurmokh.sangha@starlingbank.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> 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.
Such a reference is called an "outer reference", and it's a required
feature in the SQL standard. The reason you get "true" is that the
expression
> where a in ( select a from btable where c = 10) ;
is basically reducing to "a = a".
https://wiki.postgresql.org/wiki/FAQ#Why_doesn.27t_PostgreSQL_report_a_column_not_found_error_when_u...
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-01-19 19:42 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-19 18:02 BUG #17754: Subquery IN clause returns row matches where subquery is invalid PG Bug reporting form <noreply@postgresql.org>
2023-01-19 19:42 ` Tom Lane <tgl@sss.pgh.pa.us>
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