Re: IN/NOT IN Parsing

Shakespeare wrote:
> "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...)
>

Yep. Variation on a theme:

SQL> select * from v$version;

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

SQL> create table t1 (id1 number, id2 number);  

Table created.

SQL> create table t2 (id1 number, id2 number);

Table created.

SQL> insert into t1 values(1,3);

1 row created.

SQL> insert into t2 values(1,1);

1 row created.

SQL> insert into t2 values(1,2);

1 row created.

SQL> commit;

Commit complete.  

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

no rows selected

SQL> alter table t1 drop column id2;

Table altered.

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

        ID1 ID2

• ----------

          1 1

          1 2

To OP: Moral: Ensure that SQL is unambiguous, as the Bard said.

Palooka Received on Fri Oct 24 2008 - 17:29:24 CDT