Recently, at a customer site running Oracle 18c (12.2.0.2), we observed a baffling behavior: certain queries that worked fine when the application used case sensitive mode stopped returning expected rows when the application switched to case insensitive mode. The application in question was Remedy and the symptom was obvious — a “Site” dropdown that showed 72 rows in case sensitive mode would be empty (or missing expected rows) in case insensitive mode.

What we saw

The SQL used to populate the dropdown (and similar SQL for Company, Region, Site Group) contained predicates that — when executed under case insensitive session settings — did not return rows that were expected. Here is a representative snippet of the predicate logic:

(TDB1.C1 = ' ') OR (' ' = ' ')
AND ((TDB1.C2 = ' ') OR (' ' = ' '))
AND ((TDB1.C7 = ' ') OR (' ' = ' '))
AND (TDB1.C13 = 0)
AND (TDB1.C18 = 1)
AND (TDB1.C7 = 1)
    

On the surface the predicate looks unusual and redundant — but the core problem was deeper: some internal optimizer/transformation behaviour changed under case insensitive mode, causing predicates involving NULL/empty semantics to be treated incorrectly. After digging, we discovered this matched Oracle Bug 27416997 (Metalink Doc ID 2390584.1).

How we fixed it for the customer

The bug is fixed in Oracle 19.1. For the customer (where an immediate version upgrade was not an option), we applied one of the documented session/system workarounds described in the Metalink note. Setting this hidden optimizer parameter resolved the issue immediately: If you have any login triggers then add this statement in it.

ALTER SESSION SET "_optimizer_generate_transitive_pred" = FALSE;
    

Important: Hidden parameters (those beginning with an underscore) must be handled with caution — they are undocumented for a reason and can have side effects. Always test changes in a non-production environment, and where possible consult Oracle Support before applying them broadly.

Recommended approach — short and practical

1. If you see rows missing only in case-insensitive mode, suspect optimizer predicate transformation issues (and specifically check for Bug 27416997 if your Oracle version is 12.2.0.2 / 18c).
2. Prefer application of official bug fixes — plan to upgrade to Oracle 19.1 or later where the bug is resolved.
3. As a short-term workaround, test the documented parameter change in a staging environment:

   ALTER SYSTEM SET "_optimizer_generate_transitive_pred" = FALSE SCOPE=BOTH;

   If you cannot set system scope, try session scope first (but remember scope and persistence implications).
4. Verify related SQL and predicates for NULL / empty string ambiguity and consider making predicate logic explicit in SQL (e.g. using IS NULL or non-ambiguous comparisons) to avoid surprises.
5. Open a support ticket with Oracle and reference the Metalink Doc ID 2390584.1 and Bug 27416997 — this helps get the official guidance and ensures your environment is supported.

Why this matters

Case insensitivity is a common requirement for user-facing applications. When an optimizer transformation or corner case causes predicates to be misapplied, the result is data missing from UI lists, reports, or filters — which looks like data corruption or application malfunction to end users. Quick workarounds can restore functionality, but the correct long-term fix is to apply the vendor patch or upgrade to a fixed release.

Final notes

If you encounter a similar issue, collect representative SQL, execution plans (both before and after the change), and session parameters. Those artifacts make troubleshooting much faster for DBAs and for Oracle Support. If you’d like, I can share a checklist of the exact traces/plan captures we used to diagnose this case.