On 03/01/2011 07:50 PM, Royce Ausburn wrote:
Hi all, Got an odd one. test=# select version(); version ------------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 8.4.5 on i386-apple-darwin, compiled by GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370), 32-bit (1 row) test=# create temp table test1 (something integer); CREATE TABLE test=# create temp table test2 (id1 integer, charge numeric); CREATE TABLE test=# select * from test2 where id1 in (select id1 from test1) and charge=0.05; id1 | charge -----+-------- (0 rows) Notice the last query's subselect is selecting "id1" from test1, but there's no such column on id1. Postgres correctly fails when you do: test=# select id1 from test1; ERROR: column "id1" does not exist LINE 1: select id1 from test1; The plan for the query that I think should have failed: test=# explain select * from test2 where id1 in (select id1 from test1) and charge=0.05; QUERY PLAN ----------------------------------------------------------------- Seq Scan on test2 (cost=0.00..24628.45 rows=3 width=36) Filter: ((charge = 0.05) AND (SubPlan 1)) SubPlan 1 -> Seq Scan on test1 (cost=0.00..34.00 rows=2400 width=0) (4 rows) Notice the sub plan doesn't actually cause any filtering. This produces incorrect results when there's data in the table: test=# insert into test1 select 5; INSERT 0 1 test=# insert into test2 select 10, 70; INSERT 0 1 test=# select * from test2 where id1 in (select id1 from test1) and charge=70; id1 | charge -----+-------- 10 | 70 (1 row) test=# explain select * from test2 where id1 in (select id1 from test1) and charge=70; QUERY PLAN ----------------------------------------------------------------- Seq Scan on test2 (cost=0.00..24628.45 rows=3 width=36) Filter: ((charge = 70::numeric) AND (SubPlan 1)) SubPlan 1 -> Seq Scan on test1 (cost=0.00..34.00 rows=2400 width=0) (4 rows) Am I correct in thinking this is a bug in pg? --Royce --Royce Chief Engineer @ Inomial 03 9999 3125 0417 954 640
Its getting id1 from the parent table. (test2) You can use fields from the parent table in subselects. try this and it'll complain: select * from test2 where id1 in (select junk from test1) and charge=70; try: select * from test2 where id1 in (select something from test1 where something = id1) and charge=70; -Andy -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
