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

ACTUAL capacity of field declared in character set UTF8 can be reduced by changing to character set NONE, table data are unavaliable after this. [CORE4730] #5036

Open
firebird-automations opened this issue Mar 31, 2015 · 1 comment

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

set autoddl off;
recreate table test(id int);
commit;

set term ^;
execute block as
begin
execute statement 'drop domain dmxx';
when any do begin end
end
^
set term ;^
commit;

create domain dmxx varchar(15) character set none;
commit;

alter table test add s01 varchar(8) character set utf8;
commit;
insert into test(id, s01) values( 1, 'qwertyui');
insert into test(id, s01) values( 2, 'âêûėżöé');
insert into test(id, s01) values( 3, 'öüčřžéèê');
commit;
select id, s01, char_length(s01) s01_char_length, octet_length(s01) s01_octecs from test;

alter table test alter column s01 type dmxx;
commit;
show table test;

select * from test;

Result in WI-T3.0.0.31756:
~~~~~~~~~~~~~~~~~~~~~

      ID S01      S01\_CHAR\_LENGTH   S01\_OCTECS 

============ ======== =============== ============
1 qwertyui 8 8
2 âêûėżöé 7 14
3 öüčřžéèê 8 16

ID INTEGER Nullable
S01 (DMXX) VARCHAR(15) Nullable -- <<< table DDL has been changes SUCCESSFULLY despite data with more than 15 octets in it ?

      ID S01             

============ ===============
1 qwertyui
2 âêûėżöé
Statement failed, SQLSTATE = 22001
arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 15, actual 32

Result in WI-V2.5.4.26853:
~~~~~~~~~~~~~~~~~~~~~

      ID S01                              S01\_CHAR\_LENGTH   S01\_OCTECS 

============ ================================ =============== ============
1 qwertyui 8 8
2 âêûėżöé 7 14
3 öüčřžéèê 8 16

Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-New size specified for column S01 must be at least 32 characters.

ID INTEGER Nullable
S01 VARCHAR(8) CHARACTER SET UTF8 Nullable

      ID S01                              

============ ================================
1 qwertyui
2 âêûėżöé
3 öüčřžéèê

@firebird-automations
Copy link
Collaborator Author

Commented by: @aafemt

IMHO, altering of field's datatype with changing of character set must be prohibited at all.
"right truncation" is just one of possible errors. "transliteration error" is possible as well if old data contain characters missing in new charset.

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

No branches or pull requests

1 participant