Thread: Query should have failed, but didn't?
Hi all,
--Royce
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
Chief Engineer @ Inomial
03 9999 3125
0417 954 640
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. build5370), 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 correctlyfails 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
This is not a bug; given your test queries whenever you reference id1 you are ALWAYS referencing the column id1 in table test2. >>test=# select * from test2 where id1 in (select id1 from test1) and charge=70; >> id1 | charge >>-----+-------- >> 10 | 70 >> (1 row) Hint: Consider the results of: SELECT literal FROM table1; AND SELECT t1.*, (SELECT t2.col2 FROM t2 WHERE t2.id = t1.id) FROM t1; David J.
On 02/03/2011, at 2:16 PM, Andy Colson wrote: > 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; Oh! Of course! What a fool. Thanks =) --Royce