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
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4733
URL: http://tracker.firebirdsql.org/browse/CORE-4733
Project: Firebird Core
Issue Type: Bug
Reporter: 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:
======
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".
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;
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;
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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
