Unable to select from a view which was created using 'CREATE VIEW (<column_name>)' if connection charset = utf8 and query returns [var]char

[pavel-zotov]

Consider following script:

set bail on;
shell del r:\temp\tmp4test.fdb 2>nul;
set blob all;
set names utf8; ------------------------------------------------------------------------------------------------------ [ 1 ]
--set names win1251;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';

-- case-a: declaring view without specifying [<full_column_list>]:
create view v_dual_nul_a as select null as implicit_col_name_for_null from rdb$database;
create view v_dual_num_a as select 123 as implicit_col_name_for_numeric from rdb$database;
create view v_dual_dts_a as select current_timestamp as implicit_col_name_for_timestamp from rdb$database;
create view v_dual_boo_a as select true as implicit_col_name_for_boolean from rdb$database;
create view v_dual_blb_a as select cast('bin' as blob sub_type text) as implicit_col_name_for_blob from rdb$database;
create view v_dual_txt_a as select 'TXT' as implicit_col_name_for_char from rdb$database;

-- case-b: declaring view __with__ specifying [<full_column_list>]:
create view v_dual_nul_b(explicit_col_name_for_null) as select null from rdb$database;
create view v_dual_num_b(explicit_col_name_for_numeric) as select 123 from rdb$database;
create view v_dual_dts_b(explicit_col_name_for_timestamp) as select current_timestamp from rdb$database;
create view v_dual_boo_b(explicit_col_name_for_boolean) as select true from rdb$database;
create view v_dual_blb_b(explicit_col_name_for_blob) as select cast('bin' as blob sub_type text) from rdb$database;
create view v_dual_txt_b(explicit_col_name_for_char) as select 'TXT' from rdb$database; ------------------------------ [ 2 ]

-------------------------------------

set sqlda_display on;
set bail off;

select * from v_dual_nul_a;

select * from v_dual_num_a;

select * from v_dual_dts_a;

select * from v_dual_boo_a;

select * from v_dual_blb_a;

select * from v_dual_txt_a;

-------------------------------------

select * from v_dual_nul_b;

select * from v_dual_num_b;

select * from v_dual_dts_b;

select * from v_dual_boo_b;

select * from v_dual_blb_b;

select * from v_dual_txt_b; ------------------------------------------------------------------------------------------ [ 3 ]

NOTE-1: character set of connection must be multy-byte (utf8).

Attempt to query view v_dual_txt_b (see lines marked as [ 2 ] and [ 3 ]) leads to:

select * from v_dual_txt_b;

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 452 TEXT Nullable scale: 0 subtype: 0 len: 0 charset: 4 UTF8
  :  name: EXPLICIT_COL_NAME_FOR_CHAR  alias: EXPLICIT_COL_NAME_FOR_CHAR
  : table: V_DUAL_TXT_B  owner: SYSDBA

EXPLICIT_COL_NAME_FOR_CHAR 
========================== 
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 0, actual 3

No such problem if connection charset not specified or belong to 'narrow' character sets.