How 2 Bytes can fit into 1 Byte

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