Re: Query should have failed, but didn't?

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