[DERBY-5681] When a foreign key constraint on a table is dropped, the associated statistics row for the conglomerate is not removed

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.8.2.2
• Fix Version/s: 10.3.3.1, 10.4.2.1, 10.5.3.2, 10.6.2.4, 10.7.1.4, 10.8.3.0, 10.9.1.0
• Component/s: SQL, Store
• Issue & fix info: Patch Available, Repro attached
• Assignee: Mamta A. Satoor
• Reporter: Brett Bergquist
• Created: 31/Mar/12 23:07
• Updated: 03/Sep/14 08:31
• Resolved: 18/May/12 22:45

Description

If you drop the foreign key constraint for a table, the statistics row does not get removed. This affects the indexStat daemon because it now finds these statistics row which always appear as out of date, causing an update to be scheduled.

Here is how to get it to happen:

set schema app;

CREATE TABLE TEST_TAB_1
(
ID INTEGER PRIMARY KEY NOT NULL
);

CREATE TABLE TEST_TAB_2
(
ID INTEGER PRIMARY KEY NOT NULL
);

ALTER TABLE TEST_TAB_2
ADD CONSTRAINT TEST_TAB_2_FK_1
FOREIGN KEY (ID) REFERENCES TEST_TAB_1(ID);

insert into app.TEST_TAB_1 values (1);
insert into test_tab_2 values(1);

call syscs_util.syscs_update_statistics('APP', 'TEST_TAB_2', null);

select
c.TABLEID,
c.CONGLOMERATENUMBER,
c.CONGLOMERATENAME,
c.ISINDEX,
c.ISCONSTRAINT,
c.CONGLOMERATEID,
t.TABLEID,
t.TABLENAME,
t.TABLETYPE,
s.STATID,
s.REFERENCEID,
s.TABLEID,
s.CREATIONTIMESTAMP,
s.TYPE,
s.VALID,
s.COLCOUNT,
CAST(STATISTICS AS VARCHAR(40)) as STATISTICS
from sys.SYSCONGLOMERATES c join sys.SYSTABLES t on c.TABLEID = t.TABLEID join sys.SYSSTATISTICS s on s.TABLEID = t.TABLEID
where t.TABLENAME = 'TEST_TAB_2' and c.ISINDEX = false;

– At this point there are two statistic rows

TABLEID CONGLOMERATENUMBER CONGLOMERATENAME ISINDEX ISCONSTRAINT CONGLOMERATEID TABLEID TABLENAME TABLETYPE STATID REFERENCEID TABLEID CREATIONTIMESTAMP TYPE VALID COLCOUNT STATISTICS
84490209-0136-6999-c1b4-000065089f97 348432 84490209-0136-6999-c1b4-000065089f97 false false cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 TEST_TAB_2 T edbc8255-0136-6999-c1b4-000065089f97 55410238-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 2012-03-31 17:36:49.629 I true 1 numunique= 1 numrows= 1
84490209-0136-6999-c1b4-000065089f97 348432 84490209-0136-6999-c1b4-000065089f97 false false cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 TEST_TAB_2 T 05278254-0136-6999-c1b4-000065089f97 63454207-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 2012-03-31 17:36:49.628 I true 1 numunique= 1 numrows= 1

– Now drop the constraint

alter table TEST_TAB_2
drop constraint TEST_TAB_2_FK_1;

select
c.TABLEID,
c.CONGLOMERATENUMBER,
c.CONGLOMERATENAME,
c.ISINDEX,
c.ISCONSTRAINT,
c.CONGLOMERATEID,
t.TABLEID,
t.TABLENAME,
t.TABLETYPE,
s.STATID,
s.REFERENCEID,
s.TABLEID,
s.CREATIONTIMESTAMP,
s.TYPE,
s.VALID,
s.COLCOUNT,
CAST(STATISTICS AS VARCHAR(40)) as STATISTICS
from sys.SYSCONGLOMERATES c join sys.SYSTABLES t on c.TABLEID = t.TABLEID join sys.SYSSTATISTICS s on s.TABLEID = t.TABLEID
where t.TABLENAME = 'TEST_TAB_2' and c.ISINDEX = false;

– There are still two statistic rows

TABLEID CONGLOMERATENUMBER CONGLOMERATENAME ISINDEX ISCONSTRAINT CONGLOMERATEID TABLEID TABLENAME TABLETYPE STATID REFERENCEID TABLEID CREATIONTIMESTAMP TYPE VALID COLCOUNT STATISTICS
84490209-0136-6999-c1b4-000065089f97 348432 84490209-0136-6999-c1b4-000065089f97 false false cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 TEST_TAB_2 T edbc8255-0136-6999-c1b4-000065089f97 55410238-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 2012-03-31 17:36:49.629 I true 1 numunique= 1 numrows= 1
84490209-0136-6999-c1b4-000065089f97 348432 84490209-0136-6999-c1b4-000065089f97 false false cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 TEST_TAB_2 T 05278254-0136-6999-c1b4-000065089f97 63454207-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 2012-03-31 17:36:49.628 I true 1 numunique= 1 numrows= 1

– Add another row

insert into app.TEST_TAB_1 values (2);
insert into test_tab_2 values(2);

– Update the statistics

call syscs_util.syscs_update_statistics('APP', 'TEST_TAB_2', null);

select
c.TABLEID,
c.CONGLOMERATENUMBER,
c.CONGLOMERATENAME,
c.ISINDEX,
c.ISCONSTRAINT,
c.CONGLOMERATEID,
t.TABLEID,
t.TABLENAME,
t.TABLETYPE,
s.STATID,
s.REFERENCEID,
s.TABLEID,
s.CREATIONTIMESTAMP,
s.TYPE,
s.VALID,
s.COLCOUNT,
CAST(STATISTICS AS VARCHAR(40)) as STATISTICS
from sys.SYSCONGLOMERATES c join sys.SYSTABLES t on c.TABLEID = t.TABLEID join sys.SYSSTATISTICS s on s.TABLEID = t.TABLEID
where t.TABLENAME = 'TEST_TAB_2' and c.ISINDEX = false;

– There are still two rows but now one show 1 row and one shows 2 rows

TABLEID CONGLOMERATENUMBER CONGLOMERATENAME ISINDEX ISCONSTRAINT CONGLOMERATEID TABLEID TABLENAME TABLETYPE STATID REFERENCEID TABLEID CREATIONTIMESTAMP TYPE VALID COLCOUNT STATISTICS
84490209-0136-6999-c1b4-000065089f97 348432 84490209-0136-6999-c1b4-000065089f97 false false cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 TEST_TAB_2 T edbc8255-0136-6999-c1b4-000065089f97 55410238-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 2012-03-31 17:36:49.629 I true 1 numunique= 1 numrows= 1
84490209-0136-6999-c1b4-000065089f97 348432 84490209-0136-6999-c1b4-000065089f97 false false cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 TEST_TAB_2 T 18438274-0136-6999-c1b4-000065089f97 63454207-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 2012-03-31 17:41:19.164 I true 1 numunique= 2 numrows= 2

– Add the constraint back on

ALTER TABLE TEST_TAB_2
ADD CONSTRAINT TEST_TAB_2_FK_1
FOREIGN KEY (ID) REFERENCES TEST_TAB_1(ID);

– Insert another row

insert into app.TEST_TAB_1 values (3);
insert into test_tab_2 values(3);

– Update the statistics

call syscs_util.syscs_update_statistics('APP', 'TEST_TAB_2', null);

select
c.TABLEID,
c.CONGLOMERATENUMBER,
c.CONGLOMERATENAME,
c.ISINDEX,
c.ISCONSTRAINT,
c.CONGLOMERATEID,
t.TABLEID,
t.TABLENAME,
t.TABLETYPE,
s.STATID,
s.REFERENCEID,
s.TABLEID,
s.CREATIONTIMESTAMP,
s.TYPE,
s.VALID,
s.COLCOUNT,
CAST(STATISTICS AS VARCHAR(40)) as STATISTICS
from sys.SYSCONGLOMERATES c join sys.SYSTABLES t on c.TABLEID = t.TABLEID join sys.SYSSTATISTICS s on s.TABLEID = t.TABLEID
where t.TABLENAME = 'TEST_TAB_2' and c.ISINDEX = false;

– Now there are 3 rows

TABLEID CONGLOMERATENUMBER CONGLOMERATENAME ISINDEX ISCONSTRAINT CONGLOMERATEID TABLEID TABLENAME TABLETYPE STATID REFERENCEID TABLEID CREATIONTIMESTAMP TYPE VALID COLCOUNT STATISTICS
84490209-0136-6999-c1b4-000065089f97 348432 84490209-0136-6999-c1b4-000065089f97 false false cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 TEST_TAB_2 T edbc8255-0136-6999-c1b4-000065089f97 55410238-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 2012-03-31 17:36:49.629 I true 1 numunique= 1 numrows= 1
84490209-0136-6999-c1b4-000065089f97 348432 84490209-0136-6999-c1b4-000065089f97 false false cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 TEST_TAB_2 T 45eb02e8-0136-6999-c1b4-000065089f97 63454207-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 2012-03-31 17:46:00.211 I true 1 numunique= 3 numrows= 3
84490209-0136-6999-c1b4-000065089f97 348432 84490209-0136-6999-c1b4-000065089f97 false false cccb420a-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 TEST_TAB_2 T 0ea502e9-0136-6999-c1b4-000065089f97 7ab90278-0136-6999-c1b4-000065089f97 84490209-0136-6999-c1b4-000065089f97 2012-03-31 17:46:00.212 I true 1 numunique= 3 numrows= 3

Note that dropping that recreating the constraint or compressing the table does not fix the problem.

Issue Links

• relates to: DERBY-5702 Creating a foreign key constraint does not automatically create a statistics row if foreign key constraint will share a backing index created for a primary key
• relates to: DERBY-6283 indexStat daemon processing tables over and over even when there are no changes in the tables in soft upgraded database.
• relates to: DERBY-5680 indexStat daemon processing tables over and over even when there are no changes in the tables
• relates to: DERBY-4115 Provide a way to drop statistics information