Issue Details (XML | Word | Printable)

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

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

Inconsistencies with ALTER DOMAIN and ALTER TABLE with DROP NOT NULL and PRIMARY KEYs

Created: 30/Mar/15 12:31 PM   Updated: 30/May/15 07:08 PM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 3.0 Beta 2

File Attachments: 1. Zip Archive c4725-fdb-created-20150311-ods12-generation16.zip (58 kB)


QA Status: Done successfully
Test Details:
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
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>


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 03/Apr/15 02:59 AM
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;

Adriano dos Santos Fernandes added a comment - 03/Apr/15 03:03 AM
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!!!

Pavel Zotov added a comment - 21/Apr/15 08:05 PM
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)

Adriano dos Santos Fernandes added a comment - 21/Apr/15 11:25 PM
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.

Pavel Zotov added a comment - 22/Apr/15 05:20 AM
> 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) ?

Adriano dos Santos Fernandes added a comment - 22/Apr/15 12:05 PM
As I said, there is no error.

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