Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

COMPUTED BY column looses charset and collate of source field <F> when <F> is either of type BLOB or VARCHAR casted to BLOB [CORE6531] #6758

Closed
firebird-automations opened this issue Apr 3, 2021 · 4 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0.0 [ 10931 ]

Fix Version: 3.0.8 [ 10960 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment