Re: 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: Hexathioorthooxalate <ruler_at_removemetoemail.clara.co.uk>
Date: Fri, 4 Mar 2005 14:00:36 -0000
Message-Id: <1109944823.28822.0@lotis.uk.clara.net>

Sergey, before I have a look at this further for you, could you ....

A few minutes ago I created the SCOTT schema using the script "utlsampl.sql" a few minutes ago. I get 4 rows on 10g on a winbox, which you say is wrong. I get 4 rows using 9.2, same machine. Please confirm you have used the <b>same</b> SQL script to generate the <b>same</b> SCOTT schema on both your 8i and 9i DB's. This will confirm you are executing your query against the <b>same</b> data and ruling out the obvious. If the problem persists, we can look into in more detail.
Regards
Hex

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
 10          (
 11            SELECT
 12              jn.dname,
 13              SUM (emp.sal) s_sal
 14            FROM
 15              (
 16                SELECT
 17                  dept.dname,
 18                  emp.ename,
 19                  emp.sal sal
 20                FROM
 21                  emp,
 22                  dept
 23                WHERE
 24                  emp.deptno = dept.deptno) jn,
 25                  emp
 26              WHERE
 27                jn.ename = emp.ename
 28              GROUP BY ROLLUP (jn.dname)
 29              ) rlp,
 30               dept
 31        WHERE
 32          dept.dname = rlp.dname) rlp_join,
 33          dept

 34 WHERE
 35 dept.dname = rlp_join.dname(+) AND  36 rlp_join.dname IS NULL
 37 ;

DNAME RLP_NAME S_SAL -------------- -------------- ---------- RESEARCH
SALES
ACCOUNTING
OPERATIONS SQL> SELECT
  2 *
  3 FROM V$VERSION; BANNER

---

Personal Oracle Database 10g Release 10.1.0.2.0 - Production PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production NLSRTL Version 10.1.0.2.0 - Production

SQL> SPOOL OFF "Sergey Adamenko" <adamenko_usenet_at_ukr.net> wrote in message news:5c805884.0503040449.71498d40_at_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 - 08:00:36 CST