Обсуждение: BUG #17754: Subquery IN clause returns row matches where subquery is invalid
BUG #17754: Subquery IN clause returns row matches where subquery is invalid
От
PG Bug reporting form
Дата:
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
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_using_the_wrong_name_in_a_subquery.3F regards, tom lane