You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Command "Alter table <T> alter TYPE <C> <DOMAIN_WITH_NOT_NULL" does not verifies data in column <C> and makes incorrect assignments in <C> to ZERO / JULIAN_DATE / ASCII(0) for types INT, TIMESTAMP and VARCHAR [CORE4733]
#5039
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;
commit;
create table tt(x int, y timestamp, c varchar(10) character set utf8, b boolean);
commit;
insert into tt values(null, null, null, null);
commit;
select * from tt;
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".
Run-1:
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.
commit;
select * from tt;
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):
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;
XB
>
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:
Run-2:
SQL> commit;
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
PS.
SQL> show version;
ISQL Version: WI-T3.0.0.31768 Firebird 3.0 Beta 2
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.31768 Firebird 3.0 Beta 2"
Firebird/Windows/Intel/i386 (remote server), version "WI-T3.0.0.31768 Firebird 3.0 Beta 2/tcp (csprog)/P13"
Firebird/Windows/Intel/i386 (remote interface), version "WI-T3.0.0.31768 Firebird 3.0 Beta 2/tcp (csprog)/P13"
on disk structure version 12.0
See also tests that manipulates with NULL fields/domains and check results: CORE1518 Adding a non-null restricted column to a populated table renders the table inconsistent; CORE4453 (Regression: NOT NULL constraint, declared in domain, does not work); CORE4725 (Inconsistencies with ALTER DOMAIN and ALTER TABLE with DROP NOT NULL and PRIMARY KEYs); CORE4733 (Command "Alter table <T> alter TYPE <C> <DOMAIN_WITH_NOT_NULL" does not verifies data in column <C> and makes incorrect assignments in <C> to ZERO / JULIAN_DATE / ASCII(0) for types INT, TIMESTAMP and VARCHAR).
The text was updated successfully, but these errors were encountered:
Test Details: See also tests that manipulates with NULL fields/domains and check results: CORE1518 Adding a non-null restricted column to a populated table renders the table inconsistent; CORE4453 (Regression: NOT NULL constraint, declared in domain, does not work); CORE4725 (Inconsistencies with ALTER DOMAIN and ALTER TABLE with DROP NOT NULL and PRIMARY KEYs); CORE4733 (Command "Alter table <T> alter TYPE <C> <DOMAIN_WITH_NOT_NULL" does not verifies data in column <C> and makes incorrect assignments in <C> to ZERO / JULIAN_DATE / ASCII(0) for types INT, TIMESTAMP and VARCHAR).
Submitted by: @pavel-zotov
Prepare:
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;
commit;
create table tt(x int, y timestamp, c varchar(10) character set utf8, b boolean);
commit;
insert into tt values(null, null, null, null);
commit;
select * from tt;
Output:
====== ========================= ========== =======
<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".
Run-1:
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.
commit;
select * from tt;
======= ========================= ========== =======
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;
XB
>
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:
Run-2:
SQL> commit;
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
PS.
SQL> show version;
ISQL Version: WI-T3.0.0.31768 Firebird 3.0 Beta 2
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.31768 Firebird 3.0 Beta 2"
Firebird/Windows/Intel/i386 (remote server), version "WI-T3.0.0.31768 Firebird 3.0 Beta 2/tcp (csprog)/P13"
Firebird/Windows/Intel/i386 (remote interface), version "WI-T3.0.0.31768 Firebird 3.0 Beta 2/tcp (csprog)/P13"
on disk structure version 12.0
Charset of database and connection = NONE.
Commits: b363356 2828aee FirebirdSQL/fbt-repository@dbbe64a FirebirdSQL/fbt-repository@834bd37
====== Test Details ======
See also tests that manipulates with NULL fields/domains and check results:
CORE1518 Adding a non-null restricted column to a populated table renders the table inconsistent;
CORE4453 (Regression: NOT NULL constraint, declared in domain, does not work);
CORE4725 (Inconsistencies with ALTER DOMAIN and ALTER TABLE with DROP NOT NULL and PRIMARY KEYs);
CORE4733 (Command "Alter table <T> alter TYPE <C> <DOMAIN_WITH_NOT_NULL" does not verifies data in column <C> and makes incorrect assignments in <C> to ZERO / JULIAN_DATE / ASCII(0) for types INT, TIMESTAMP and VARCHAR).
The text was updated successfully, but these errors were encountered: