ALTER TABLE <T> ALTER <INDEXED_NON_TEXTUAL_COL> TYPE VARCHAR(nnnn) causes column to be non-updatable state

[pavel-zotov]

If some table <T> has non-textual indexed column <C> then ALTER TABLE <T> ALTER COLUMN <C> TYPE VARCHAR(nnn)
leads such column to be in 'partially not-updatable state'.
To be more concrete: such column can not store textual values that can not be converted to the previous datatype to<C>.

For example, let old datatype was boolean and stored in INDEXED column <bool_fld>.
If we do now ALTER TABLE <test> ALTER COLUMN <bool_fld> TYPE VARCHAR(50) then subsequent INSERT INTO <test> VALUES ('really true') will fail with conversion errror. No such problem if column <bool_fld> has no index.

This is more full example with several other data types:

set bail on;
set list on;
shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';

set echo on;
create table t_text_initially(val varchar(50));
create index t_text_init_val on t_text_initially(val);
commit;

create table t_bool_storage(val boolean);
insert into t_bool_storage select iif(mod(i,3)=2, null, mod(i,2) = 0 ) from ( select row_number()over() i from rdb$types );
commit;
create index t_bool_storage_val on t_bool_storage(val);
commit;
-------------------------------------------
create table t_date_storage(val date);
insert into t_date_storage select dateadd(mod(i,5) day to date '01.01.2026') from ( select row_number()over() i from rdb$types );
commit;
create index t_date_storage_val on t_date_storage(val);
commit;
-------------------------------------------
create table t_time_storage(val time);
insert into t_time_storage select dateadd(mod(i,50) second to time '00:00:00') from ( select row_number()over() i from rdb$types );
commit;
create index t_time_storage_val on t_time_storage(val);
commit;
-------------------------------------------
create table t_i128_storage(val int128);
insert into t_i128_storage select 170141183460469231731687303715884105727 - row_number()over() from rdb$types;
commit;
create index t_i128_storage on t_i128_storage(val);
commit;
-------------------------------------------
insert into t_text_initially select * from t_bool_storage;
insert into t_text_initially select * from t_date_storage;
insert into t_text_initially select * from t_time_storage;
insert into t_text_initially select * from t_i128_storage;
commit;

insert into t_text_initially(val) values('textual true');
insert into t_text_initially(val) values('textual 23.03.2026');
insert into t_text_initially(val) values('textual 01:02:03');
insert into t_text_initially(val) values('textual -123456789');
commit;
-------------------------------------------
alter table t_bool_storage alter val type varchar(50);
alter table t_date_storage alter val type varchar(50);
alter table t_time_storage alter val type varchar(50);
alter table t_i128_storage alter val type varchar(50);
commit;
show index;
-------------------------------------------
set bail off;
insert into t_bool_storage(val) values('textual true');
insert into t_date_storage(val) values('textual 23.03.2026');
insert into t_time_storage(val) values('textual 01:02:03');
insert into t_i128_storage(val) values('textual -123456789');

(four last statements will fail with SQLSTATE = 22018 / Expression evaluation error for index ... / -conversion error from string ...)