Re: IN/NOT IN Parsing

"Preston" <dontwantany_at_nowhere.invalid> writes:

> Preston wrote:
>
>> I'd never noticed until today that Oracle doesn't check if columns
>> used in an in/not in clause are valid. Is this common knowledge that
>> I've somehow missed out on?
>>
>> Strikes me as being very dangerous as you could typo a column name &
>> never know about it - at least not until your data was already screwed
>> up.
>>
>>
>> create table t1 (id1 number);
>>
>> create table t2 (id1 number, id2 number);
>>
>> insert into t1 values(1);
>>
>> insert into t2 values(1,1);
>> insert into t2 values(1,2);
>>
>> commit;
>>
>> select * from t2
>> where id2 in (select id2 from t1);
>
> Sorry, obviously should've said doesn't /always/ check.
>
> It appears that if the column name in the sub-query is the same as a
> column name in the table being selected from, it doesn't check. So:
>
> select * from t2
> where id2 in (select id3 from t1);
>
> gives the error 'Invalid Identifier' as you'd expect.

Yep, I think this is correct (though possibly dangerous) behavior. What I think is happening is that the parse is assuminig the column you are referring to in the subquery is the column from the main query table i.e t2.id2.

For this reason and to help keep code clear, I always prefix column names in subqueries with the table or table alias. I think this avoids possible unexpected behavior from typos and it makes it less likely that someone later on will misinterpret the statement when performing maintenance.

I also don't think this would be something that cold easily be fixed. It is legal to reference columns fro the outer table in a subquery and therefore, the parser can't detect that what you have done is an error. The only real solution would be to require that all columns referenced in a subquery must have a table prefix if they are not in the table referenced in the subquery from clause. However, this would break a hell of a lot of code!

Tim

-- 
tcross (at) rapttech dot com dot au