Sokrates on Oracle

Yet Another Elementary SQL Bug

Posted by Matthias Rogel on 20. February 2014


sokrates@12.1 > select banner from v$version;

Oracle Database 12c Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE	Production
TNS for Linux: Version - Production
NLSRTL Version - Production

sokrates@12.1 > select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

sokrates@12.1 > !echo $NLS_LANG

Setup is

create table t ( r varchar2(1 char));
insert into t values('£');

alter session set nls_length_semantics=BYTE;
alter table t add( v as ( cast(r as varchar2(1))));

alter session set nls_length_semantics=CHAR;
create index t_idx on t(v);

exec dbms_stats.set_table_stats(null, 'T', numrows => 1e7)

We get different result for count(v) when using length-semantics BYTE, depending if a table scan or an index scan is used:

sokrates@12.1 > alter session set nls_length_semantics=BYTE;

Session altered.

sokrates@12.1 > select /* index full scan used */ count(v) from t;


sokrates@12.1 > select /* full table scan used */ count(*), count(v) from t;

---------- ----------
	 1	    0

Same behaviour was observed on 11.2

4 Responses to “Yet Another Elementary SQL Bug”

  1. Dom Brooks said

    Interesting extension from yesterday.

    This is a virtual column bug – it has to be because the result differs based on access path.

    But it does sort of border on the user error again – you should be explicit about BYTE vs CHAR in the expressions.

    But surely the virtual column expression should enforce one or other and not leave it up to the session?

    The point about virtual columns is that the value of the expression is not stored in the table so is evaluated at runtime – hence the 0 in the second select.

    The index has to store the value of the expression.

    I hope you’re raising these with Oracle Support.

    In my opinion there should either be an error raised when you try to create such a non-deterministic VC or it should rewrite it to use the byte semantics at create time.

  2. After some more weeks of discussions with Support the final answer is:
    it is a bug, but it is documented on
    that queries may behave incorrectly.

    “… If you reset these parameters at the session level, then queries using the function-based index may return incorrect results….”

    Oracle is not willing to fix it.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: