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

This looks like a bug that should have been fixed in 9.2.0.2. Check the execution plan to see if the subquery has been turned into a semi-join.

Possibly a /*+ no_unnest */ hint in the subquery would make the problem go away. But since you have a reproducible test case, you should post it as a TAR with Oracle.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005






<vistav20_at_yahoo.com> wrote in message 
news:1110535066.181395.36550_at_g14g2000cwa.googlegroups.com...


> 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.

>