Derby
  1. Derby
  2. DERBY-5079

DERBY-4984 caused a regression which will not allow users to drop a table if the table was involved in a trigger action rebind during ALTER TABLE DROP COLUMN

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: 10.7.1.4, 10.8.1.2
    • Fix Version/s: 10.7.1.4, 10.8.1.2
    • Component/s: SQL
    • Labels:
      None
    • Bug behavior facts:
      Regression

      Description

      The fix for DERBY-4984 has introduced a regression which adds rows into SYSDEPENDS which are incorrect. The error I think was caused when before doing a rebind of trigger action, I made the trigger table as the current dependent in the dependency system and hence all the dependencies required by the trigger action got associated with that trigger table and hence causing the errorneous behavior. I will work on this asap. The fix went into trunk and 10.7. I will add a test case for this in the jira and will also add a test case in our junit test

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          [bulk update] Close all resolved issues that haven't been updated for more than one year.

          Show
          Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.
          Hide
          Mamta A. Satoor added a comment -

          Fixed the problem in trunk. will work on migrating it to 10.7 next.

          Show
          Mamta A. Satoor added a comment - Fixed the problem in trunk. will work on migrating it to 10.7 next.
          Hide
          Mamta A. Satoor added a comment -

          I have added some disabled test cases for this jira. If those test changes are uncommented, the test will fail. I am working on the fix and hope to have a fix soon to fix this problem.s

          Show
          Mamta A. Satoor added a comment - I have added some disabled test cases for this jira. If those test changes are uncommented, the test will fail. I am working on the fix and hope to have a fix soon to fix this problem.s
          Hide
          Mamta A. Satoor added a comment -

          Following is the ij script showing the problem
          java -Dij.exceptionTrace=true org.apache.derby.tools.ij
          connect 'jdbc:derby:c:/dellater/db1;create=true';
          CREATE TABLE ATDC_15_TAB1(a1 int, b1 int);
          CREATE TABLE ATDC_15_TAB2(a2 int, b2 int);
          insert into ATDC_15_TAB1 values(1,11);
          insert into ATDC_15_TAB2 values(1,11);
          create trigger atdc_15_trigger_1 after update
          on atdc_15_tab1 REFERENCING NEW AS newt
          for each row
          update atdc_15_tab2 set b2 = newt.b1;
          select * from sys.sysdepends;
          alter table atdc_15_tab1 drop column a1 restrict;
          select * from sys.sysdepends;
          drop table ATDC_15_TAB1;
          drop table ATDC_15_TAB2;

          The drop of ATDC_15_TAB2 fails with following error
          ERROR 42X94: Table '23ce809c-012e-7281-172d-0000003c7d78' does not exist.

          The first select * from sys.sysdepends shows 5 rows which are correct and as follows
          select * from sys.sysdepends;
          DEPENDENTID |DEPENDENTFINDER|PROVIDERID |PROVIDERFINDER
          ---------------------------------------------------------------------------------------------------------
          560740aa-012e-7281-172d-0000003c7d78|StoredPrepared&|846c00a0-012e-7281-172d-0000003c7d78|ColumnsInTable
          560740aa-012e-7281-172d-0000003c7d78|StoredPrepared&|8c9400a1-012e-7281-172d-0000003c7d78|Conglomerate
          0ddd00a9-012e-7281-172d-0000003c7d78|Trigger |560740aa-012e-7281-172d-0000003c7d78|StoredPrepared&
          0ddd00a9-012e-7281-172d-0000003c7d78|Trigger |23ce809c-012e-7281-172d-0000003c7d78|Table
          560740aa-012e-7281-172d-0000003c7d78|StoredPrepared&|23ce809c-012e-7281-172d-0000003c7d78|Table
          5 rows selected

          But after the ALTER TABLE DROP COLUMN, it shows 3 extra rows, which is not correct
          select * from sys.sysdepends;
          560740aa-012e-7281-172d-0000003c7d78|StoredPrepared&|846c00a0-012e-7281-172d-0000003c7d78|ColumnsInTable
          560740aa-012e-7281-172d-0000003c7d78|StoredPrepared&|8c9400a1-012e-7281-172d-0000003c7d78|Conglomerate
          0ddd00a9-012e-7281-172d-0000003c7d78|Trigger |560740aa-012e-7281-172d-0000003c7d78|StoredPrepared&
          0ddd00a9-012e-7281-172d-0000003c7d78|Trigger |23ce809c-012e-7281-172d-0000003c7d78|Table
          560740aa-012e-7281-172d-0000003c7d78|StoredPrepared&|23ce809c-012e-7281-172d-0000003c7d78|Table
          23ce809c-012e-7281-172d-0000003c7d78|Table |846c00a0-012e-7281-172d-0000003c7d78|Table
          23ce809c-012e-7281-172d-0000003c7d78|Table |846c00a0-012e-7281-172d-0000003c7d78|Table
          23ce809c-012e-7281-172d-0000003c7d78|Table |846c00a0-012e-7281-172d-0000003c7d78|Table
          8 rows selected

          Show
          Mamta A. Satoor added a comment - Following is the ij script showing the problem java -Dij.exceptionTrace=true org.apache.derby.tools.ij connect 'jdbc:derby:c:/dellater/db1;create=true'; CREATE TABLE ATDC_15_TAB1(a1 int, b1 int); CREATE TABLE ATDC_15_TAB2(a2 int, b2 int); insert into ATDC_15_TAB1 values(1,11); insert into ATDC_15_TAB2 values(1,11); create trigger atdc_15_trigger_1 after update on atdc_15_tab1 REFERENCING NEW AS newt for each row update atdc_15_tab2 set b2 = newt.b1; select * from sys.sysdepends; alter table atdc_15_tab1 drop column a1 restrict; select * from sys.sysdepends; drop table ATDC_15_TAB1; drop table ATDC_15_TAB2; The drop of ATDC_15_TAB2 fails with following error ERROR 42X94: Table '23ce809c-012e-7281-172d-0000003c7d78' does not exist. The first select * from sys.sysdepends shows 5 rows which are correct and as follows select * from sys.sysdepends; DEPENDENTID |DEPENDENTFINDER|PROVIDERID |PROVIDERFINDER --------------------------------------------------------------------------------------------------------- 560740aa-012e-7281-172d-0000003c7d78|StoredPrepared&|846c00a0-012e-7281-172d-0000003c7d78|ColumnsInTable 560740aa-012e-7281-172d-0000003c7d78|StoredPrepared&|8c9400a1-012e-7281-172d-0000003c7d78|Conglomerate 0ddd00a9-012e-7281-172d-0000003c7d78|Trigger |560740aa-012e-7281-172d-0000003c7d78|StoredPrepared& 0ddd00a9-012e-7281-172d-0000003c7d78|Trigger |23ce809c-012e-7281-172d-0000003c7d78|Table 560740aa-012e-7281-172d-0000003c7d78|StoredPrepared&|23ce809c-012e-7281-172d-0000003c7d78|Table 5 rows selected But after the ALTER TABLE DROP COLUMN, it shows 3 extra rows, which is not correct select * from sys.sysdepends; 560740aa-012e-7281-172d-0000003c7d78|StoredPrepared&|846c00a0-012e-7281-172d-0000003c7d78|ColumnsInTable 560740aa-012e-7281-172d-0000003c7d78|StoredPrepared&|8c9400a1-012e-7281-172d-0000003c7d78|Conglomerate 0ddd00a9-012e-7281-172d-0000003c7d78|Trigger |560740aa-012e-7281-172d-0000003c7d78|StoredPrepared& 0ddd00a9-012e-7281-172d-0000003c7d78|Trigger |23ce809c-012e-7281-172d-0000003c7d78|Table 560740aa-012e-7281-172d-0000003c7d78|StoredPrepared&|23ce809c-012e-7281-172d-0000003c7d78|Table 23ce809c-012e-7281-172d-0000003c7d78|Table |846c00a0-012e-7281-172d-0000003c7d78|Table 23ce809c-012e-7281-172d-0000003c7d78|Table |846c00a0-012e-7281-172d-0000003c7d78|Table 23ce809c-012e-7281-172d-0000003c7d78|Table |846c00a0-012e-7281-172d-0000003c7d78|Table 8 rows selected

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development