Oracle 12.1 string literal on varchar2 with trailing space

Question

I'm having a very specific issue with Oracle 12.1 concerning the handling of string literal ending with space in SQL WHERE clause applied on a VARCHAR2(2500) column (named 'NOTES').

As I need Case-and-Accent-Insensitive string comparison, I alter the NLS_COMP and NLS_SORT in a Logon Database trigger:

ALTER SESSION SET NLS_COMP='LINGUISTIC'; 
ALTER SESSION SET NLS_SORT='BINARY_AI';

Then, if i try this (notice the ' ' space at the end of 'cholera', it is needed as the space is present in the table data, and I can't change it as I only have read-only access to the table):

SELECT NOTES FROM DECCODESIDC WHERE NOTES='cholera ';

So far, so good, it returns the only row matching the criteria ('Choléra ')

But if i create a View based on the table, and I apply the same criteria, it doesn't return anything:

SELECT NOTES FROM (SELECT NOTES FROM DECCODESIDC) WHERE NOTES='cholera ';

I noticed that the explain plan is different between the two queries

Here is the first query explain plan:

first query explain plan
Notice the 6 last digits of HEXTORAW : 61 20 00 -> 61='a', 20=' ' *(space)*, 00=end

And the second one:

second query explain plan
Notice the 6 last digits of HEXTORAW : 72 61 00 -> 72='r', 61='a', 00=end

As we can see, the HEXTORAW function automatically added by Oracle remove the trailing space in the second query, but not in the first...

I'm aware that string literals are considered as CHAR by Oracle and are subject to space padding, but in this case the string literal is compared against a VARCHAR2 type column... and it doesn't explain why the execution plans are differents ...

Am I missing something or is it a bug in Oracle?

Benoit

ps: the fact is that I don't write such queries by hand, but rely on Entity Framework with Oracle Managed Drivers, so I don't have so much options concerning query rewriting :(

ps2: As a temporary workaround, I added a call to the TRIM oracle function on every VARCHAR2 column in my View, but it is suboptimal in term of performance...

Answer 1

For anyone facing the same problem: I applied patch bundle 12.1.0.2.170117 and everything seems to be ok now... :/

Answer 2

What happens if you explicity cast the string character to VARCHAR2 as in ... = CAST('yourstring' AS VARCHAR2). Not a solution, just something else to understand.

Frank

• BenoitM: Sorry, I did not mentioned that during my tests, I tried to manually cast the string literal to a VARCHAR2, and that it works on both queries. Unfortunately, Entity Framework is used and it creates the SQL with string literals... :/