Sokrates on Oracle

«
»

Yet Another Elementary SQL Bug

Posted by Matthias Rogel on 20. February 2014

Environment

sokrates@12.1 > select banner from v$version;

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

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

VALUE
--------------------------------------------------------------------------------
AL32UTF8
sokrates@12.1 > !echo $NLS_LANG
american_america.UTF8

Setup is

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

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;

  COUNT(V)
----------
	 1

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

  COUNT(*)   COUNT(V)
---------- ----------
	 1	    0

Same behaviour was observed on 11.2

This entry was posted on 20. February 2014 at 22:51 and is filed under 11.2, 12c, Bug, sql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “Yet Another Elementary SQL Bug”

  1. Dom Brooks said

    21. February 2014 at 11:25

    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.

    Reply

  2. Matthias Rogel said

    3. July 2014 at 16:26

    After some more weeks of discussions with Support the final answer is:
    it is a bug, but it is documented on
    http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5012.htm#SQLRF53993
    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.

    Reply

Leave a comment

«
»