Обсуждение: BUG #13391: when use in/= & subquery, non exists column can elected.
The following bug has been logged on the website: Bug reference: 13391 Logged by: digoal Email address: digoal@126.com PostgreSQL version: 9.4.2 Operating system: CentOS 6.x x64 Description: when use in/= & subquery, non exists column can elected. for exp: postgres=# create table table1 (c1 int, c2 int, c3 int, c4 text); CREATE TABLE postgres=# create table table2 (id int); CREATE TABLE postgres=# insert into table1 values (1,2,3,'test'); INSERT 0 1 postgres=# insert into table1 values (2,3,4,'test'); INSERT 0 1 postgres=# insert into table2 values (100); INSERT 0 1 c1 column not exists in table2, but when table2 in subquery, it can elected? postgres=# explain (analyze,verbose) select * from table1 where c1 = (select c1 from table2 limit 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Seq Scan on public.table1 (cost=0.00..116.59 rows=23 width=44) (actual time=0.020..0.023 rows=2 loops=1) Output: table1.c1, table1.c2, table1.c3, table1.c4 Filter: (table1.c1 = (SubPlan 1)) SubPlan 1 -> Limit (cost=0.00..0.01 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=2) Output: (table1.c1) -> Seq Scan on public.table2 (cost=0.00..112.30 rows=10230 width=0) (actual time=0.002..0.002 rows=1 loops=2) Output: table1.c1 Planning time: 0.169 ms Execution time: 0.070 ms (10 rows) postgres=# explain (analyze,verbose) select * from table1 where c1 in (select c1 from table2 limit 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Seq Scan on public.table1 (cost=0.00..97.34 rows=2270 width=44) (actual time=0.018..0.022 rows=2 loops=1) Output: table1.c1, table1.c2, table1.c3, table1.c4 Filter: (SubPlan 1) SubPlan 1 -> Limit (cost=0.00..0.01 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=2) Output: (table1.c1) -> Seq Scan on public.table2 (cost=0.00..112.30 rows=10230 width=0) (actual time=0.002..0.002 rows=1 loops=2) Output: table1.c1 Planning time: 0.102 ms Execution time: 0.069 ms (10 rows) postgres=# select * from table1 where c1 in (select c1 from table2 limit 1); c1 | c2 | c3 | c4 ----+----+----+------ 1 | 2 | 3 | test 2 | 3 | 4 | test (2 rows) postgres=# select * from table1 where c1 = (select c1 from table2 limit 1); c1 | c2 | c3 | c4 ----+----+----+------ 1 | 2 | 3 | test 2 | 3 | 4 | test (2 rows) postgres=# explain select c1 from table2 limit 1; ERROR: column "c1" does not exist LINE 1: explain select c1 from table2 limit 1; and there is also can execute with catalog. postgres=# explain select * from pg_tables where tablename in (select tablename from tt); QUERY PLAN ---------------------------------------------------------------------------------- Nested Loop Left Join (cost=0.00..32052.66 rows=51 width=199) Join Filter: (t.oid = c.reltablespace) -> Nested Loop Left Join (cost=0.00..32049.20 rows=51 width=139) Join Filter: (n.oid = c.relnamespace) -> Seq Scan on pg_class c (cost=0.00..32040.43 rows=51 width=79) Filter: ((relkind = 'r'::"char") AND (SubPlan 1)) SubPlan 1 -> Seq Scan on tt (cost=0.00..106.30 rows=9630 width=0) -> Materialize (cost=0.00..1.15 rows=10 width=68) -> Seq Scan on pg_namespace n (cost=0.00..1.10 rows=10 width=68) -> Materialize (cost=0.00..1.04 rows=3 width=68) -> Seq Scan on pg_tablespace t (cost=0.00..1.03 rows=3 width=68) (12 rows) postgres=# explain select tablename from tt; ERROR: column "tablename" does not exist LINE 1: explain select tablename from tt; ^ postgres=# \d tt Table "public.tt" Column | Type | Modifiers --------+---------+----------- id | integer | ^
Re: BUG #13391: when use in/= & subquery, non exists column can elected.
От
"David G. Johnston"
Дата:
On Tuesday, June 2, 2015, <digoal@126.com> wrote: > The following bug has been logged on the website: > > Bug reference: 13391 > Logged by: digoal > Email address: digoal@126.com <javascript:;> > PostgreSQL version: 9.4.2 > Operating system: CentOS 6.x x64 > Description: > > when use in/= & subquery, non exists column can elected. > for exp: > > postgres=# explain (analyze,verbose) select * from table1 where c1 = > (select > c1 from table2 limit 1); > > This is a correlated subquery, not a bug. David J.