Derby
  1. Derby
  2. 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

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.8.2.2
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Low
    • Issue & fix info:
      Repro attached

      Description

      When a foreign key constraints is created on a column which already has a primary key constraint, the statistics for foreign key constraint do not get created automatically. Have to run update statistics by hand to add statistics for foreign key constraint. I hope my understanding of statistics creation is correct in this regards. Following script shows the issue

      java -Dderby.storage.indexStats.auto=false -Dij.exceptionTrace=true org.apache.derby.tools.ij
      connect 'jdbc:derby:db1;create=true';
      CREATE TABLE TEST_TAB_1
      (
      ID INTEGER NOT NULL primary key
      );
      CREATE TABLE TEST_TAB_2
      (
      ID INTEGER not null, ID1 INTEGER not null
      );
      insert into TEST_TAB_1 values (1);
      insert into test_tab_2 values(1,1);
      CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_1', null);
      CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null);

      --At this point, we will find statistics row for primary key constraint on TEST_TAB_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_1' and c.ISINDEX = false;

      --Now create primary key constraint on TEST_TAB_2
      ALTER TABLE TEST_TAB_2
      ADD CONSTRAINT TEST_TAB_2_PK_1
      PRIMARY KEY (id);

      --At this point, we will find statistics row for primary key constraint on TEST_TAB_2
      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 if we create a foreign key constraint on TEST_TAB_2(ID), there will be no statistics created for it.
      ALTER TABLE TEST_TAB_2
      ADD CONSTRAINT TEST_TAB_2_FK_1
      FOREIGN KEY(id) REFERENCES TEST_TAB_1(id);

      --still only one statistics row for TEST_TAB_2
      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;

      --Running statistics creation by hand will create 2nd statistics row for TEST_TAB_2
      CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null);

      – now will have 2 statistics rows for TEST_TAB_2
      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;

      --If I create foreign key on a column that does not share backing index with primary key, stats will get created automatically
      ALTER TABLE TEST_TAB_2
      ADD CONSTRAINT TEST_TAB_2_FK_2
      FOREIGN KEY(id1) REFERENCES TEST_TAB_1(id);

      --will have additional constraint row for new foreign key constraint on TEST_TAB_2
      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;

        Issue Links

          Activity

          Mamta A. Satoor created issue -
          Mamta A. Satoor made changes -
          Field Original Value New Value
          Link This issue is related to DERBY-5681 [ DERBY-5681 ]
          Knut Anders Hatlen made changes -
          Labels derby_triage10_10
          Urgency Low [ 10053 ]
          Priority Major [ 3 ] Minor [ 4 ]
          Gavin made changes -
          Workflow jira [ 12663446 ] Default workflow, editable Closed status [ 12801959 ]

            People

            • Assignee:
              Unassigned
              Reporter:
              Mamta A. Satoor
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:

                Development