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

Inconsistencies with ALTER DOMAIN and ALTER TABLE with DROP NOT NULL and PRIMARY KEYs [CORE4725] #5032

Closed
firebird-issue-importer opened this issue Mar 30, 2015 · 11 comments

Comments

@firebird-issue-importer

Submitted by: @pavel-zotov

Attachments:
c4725-fdb-created-20150311-ods12-generation16.zip

WI-T3.0.0.31733

Script:

recreate table test02\(x int not null\);
alter table test02 add constraint test02\_pk primary key \(x\);
commit;
alter table test02 alter x null; \-\-\-\-\-\-\-\-\- PASSED\!
commit;

set echo on;
show table test02;

insert into test02\(x\) values\(null\);
insert into test02\(x\) select x from test02;
update test02 set x = null;

select \* from test02;

Output:

show table test02;

X INTEGER Nullable
CONSTRAINT TEST02_PK:
Primary key (X)

insert into test02\(x\) values\(null\);
insert into test02\(x\) select x from test02;
update test02 set x = null;

select \* from test02;

       X

============
<null>
<null>

Commits: 5109af2 FirebirdSQL/fbt-repository@7bad9ff

====== Test Details ======

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 Mar 30, 2015

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 3, 2015

Commented by: @asfernandes

Changing this bug title to reflect the current state of things.

create domain d integer;
create domain dn integer not null;

recreate table test02(x integer not null);
alter table test02 add constraint test02_pk primary key (x);
alter table test02 alter x drop not null; -- correct error

recreate table test02(x d not null);
alter table test02 add constraint test02_pk primary key (x);
alter table test02 alter x drop not null; -- correct error

recreate table test02(x dn);
alter table test02 add constraint test02_pk primary key (x);
alter table test02 alter x drop not null; -- correct pass

recreate table test02(x dn not null);
alter table test02 add constraint test02_pk primary key (x);
alter table test02 alter x drop not null; -- incorrect error !!!

recreate table test02(x dn);
alter table test02 add constraint test02_pk primary key (x);
alter domain dn drop not null; -- incorrect pass !!!

alter domain dn set not null;

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 3, 2015

Modified by: @asfernandes

summary: Command "Alter table <T> alter <C> NULL" is allowed for column <C> which was already defined as PRIMARY KEY for <T> => Inconsistencies with ALTER DOMAIN and ALTER TABLE with DROP NOT NULL and PRIMARY KEYs

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 3, 2015

Commented by: @asfernandes

Now about this:

recreate table test02(x dn not null);
alter table test02 add constraint test02_pk primary key (x);
alter table test02 alter x drop not null; -- incorrect error !!!

This incorrect thing is done by a system trigger that we have only the BLR code. Yes, we don't have a system trigger source code!!!

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 14, 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 Apr 21, 2015

Commented by: @pavel-zotov

Adriano, can you please clarify about this:

===
recreate table test02(x dn not null);
alter table test02 add constraint test02_pk primary key (x);
alter table test02 alter x drop not null; -- incorrect error !!!

This incorrect thing is done by a system trigger that we have only the BLR code. Yes, we don't have a system trigger source code!!!

Currently this code does *NOT* produce any error and table test02 will remain in the previous state: its field 'x' will be NOT null.
Is it correct ? Why user should not be at least warned that his statement dis not executed successfully ?

I do the following:

create domain dm\_02 integer not null;
commit;
recreate table test02\(x dm\_02 not null\);
alter table test02 add constraint test02\_pk primary key \(x\);
commit;

set echo on;
alter table test02 alter x drop not null;
commit;

show table test02;

-- and get:

1) STDOUT:

 alter table test02 alter x drop not null;
commit;

show table test02;

X (DM_02) INTEGER Not Null
CONSTRAINT TEST02_PK:
Primary key (X)

2) STDERR:
--- empty file ---

And 2nd question: I have empty database with ODS 12.0 that has been created 11-mar-2015, and if I run this query against this database than exception DOES appear.
May be this database is 'too old' but how can it be found from gstat output ? (see attached file)

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 21, 2015

Modified by: @pavel-zotov

Attachment: c4725-fdb-created-20150311-ods12-generation16.zip [ 12722 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 22, 2015

Commented by: @asfernandes

alter table test02 alter x drop not null; drops the field's NOT NULL constraint, but the domain's constraint remains. Technically it's still NOT NULL. You will get an error if you try to drop the domain's constraint after it.

The updated system trigger is created only in databases created after my change.

You cannot control the ODS version of trunk.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 22, 2015

Commented by: @pavel-zotov

> Technically it's still NOT NULL. You will get an error if you try to drop the domain's constraint after it.

I did not attempt to remove DOMAIN constraint, only field's one was touched.
Why I did NOT get error (or warning) that field 'X' remained in NOT null state (because it's included in PK constraint) ?

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 22, 2015

Commented by: @asfernandes

As I said, there is no error.

I resist to put warning because it was always said that nobody checks FB warnings.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 30, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

Test Details: 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