Re: Apparently anomalous behaviour with a subquery - has anyone seen this?

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 9 Jul 2002 20:43:47 -0500
Message-ID: <uelec2nmx.fsf@hotpop.com>

On Tue, 09 Jul 2002, dmorgan_at_exesolutions.com wrote:
> Paul Brewer wrote:
>

>> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
>> news:3D2B4AEA.EF7D9D1_at_exesolutions.com...
>> > And your concept of what a correlated sub-query should do is ...?
>> >
>> I didn't find this post particularly helpful.
>> Still looking for suggestions.
>>
>> Thanks,
>> Paul

>
> You weren't asking for help. You were wishing Oracle would change a
> very standard SQL behavior that has, as far as I know, not caused any
> problems for at least a million other developers.
>
> I was trying to point out that were your suggestion to be taken it
> would require Oracle to break correlated subquerys.

How is "select count(*) from emp where empno in (select empno from dept);" a correlated subquery? Are you saying all subqueries are correlated?

> I am sorry if you didn't catch that. The query did exactly what you
> asked it to do. Nothing more. Nothing less.

I don't know. I'm getting an error.

    Table Name: t1

     Name					   Null?    Type
     ----------------------------------------- -------- -----------
     ID						    NUMBER

    Table Name: t2
     Name					   Null?    Type
     ----------------------------------------- -------- -----------
     ID						    NUMBER

    SQL>select count(*) from t1 where id in (select var from t2);     select count(*) from t1 where id in (select var from t2)

                                                *

    ERROR at line 1:
    ORA-00904: invalid column name

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

      COUNT(*)

---

             1

My sqlplus session says:

    SQL*Plus: Release 8.1.7.0.0 - Production on Tue Jul 9 21:37:33 2002

    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production     With the Partitioning option
    JServer Release 8.1.7.0.0 - Production

I believe its on a Linux.

I then tried the same thing on my W2K box and got the same behaviour.

    SQL*Plus: Release 8.1.7.0.0 - Production on Tue Jul 9 21:47:51 2002

    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production     With the Partitioning option
    JServer Release 8.1.7.0.0 - Production

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.

Received on Tue Jul 09 2002 - 20:43:47 CDT