ALTER TABLE ALTER COLUMN <textual_field> can not be changed properly in some cases

[pavel-zotov]

In some cases 'ALTER TABLE ... ALTER COLUMN <textual_field> type ... <new_charset>' can lead either to unchanged character set of <textual_field> or this character set is changed but COLLATE attribute gets value that can not be explained. Of course, following normally can be run only against empty tables:

case-1.

set bail on;
shell del g:\temp\tmp4test.fdb 2>nul;
create database 'localhost:g:\temp\tmp4test.fdb';
show version;
commit;
set echo on;
 
alter character set utf8 set default collation unicode_ci;
alter character set win1250 set default collation win_cz_ci_ai;
alter character set win1252 set default collation win_ptbr;
alter character set win1257 set default collation win1257_ee;
commit;
 
create table test(
    f01 varchar(10) character set win1252
   ,f02 varchar(10) character set win1257
   ,f03 varchar(10) character set utf8
);
commit;

---------------------------------------------------------------
 
commit;
connect 'localhost:g:\temp\tmp4test.fdb';
 
alter table test
    alter column f01 type varchar(10) character set win1250
   -----------------------------------------------------------
   ,alter column f02 type varchar(10) character set win1252
   -----------------------------------------------------------
   ,alter column f03 type varchar(10) character set win1257
;
commit;
connect 'localhost:g:\temp\tmp4test.fdb';
 
 
show table test; -------------------- [ 1 ]

/*
    Output: STRANGE. All remained unchanged:

    f01 ................. win1252 collate win_ptbr    // why not changed to win1250 collate win_cz_ci_ai ?
    f02 ................. win1257 collate win1257_ee  // why not changed to win1252 collate win_ptbr ?
    f03 ................. utf8    collate unicode_ci  // why not changed to win1257 collate win1257_ee ?

*/

case-2.

set bail on;
shell del g:\temp\tmp4test.fdb 2>nul;
create database 'localhost:g:\temp\tmp4test.fdb';
show version;
commit;
set echo on;
 
alter character set utf8 set default collation unicode_ci;
alter character set win1250 set default collation win_cz_ci_ai;
alter character set win1252 set default collation win_ptbr;
alter character set win1257 set default collation win1257_ee;
commit;
 
create table test(
    f_init_1252 varchar(10) character set win1252
   ,f_init_1257 varchar(10) character set win1257
   ,f_init_utf8 varchar(10) character set utf8
);
commit;

-- show table test;

/*
    f_init_1252 ................. win1252 collate win_ptbr
    f_init_1257 ................. win1257 collate win1257_ee
    f_init_utf8 ................. utf8    collate unicode_ci

*/
 
---------------------------------------------------------------
 
commit;
connect 'localhost:g:\temp\tmp4test.fdb';
 
alter table test
    alter column f_init_1252 to f_curr_1250
   ,alter column f_curr_1250 type varchar(10) character set win1250
   -----------------------------------------------------------
   ,alter column f_init_1257 to f_curr_1252
   ,alter column f_curr_1252 type varchar(10) character set win1252
   -----------------------------------------------------------
   ,alter column f_init_utf8 to f_curr_1257
   ,alter column f_curr_1257 type varchar(10) character set win1257
;
commit;
connect 'localhost:g:\temp\tmp4test.fdb';
 
 
show table test; -------------------- [ 1 ]
 
/*
    Output: charsets were CHANGED but new COLLATE attribute looks strange:

    f_curr_1250 ................. win1250 collate bs_ba      // from where 'bs_ba' came ?     expected: win_cz_ci_ai
    f_curr_1252 ................. win1252 collate pxw_intl   // from where 'pxw_intl' came ?  expected: win_ptbr
    f_curr_1257 ................. win1257 collate win1257_lv // from where 'win1257_lv came ? expected: win1257_ee

*/

[mrotteveel]

If I had to guess, it simply doesn't change the collation, so for the second case, the collation id of the old collation is the same as the collation id of the new collation.

E.g. WIN_PTBR is RDB$COLLATION_ID 6, and BS_BA is also id 6. Same for WIN1257_EE / PXW_INTL: id 1, and UNICODE_CI / WIN1257_LV: id 3

That said, I think this should be two separate issues: One for the not changing of the character set, and one for the changing of the character set, but not resetting the collation.

[aafemt]

It looks like at least first one is my fault. I'm preparing PR.

[aafemt]

About second issue: is it really allowed to reference not-yet-existing column?

PS: Oops, ignore this comment, I mixed up column renaming and addition.

[aafemt]

Second issue is a duplicate of #2842 for non-domain based new type.