Submitted by: @pavel-zotov
set bail on;
set list on;
shell del c:\temp\tmp.fdb 2>nul;
create database 'localhost:c:\temp\tmp.fdb';
create collation name_coll for utf8 from unicode case insensitive;
commit;
create domain dm_name_ci as blob sub_type text character set utf8 collate name_coll;
commit;
recreate table test(
id int
,b1 dm_name_ci
,calc_b1 computed by ( b1 )
-----------------------------------------------------
,c1 varchar(10) character set utf8 collate name_coll
,calc_c1 computed by ( cast(c1 as blob sub_type text character set utf8) collate name_coll ) -- ==> SQLDA: "charset: 0 NONE"
--,calc_c1 computed by ( (select list(x.c1) from test x where http://x.id = http://test.id) ) -- ==> SQLDA: "charset: 0 NONE"
--,calc_c1 computed by ( cast(c1 as varchar(10) character set utf8) collate name_coll ) -- ==> SQLDA: "charset: 32260 UTF8" // OK
);
insert into test(id, b1, c1) values(1,'qWE','qWE');
insert into test(id, b1, c1) values(2,'QWe','QWe');
insert into test(id, b1, c1) values(3,'qwE','qwE');
commit;
set count on;
set echo on;
---------------------------------------------
select id
from test where calc_c1 starting with 'qwe'; -- Records affected: 0
---------------------------------------------
select id
from test where calc_b1 starting with 'qwe'; -- Records affected: 0
---------------------------------------------
set sqlda_display on;
select t.b1, t.c1 from test t rows 0;
select t.calc_b1, t.calc_c1 from test t rows 0;
set sqlda_display off;
Outcome:
########
select id
from test where calc_c1 starting with 'qwe'; -- Records affected: 0
Records affected: 0
---------------------------------------------
select id
from test where calc_b1 starting with 'qwe'; -- Records affected: 0
Records affected: 0
---------------------------------------------
set sqlda_display on;
select t.b1, t.c1 from test t rows 0;
INPUT message field count: 0
OUTPUT message field count: 2
01: sqltype: 520 BLOB Nullable scale: 0 subtype: 1 len: 8 charset: 4 UTF8
: name: B1 alias: B1
: table: TEST owner: SYSDBA
02: sqltype: 448 VARYING Nullable scale: 0 subtype: 0 len: 40 charset: 32260 UTF8
: name: C1 alias: C1
: table: TEST owner: SYSDBA
Records affected: 0
select t.calc_b1, t.calc_c1 from test t rows 0;
INPUT message field count: 0
OUTPUT message field count: 2
01: sqltype: 520 BLOB Nullable scale: 0 subtype: 1 len: 8 charset: 0 NONE
: name: CALC_B1 alias: CALC_B1
: table: TEST owner: SYSDBA
02: sqltype: 520 BLOB Nullable scale: 0 subtype: 1 len: 8 charset: 0 NONE
: name: CALC_C1 alias: CALC_C1
: table: TEST owner: SYSDBA
Records affected: 0
set sqlda_display off;
Commits: 572c733 e3d20a9
Submitted by: @pavel-zotov
set bail on;
set list on;
shell del c:\temp\tmp.fdb 2>nul;
create database 'localhost:c:\temp\tmp.fdb';
create collation name_coll for utf8 from unicode case insensitive;
commit;
create domain dm_name_ci as blob sub_type text character set utf8 collate name_coll;
commit;
recreate table test(
id int
,b1 dm_name_ci
,calc_b1 computed by ( b1 )
-----------------------------------------------------
,c1 varchar(10) character set utf8 collate name_coll
,calc_c1 computed by ( cast(c1 as blob sub_type text character set utf8) collate name_coll ) -- ==> SQLDA: "charset: 0 NONE"
--,calc_c1 computed by ( (select list(x.c1) from test x where http://x.id = http://test.id) ) -- ==> SQLDA: "charset: 0 NONE"
--,calc_c1 computed by ( cast(c1 as varchar(10) character set utf8) collate name_coll ) -- ==> SQLDA: "charset: 32260 UTF8" // OK
);
insert into test(id, b1, c1) values(1,'qWE','qWE');
insert into test(id, b1, c1) values(2,'QWe','QWe');
insert into test(id, b1, c1) values(3,'qwE','qwE');
commit;
set count on;
set echo on;
---------------------------------------------
select id
from test where calc_c1 starting with 'qwe'; -- Records affected: 0
---------------------------------------------
select id
from test where calc_b1 starting with 'qwe'; -- Records affected: 0
---------------------------------------------
set sqlda_display on;
select t.b1, t.c1 from test t rows 0;
select t.calc_b1, t.calc_c1 from test t rows 0;
set sqlda_display off;
Outcome:
########
select id
from test where calc_c1 starting with 'qwe'; -- Records affected: 0
Records affected: 0
---------------------------------------------
select id
from test where calc_b1 starting with 'qwe'; -- Records affected: 0
Records affected: 0
---------------------------------------------
set sqlda_display on;
select t.b1, t.c1 from test t rows 0;
INPUT message field count: 0
OUTPUT message field count: 2
01: sqltype: 520 BLOB Nullable scale: 0 subtype: 1 len: 8 charset: 4 UTF8
: name: B1 alias: B1
: table: TEST owner: SYSDBA
02: sqltype: 448 VARYING Nullable scale: 0 subtype: 0 len: 40 charset: 32260 UTF8
: name: C1 alias: C1
: table: TEST owner: SYSDBA
Records affected: 0
select t.calc_b1, t.calc_c1 from test t rows 0;
INPUT message field count: 0
OUTPUT message field count: 2
01: sqltype: 520 BLOB Nullable scale: 0 subtype: 1 len: 8 charset: 0 NONE
: name: CALC_B1 alias: CALC_B1
: table: TEST owner: SYSDBA
02: sqltype: 520 BLOB Nullable scale: 0 subtype: 1 len: 8 charset: 0 NONE
: name: CALC_C1 alias: CALC_C1
: table: TEST owner: SYSDBA
Records affected: 0
set sqlda_display off;
Commits: 572c733 e3d20a9