Potential regression with Oracle 11g in Select Sum

[bcaillet]

Hi folks,

I need your expertise with Oracle. My application is using both SQL and Oracle. When testing the new release of Oracle 11g, one test case is failing and the following code is being regressed in ORA 11g from ORA 10g.

Does anybody if that's a known issue?

The code is

select  sum(DEBITMST) from (SELECT SUM(A.DEBITMST) AS DEBITMST,
SUM(A.CREDITMST) AS CREDITMST,
SUM(A.DEBITOPRMST) AS DEBITOPRMST,
SUM(A.CREDITOPRMST) AS CREDITOPRMST,
SUM(A.DEBITTAXMST) AS DEBITTAXMST,
SUM(A.CREDITTAXMST) AS CREDITTAXMST,
SUM(A.DEBITMSTSECOND) AS DEBITMSTSECOND,
SUM(A.CREDITMSTSECOND) AS CREDITMSTSECOND,
SUM(A.DEBITOPRMSTSECOND) AS DEBITOPRMSTSECOND,
SUM(A.CREDITOPRMSTSECOND) AS CREDITOPRMSTSECOND,
SUM(A.DEBITTAXMSTSECOND) AS DEBITTAXMSTSECOND,
SUM(A.CREDITTAXMSTSECOND) AS CREDITTAXMSTSECOND,
SUM(A.QTY) AS QTY,
SUBSTR(NLS_LOWER(A.ACCOUNTNUM),1,41) AS ACCOUNTNUM,
SUBSTR(NLS_LOWER(A.DIMENSION),1,21) AS DIMENSION,
SUBSTR(NLS_LOWER(A.DIMENSION2_),1,21) AS DIMENSION2_,
SUBSTR(NLS_LOWER(A.DIMENSION3_),1,21) AS DIMENSION3_,
A.TRANSDATE AS TRANSDATE,
A.PERIODCODE AS PERIODCODE,
A.SYSTEMGENERATEDULTIMO AS SYSTEMGENERATEDULTIMO,
SUBSTR(NLS_LOWER(A.DATAAREAID),1,7) AS DATAAREAID,
1010 AS RECID 
FROM dbo.LEDGERBALANCESDIMTRANS A 
GROUP BY 
SUBSTR(NLS_LOWER(A.ACCOUNTNUM),1,41),
SUBSTR(NLS_LOWER(A.DIMENSION),1,21),
SUBSTR(NLS_LOWER(A.DIMENSION2_),1,21),
SUBSTR(NLS_LOWER(A.DIMENSION3_),1,21),
A.TRANSDATE,
A.PERIODCODE,
A.SYSTEMGENERATEDULTIMO,
SUBSTR(NLS_LOWER(A.DATAAREAID),1,7));

Regards,
Bertrand

[debasisdas]

what exactly is the error message ?

[bcaillet]

The query returns null with Oracle 11g, not in Oracle 10g and SQL Server.

[OraMaster]

Hi
Please make sure table in both 10g and 11g are in sync wrt data.