Re: No error from subselect with invalid column name

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 19 May 1999 13:37:38 GMT
Message-ID: <3745bdfa.9732514@newshost.us.oracle.com>

A copy of this was sent to mcdonnem_at_my-dejanews.com (if that email address didn't require changing) On Wed, 19 May 1999 12:19:11 GMT, you wrote:

>I have been able to reproduce this under 7.3.2.3, 7.3.3.4, 7.3.4.2 and
>8.0.5 (32 Bit running under HP-UX 11). Has anyone ever done this or
>seen this or can explain this.
>
>create table tab1 (col1 number);
>create table tab2 (col2 number);
>select col1 from tab1 where col1 in (select col1 from tab2);
>
>The result will be no rows returned. I would have expected a 904 error
>indicating invalid column name since col1 does not exist in tab2.
>

Actually, as long as tab2 has at least 1 row -- it returns ALL records from tab1:

SQL> create table tab1 ( col1 number ); Table created.

SQL> create table tab2 ( col2 number ); Table created.

SQL> insert into tab1 values ( 1 );
1 row created.

SQL> insert into tab2 values ( 2 );
1 row created.

SQL> select col1 from tab1 where col1 in ( select col1 from tab2 );

      COL1

---

         1

You just ran a correlated subquery. Its like:

   for x in ( select * from tab1 )
   loop

        for y in ( select x.col1 col1 from tab2 )
        loop
             if ( y.col1 = x.col1 )
             then
                 KeepTheRow
             end if;
        end loop

   end loop

the subquery is being fired for each row in tab1 and you are selecting the 'constant' col1 from tab2 in the subquery.

Its quite normal and is the expected behaviour.

>
>--== Sent via Deja.com http://www.deja.com/ ==--
>---Share what you know. Learn what you don't.---

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed May 19 1999 - 08:37:38 CDT