Sokrates on Oracle

Archive for February, 2014

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

Advertisements

Posted in 11.2, 12c, Bug, sql | 4 Comments »

How 2 Bytes can fit into 1 Byte

Posted by Matthias Rogel on 20. February 2014

Chris Saxon posted a nice quiz regarding a pitfall when creating tables with VARCHAR2-columns: when you are not explicit in specifying the length-semantics ( CHAR or BYTE ), a session parameter, which may vary, is used.

This is a short follow-up of his story, which shows more pitfalls when creating views and selecting from those.
We end up in a structure which can – by definition – contain only 1 byte, but actually shows containing two bytes !

sokrates@11.2 > alter session set nls_length_semantics=CHAR;

Session altered.

sokrates@11.2 > create table i_can_insert (
  2    into_this varchar2(1)
  3  );

Table created.

sokrates@11.2 > insert into i_can_insert (into_this)
  2  values ('£');

1 row created.

sokrates@11.2 > create view i_can_select (from_that)
  2  as
  3  select cast(into_this as varchar2(1))
  4  from i_can_insert;

View created.

sokrates@11.2 > describe i_can_insert
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 INTO_THIS                                          VARCHAR2(1)

sokrates@11.2 > describe i_can_select
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FROM_THAT                                          VARCHAR2(1)

sokrates@11.2 > select * from i_can_select;

FROM_THAT
----------
£

sokrates@11.2 > REM now we change the length semantics ...
sokrates@11.2 > alter session set nls_length_semantics=BYTE;

Session altered.

sokrates@11.2 > REM note that the output of describe changes !
sokrates@11.2 > describe i_can_insert
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 INTO_THIS                                          VARCHAR2(1 CHAR)

sokrates@11.2 > describe i_can_select
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FROM_THAT                                          VARCHAR2(1 CHAR)

sokrates@11.2 > create view can_i_select (from_that)
  2  as
  3  select cast(into_this as varchar2(1))
  4  from i_can_insert;

View created.

sokrates@11.2 > describe can_i_select
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FROM_THAT                                          VARCHAR2(1)

sokrates@11.2 > REM now, can I select ?
sokrates@11.2 > set null nada
sokrates@11.2 > select * from can_i_select;

FROM_THAT
----------
nada

sokrates@11.2 > REM no I cannot, output would be too long in my current semantics, so it is trimmed ( nothing left )
sokrates@11.2 >
sokrates@11.2 > REM now, can I select from i_can_select ?
sokrates@11.2 > select * from i_can_select;

FROM_THAT
----------
nada

sokrates@11.2 > REM no
sokrates@11.2 > select * from i_can_insert;

INTO_THIS
----------
£

sokrates@11.2 > REM but still from the table itself, I can select !
sokrates@11.2 >
sokrates@11.2 > REM let us change back the semantics
sokrates@11.2 > alter session set nls_length_semantics=CHAR;

Session altered.

sokrates@11.2 > describe can_i_select
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FROM_THAT                                          VARCHAR2(1 BYTE)

sokrates@11.2 > select * from can_i_select;

FROM_THAT
----------
£

sokrates@11.2 > select lengthb(from_that) from can_i_select;

LENGTHB(FROM_THAT)
------------------
                 2

So, CAN_I_SELECT.FROM_THAT can only hold 1 byte, but the byte-length of its content is 2 !
-> looks like a bug to me.

Note, that USER_VIEWS.TEXT shows the same for both views, even DBMS_METADATA is not explicit , only COLS shows the difference

sokrates@11.2 > select text from user_views where view_name='CAN_I_SELECT';

TEXT
--------------------------------------------------------------------------------
select cast(into_this as varchar2(1))
from i_can_insert


sokrates@11.2 > select text from user_views where view_name='I_CAN_SELECT';

TEXT
--------------------------------------------------------------------------------
select cast(into_this as varchar2(1))
from i_can_insert


sokrates@11.2 > select dbms_metadata.get_ddl('VIEW', 'CAN_I_SELECT') from dual;

DBMS_METADATA.GET_DDL('VIEW','CAN_I_SELECT')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SOKRATES"."CAN_I_SELECT" ("FROM_THAT") AS
  select cast(into_this as varchar2(1))
from i_can_insert


sokrates@11.2 > select dbms_metadata.get_ddl('VIEW', 'I_CAN_SELECT') from dual;

DBMS_METADATA.GET_DDL('VIEW','I_CAN_SELECT')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SOKRATES"."I_CAN_SELECT" ("FROM_THAT") AS
  select cast(into_this as varchar2(1))
from i_can_insert


sokrates@11.2 > select table_name, column_name, data_length from cols where table_name in ('I_CAN_SELECT', 'CAN_I_SELECT');

TABLE_NAME                     COLUMN_NAME                    DATA_LENGTH
------------------------------ ------------------------------ -----------
CAN_I_SELECT                   FROM_THAT                                1
I_CAN_SELECT                   FROM_THAT                                4

Posted in sql | 4 Comments »