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_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:
And the second one:
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?
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...