When a foreign key constraint on a table is dropped, the associated statistics
row for the conglomerate is not removed
----------------------------------------------------------------------------------------------------------------------
Key: DERBY-5681
URL: https://issues.apache.org/jira/browse/DERBY-5681
Project: Derby
Issue Type: Bug
Components: Store
Affects Versions: 10.8.2.2
Reporter: Brett Bergquist
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.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
