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 next 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.
Re: Invalid results for Oracle 12c and 18c when changing NLS_SORT to BINARY_CI [message #677718 is a reply to message #677717] Wed, 09 October 2019 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 66722
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Have a look at MOS note 2390584.1: "Wrong Results with Case Insensitive Query When NLS_SORT=BINARY_CI And NLS_COMP=LINGUISTIC".
It says that it is due to Bug 27416997 - CASE INSENSITIVE QUERIES FAILING DUE TO NULL IS NOT NULL PREDICATE (check if you are in this case) which is fixed in 19c and gives some workarounds.

Re: Invalid results for Oracle 12c and 18c when changing NLS_SORT to BINARY_CI [message #677720 is a reply to message #677718] Wed, 09 October 2019 02:51 Go to previous messageGo to next message
charleschan
Messages: 4
Registered: October 2019
Junior Member
Hi Michel,

Apologies for the incorrect formatting. I'll take note of this.

Thanks a lot for your help! It indeed solved my issue.

To share the fix, I added the following lines as a workaround:

ALTER SESSION SET "_simple_view_merging" = FALSE;

What will be the effect of this?

[Updated on: Wed, 09 October 2019 03:53]

Report message to a moderator

Re: Invalid results for Oracle 12c and 18c when changing NLS_SORT to BINARY_CI [message #677725 is a reply to message #677720] Wed, 09 October 2019 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 66722
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It changes the execution plan and so workarounds the bug in the optimizer.

Please do NOT post MOS materials, they are copyrighted.

Re: Invalid results for Oracle 12c and 18c when changing NLS_SORT to BINARY_CI [message #677739 is a reply to message #677725] Thu, 10 October 2019 00:30 Go to previous message
charleschan
Messages: 4
Registered: October 2019
Junior Member
It indeed changed the execution plan. It does have a change in execution time/performance.

Thanks again for the help!
Previous Topic: Learning SQL
Next Topic: Oracle licensing
Goto Forum:
  


Current Time: Wed Dec 11 22:28:03 CST 2019