Home » Other » General » Invalid results for Oracle 12c and 18c when changing NLS_SORT to BINARY_CI (Oracle 12c)
Invalid results for Oracle 12c and 18c when changing NLS_SORT to BINARY_CI [message #677717] Tue, 08 October 2019 22:08 Go to previous message
charleschan
Messages: 4
Registered: October 2019
Junior Member
I have the following query which correctly returns records when I execute it via code or Oracle SQL Developer.

SELECT TABLE_T.COL_P,
1234 AS COL_C,
TABLE_T.COL_D,
SUM(SOME_COLUMN) Value
FROM TABLE_T
INNER JOIN TABLE_E E ON TABLE_T.COL_P = E.COL_P
AND TABLE_T.COL_C = E.COL_C
AND TABLE_T.COL_CC = E.COL_CC
AND TABLE_T.COL_CL = E.COL_CL
INNER JOIN TABLE_C C1 ON C1.COL_P = E.COL_P
AND C1.COL_C = E.COL_C
INNER JOIN TABLE_C C2 ON C2.COL_P = C1.COL_P
AND C2.COL_CX = C1.COL_CX
AND C2.COL_CY = C1.COL_CY
AND C2.COL_CZ = C1.COL_CZ
WHERE TABLE_T.COL_P = 'Some Text'
AND C2.COL_C = 1234
AND TABLE_T.COL_CL IN
(SELECT COL_CL
FROM TABLE_CL
WHERE COL_P = 'Some Text'
AND ((COL_CLTYPE = 'VALUE_A')
OR (COL_CLTYPE = 'VALUE_B')
OR (COL_CLTYPE = 'VALUE_C')
OR (COL_CLTYPE = 'VALUE_D')) )
GROUP BY TABLE_T.COL_P,
TABLE_T.COL_D

However, it fails to return records once I execute the following session commands:

ALTER SESSION SET NLS_COMP = LINGUISTIC;

ALTER SESSION SET NLS_SORT = BINARY_CI;

This problem only occurs when I'm running against an Oracle 12c or 18c database.

It works find with/without the session commands when running against an Oracle 12C R2 or 11g database.

I've already checked the Explain Plan for 12c/18c and 12cR2 and its creating the same plan.

I found out that by adding an ORDER BY clause to the query (ORDER BY TABLE_T.COL_D, it resolves the problem.

Any ideas on what might be causing this problem?

I know the ORDER BY solution works, but I'd like to know what the underlying cause is and if there's a better solution to it.
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: DB 21c
Next Topic: Oracle Service Bus Proxy Service Scheduler
Goto Forum:
  


Current Time: Fri Mar 29 04:23:01 CDT 2024