Thread: Subselect strange behaviour - bug?

I have two tables, 'configured' like this:

melem=# \d t1          Table "public.t1"Column |       Type        | Modifiers
--------+-------------------+-----------id     | integer           |value  | character varying |

melem=# \d t2          Table "public.t2"Column |       Type        | Modifiers
--------+-------------------+-----------id1    | integer           |value  | character varying |


And here is the data from both tables:

melem=# select * from t1;id | value
----+------- 1 | 1 2 | 2 3 | 3
(3 rows)

melem=# select * from t2;id1 | value
-----+-------  1 | 1  2 | 2  3 | 3  4 | 4
(4 rows)


And here is the 'problematic' query:

melem=# select * from t2 where id1 in (select id1 from t1);id1 | value
-----+-------  1 | 1  2 | 2  3 | 3  4 | 4
(4 rows)

I guess postgres should tell me that column name id1 is nonexistant in
table t1.

Now, if I change subselect to select correct column name, everything is ok:

melem=# select * from t2 where id1 in (select id from t1);id1 | value
-----+-------  1 | 1  2 | 2  3 | 3
(3 rows)


I have found out that this 'error' pops up only if the columns in both
subselect query and the 'super'select query are the same. For instance:

melem=# select * from t2 where id1 in (select id2 from t1);
ERROR:  column "id2" does not exist
LINE 1: select * from t2 where id1 in (select id2 from t1);                                             ^
melem=#


I have tested this on postgres 8.3.0, 8.2.6 and 8.2.4, and the results
are the same. I donwloaded the sources for three mentioned versions of
postgres, and built it on Linux 2.6.22-14 (Ubuntu 7.10) using gcc 4.1.3,
as well as on Debian stable (Linux 2.6.22.1 with gcc 4.1.2).
Mario

Mario Splivalo <mario.splivalo@megafon.hr> writes:
> And here is the 'problematic' query:
> melem=# select * from t2 where id1 in (select id1 from t1);

> I guess postgres should tell me that column name id1 is nonexistant in
> table t1.

No, it shouldn't, because that's a perfectly legal outer reference;
that is, what you wrote is equivalent toselect * from t2 where id1 in (select t2.id1 from t1);
        regards, tom lane

Tom Lane wrote:
> Mario Splivalo <mario.splivalo@megafon.hr> writes:
>   
>> And here is the 'problematic' query:
>> melem=# select * from t2 where id1 in (select id1 from t1);
>>     
>> I guess postgres should tell me that column name id1 is nonexistant in
>> table t1.
>>     
>
> No, it shouldn't, because that's a perfectly legal outer reference;
> that is, what you wrote is equivalent to
>     select * from t2 where id1 in (select t2.id1 from t1);
>
>   
Huh, you're right. Appologies for the noise :)
   Mario