Oracle 11g code defect with Select SUBSTR funtion

[bcaillet]

Hi all,

A few weeks ago I posted a question about a potential defect in Oracle 11g. Today I have more details on the error.

Please contact me if you are an Oracle Support engineer, I will like to follow up directly.

The first Query 1 returns the correct value
SELECT SUM(t.debitmst) 270
FROM
(
SELECT
SUBSTR(nls_lower(ledgeraccountnum), 1, 21) as account_substr,
SUM(debitmst) AS debitmst
FROM LEDGERBALANCESTRANSDELTA
GROUP BY ledgeraccountnum
) t;

The query 2 returns null (notice 21 became 20):
SELECT SUM(t.debitmst)
FROM
(
SELECT
SUBSTR(nls_lower(ledgeraccountnum), 1, 20) as account_substr,
SUM(debitmst) AS debitmst
FROM LEDGERBALANCESTRANSDELTA
GROUP BY ledgeraccountnum
) t;

This does not reproduce with Oracle 10g or SQL Server 2005/2008

Regards,
Bertrand

[debasisdas]

I can't find any issue in oracle 10g and nls_lower is not a SQL Server function.

[bcaillet]

The issue is only with ORA 11g and the script provided is ORA indeed, not an SQL Server function.

[debasisdas]

Please check for data data consistency across different versions of database and then re run the script.

[bcaillet]

Already done, data is the same.

My request is to engage officially with Oracle Support team, let me know if you can help.

Regards,
Bertrand

[bcaillet]

Issue found :
http://forums.oracle.com/forums/thre...941753#3941753