Search Postgresql Archives

Query should have failed, but didn't?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux