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:
Notice the 6 last digits of HEXTORAW : 61 20 00 -> 61='a', 20=' ' *(space)*, 00=end
And the second one:
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...
