Issue Details (XML | Word | Printable)

Key: CORE-4733
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Pavel Zotov
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 03/Apr/15 12:26 PM   Updated: 27/May/15 05:06 PM
Component/s: None
Affects Version/s: 3.0 Beta 1
Fix Version/s: 3.0 Beta 2

QA Status: Done successfully
Test Details:
See also tests that manipulates with NULL fields/domains and check results:
CORE-1518 Adding a non-null restricted column to a populated table renders the table inconsistent;
CORE-4453 (Regression: NOT NULL constraint, declared in domain, does not work);
CORE-4725 (Inconsistencies with ALTER DOMAIN and ALTER TABLE with DROP NOT NULL and PRIMARY KEYs);
CORE-4733 (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).


 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Descending order - Click to sort in ascending order
There are no comments yet on this issue.