Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
firebird-issue-importer opened this issue Apr 3, 2015 · 3 comments

Comments

@firebird-issue-importer
Copy link

firebird-issue-importer commented Apr 3, 2015

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:

 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.

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).

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 5, 2015

Modified by: @asfernandes

Version: 3.0 Beta 1 [ 10332 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 5, 2015

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 2 [ 10586 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 27, 2015

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

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).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants