create domain dm_int int NOT null;
create domain dm_dts timestamp NOT null;
create domain dm_utf varchar(10) character set utf8 NOT null;
create domain dm_boo boolean NOT null;
create table tt(x int, y timestamp, c varchar(10) character set utf8, b boolean);
insert into tt values(null, null, null, null);
select * from tt;
X Y C B
====== ========================= ========== =======
<null> <null> <null> <null>
(it's OK meanwhile).
Now we try to alter types of THREE fields do correponding domains: INT ==> DM_INT, TIMESTAMP ==> DM_DTS and VARCHAR UTF8 ==> DM_UTF.
Fourth field (BOOLEAN) - will be show separately, it's "another story".
alter table tt alter x type dm_int, alter y type dm_dts, alter c type dm_utf; -- PASSED! Verification of data was not performed.
select * from tt;
X Y C B
======= ========================= ========== =======
0 1858-11-17 00:00:00.0000 <null>
So, we see that all NULL values are implicitly changed to some 'initial' ones.
Furthermore, it should be emphasized, that value of CHARACTER column 'C' has been changed NOT to the space(s) and NOT to empty line.
This is ascii_char(0):
select x, '>'||x||'<' x_with_bounds, ascii_val(left(x,1)) x_ascii, iif(x='', 'is-empty', 'not-empty') is_empty, octet_length(x) x_octlen from tu;
X X_WITH_BOUNDS X_ASCII IS_EMPTY X_OCTLEN
========== ============= ======= ========= ============
foo >foo< 102 not-empty 3
0 not-empty 0
rio >rio< 114 not-empty 3
The result shown above for string where it was written 'bar' is strange in yet another: ascii_char(0) MUST be concatenated at least with text that is LEFTSIDE from it:
SQL> select '>' || ascii_char(0) ||'<' xb from rdb$database;
But there is no any '>' in the 2nd line at all.
Now it's time to do the same with BOOLEAN field. But in that case we'll get error:
SQL> alter table tt alter b type dm_boo;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-ALTER TABLE TT failed
-MODIFY RDB$RELATION_FIELDS failed
-- and this looks strange because:
SQL> show domain dm_boo;
DM_BOO BOOLEAN Not Null
SQL> show version;
ISQL Version: WI-T220.127.116.11768 Firebird 3.0 Beta 2
Firebird/Windows/Intel/i386 (access method), version "WI-T18.104.22.168768 Firebird 3.0 Beta 2"
Firebird/Windows/Intel/i386 (remote server), version "WI-T22.214.171.124768 Firebird 3.0 Beta 2/tcp (csprog)/P13"
Firebird/Windows/Intel/i386 (remote interface), version "WI-T126.96.36.199768 Firebird 3.0 Beta 2/tcp (csprog)/P13"
on disk structure version 12.0
Charset of database and connection = NONE.