Oracle 9i query results differ from Oracle 8i! Is Oracle 9i reliable when using complex queries with analytic functions? This is very serious question!

From: Sergey Adamenko <adamenko_usenet_at_ukr.net>
Date: 4 Mar 2005 04:49:00 -0800
Message-ID: <5c805884.0503040449.71498d40@posting.google.com>

Hi, all!

FOREWORD

---

We develop document management system (in other words, docflow) and most of our clients have Oracle 8i. But time is going on and the part of clients with Oracle 9i is growing rapidly. So we decided to upgrade our primary development and test database to Oracle 9.2.0.5 EE/SE.
Recently we discovered that results some of our complex report differ from the results in 8i. The report query is rather complicated. It has several nested inline views, rollup and many joins. It took me some time to create a simplified test for reproducing the problem. So here it is.

PROBLEM

---

The query listed below gives different result when executed on Oracle 8.1.7.4 EE and Oracle 9.2.0.5 EE. Both are running under MS Windows OS.
Q#1:

SELECT
  dept.dname,
  rlp_join.rlp_name,
  rlp_join.s_sal
FROM (SELECT

       dept.dname,
       rlp.dname rlp_name,
       rlp.s_sal
      FROM 
        (
          SELECT 
            jn.dname,
            SUM (emp.sal) s_sal
          FROM 
            (
              SELECT 
                dept.dname,
                emp.ename,
                emp.sal sal
              FROM 
                emp,
                dept
              WHERE 
                emp.deptno = dept.deptno) jn,
                emp
            WHERE 
              jn.ename = emp.ename
            GROUP BY ROLLUP (jn.dname)
            ) rlp,
             dept
      WHERE 
        dept.dname = rlp.dname) rlp_join,
        dept

WHERE
  dept.dname = rlp_join.dname(+) AND
  rlp_join.dname IS NULL

Oracle 9.2.0.5 EE dives 4 rows.
Oracle 8.1.7.4 EE dives just one row which is RIGHT! Listing of the tests is included at the bottom of this message.

HOW TO REPROCUDE THE PROBLEM

---

1. You should have access Oracle 8i and Oracle 9.2.x.x;
2. Create a new well-known SCOTT scheme;
3. Execute query Q#1 (listed above) on 8i and 9i compare the results.

Here are some additional facts:
1. When the keyword "ROLLUP"; is excluded from the query;

   results are the same (1 row);
2. When RLP_JOIN inline view is replaced with an equivalent table (CTAS for it); results are the same.

I've heard of a problem with merging views in former versions of 9i. AFAIK it could lead to an error. But it was fixed in 9.2.0.4, BICBW. I have also tried the following:

1. Set _COMPLEX_VIEW_MERGING to FALSE;
2. Use NO_MERGE hint the query.
3. Setting COMPATIBLE.

None of them were helpful. In the 3d case database startup failed with
ORA-00402: database changes by release 9.2.0.0.0 cannot be used by release 8.1.7.0.0;
ORA-00405: compatibility type "Locally Managed SYSTEM tablespace"

I'LL BE EXTREMELY GRATEFUL

---

1. if someone will prove or disprove my results (please include version and platform information);
2. if someone will give a good advice or ever solution to this problem.

Best regards,
Sergey Adamenko.
Senior Oracle DBA.
Kyiv, Ukraine.
E-mail: wadamenko_wusenet_at_wukr.net (remove 'w' from E-mail)

---

TEST LISTING
[Oracle 9i]
SQL>
SQL> SELECT * FROM V$VERSION; BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for 32-bit Windows: Version 9.2.0.5.0 - Production NLSRTL Version 9.2.0.5.0 - Production

SQL> 
SQL> 
SQL> SELECT

  2 dept.dname,
  3 rlp_join.rlp_name,
  4 rlp_join.s_sal
  5 FROM (SELECT

  6          dept.dname,
  7          rlp.dname rlp_name,
  8          rlp.s_sal
  9        FROM (SELECT
 10                jn.dname,
 11                SUM (emp.sal) s_sal
 12              FROM (SELECT
 13                      dept.dname,
 14                      emp.ename,
 15                      emp.sal sal
 16                    FROM emp,
 17                      dept
 18                    WHERE emp.deptno = dept.deptno) jn,
 19                emp
 20              WHERE jn.ename = emp.ename
 21              GROUP BY ROLLUP (jn.dname)) rlp,
 22          dept
 23        WHERE dept.dname = rlp.dname) rlp_join,

 24 dept
 25 WHERE dept.dname = rlp_join.dname(+) AND  26 rlp_join.dname IS NULL;

DNAME RLP_NAME S_SAL -------------- -------------- ---------- ACCOUNTING
OPERATIONS
RESEARCH
SALES SQL> EXIT [Oracle 8i]
I SQL>
SQL> SELECT * FROM V$VERSION; BANNER

---

Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.2.1 Production
TNS for 32-bit Windows: Version 8.1.7.4.0 - Production NLSRTL Version 3.4.1.0.0 - Production

SQL> 
SQL> 
SQL> SELECT

  2 dept.dname,
  3 rlp_join.rlp_name,
  4 rlp_join.s_sal
  5 FROM (SELECT

  6          dept.dname,
  7          rlp.dname rlp_name,
  8          rlp.s_sal
  9        FROM (SELECT
 10                jn.dname,
 11                SUM (emp.sal) s_sal
 12              FROM (SELECT
 13                      dept.dname,
 14                      emp.ename,
 15                      emp.sal sal
 16                    FROM emp,
 17                      dept
 18                    WHERE emp.deptno = dept.deptno) jn,
 19                emp
 20              WHERE jn.ename = emp.ename
 21              GROUP BY ROLLUP (jn.dname)) rlp,
 22          dept
 23        WHERE dept.dname = rlp.dname) rlp_join,

 24 dept
 25 WHERE dept.dname = rlp_join.dname(+) AND  26 rlp_join.dname IS NULL;

DNAME RLP_NAME S_SAL -------------- -------------- ---------- OPERATIONS SQL> exit Received on Fri Mar 04 2005 - 06:49:00 CST