Re: Query returns two different results in 8i and 9i

vistav20_at_yahoo.com wrote:

> Has anyone else experienced this...
> (We have tried it with 9i and 8i. The problem surfaced in 9i. That is,
> doesn't happen when logged into 8i)
>
>
> 1. Create a table on your "local" DB (i.e. the one you will log into
> to run the query). CREATE TABLE "XYZ"("ABC" NUMBER NOT NULL)
>
>
> 2. Populate it with a few values MAKING SURE that some of these values
> appear more than once in the table.
>
>
> 3. Now, create any identical copy of this table (with the data having
> some duplicate values in it) on a remote DB, defined in the DB
> connection on your local server.
>
>
> 4. Run the following two queries:
>
>
> A:
> Select ... from ...
> where ... in (select abc from xyz_at_dbConnection)
> B:
> Select ... from ...
> where ... in (select abc from xyz)
>
>
> When running the above two queries while logged into 8i, you get same
> results for both. But when logged into 9i, the one with dbConnection
> in the "in" list, returns one row for *every* value in the "in" list
> --> you end up with duplicates in your result set.
>
>
> Ofcourse you can fix it by putting "distinct" in the query, but why is
> it happening only when logged into 9i?
>
>
> When you do a query, you never worry abot having duplicate values in
> your "in" list. But with this, you have to!
>
>
> Any fix available for this?
>
>
> thanks.

9i can mean many things ... are you still using 9.2.0.1 or have you applied the patches to at least the 9.2.0.4 level?

I'd suggest only contact Oracle support if you have applied the patches and the problem still persists.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)