Re: IN/NOT IN Parsing

"Preston" <dontwantany_at_nowhere.invalid> schreef in bericht news:6mdli1Fft4rbU1_at_mid.individual.net...
| 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.
|
| --
| Preston

I've seen this behaviour too, which was indeed a typo in a standard application. Only after some changes to the query we noticed the error. Always prefix column names with their table aliases to avoid this.

If you use

select * from t2
where id2 in (select id2 from t1);

and id2 is not a column of t1, it will use the value for id2 of t2, which is syntactically and semantically correct, like select 'ABCD' from t1 (which will return 'ABCD' for each row of t1).

It's actually the same as
select * from t2
where id2 in (select t2.id2 from t1); (Always true...)

Shakespeare Received on Fri Oct 24 2008 - 07:31:21 CDT