Query returns two different results in 8i and 9i

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. Received on Fri Mar 11 2005 - 03:57:46 CST