Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6371

DROP COLUMN looks in wrong schema when checking trigger dependencies

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.10.1.1, 10.10.2.0
    • 10.10.2.0, 10.11.1.1
    • SQL
    • None
    • Repro attached

    Description

      If you define a trigger in another schema than the current schema, and the trigger action references tables in the current schema without explicit schema names, ALTER TABLE DROP COLUMN may get confused when checking dependencies.

      For example:

      ij> create table s1.t1(x int, y int);
      0 rows inserted/updated/deleted
      ij> create table s1.t2(x int, y int);
      0 rows inserted/updated/deleted
      ij> set schema s1;
      0 rows inserted/updated/deleted
      ij> create trigger s2.tr after update of x on t1 for each row insert into t2 select x from t1;
      0 rows inserted/updated/deleted
      ij> alter table s1.t1 drop column y restrict;
      ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'Y' because TRIGGER 'TR' is dependent on that object.

      The ALTER TABLE statement should have succeeded, because the trigger does not depend on column Y.

      If you create dummy tables in S2 with the same names as those in S1, the same ALTER TABLE statement succeeds:

      ij> create table s2.t1(x int);
      0 rows inserted/updated/deleted
      ij> create table s2.t2(x int);
      0 rows inserted/updated/deleted
      ij> alter table s1.t1 drop column y restrict;
      0 rows inserted/updated/deleted

      The existence of these unrelated tables should not affect whether you're allowed to drop the column.

      Attachments

        1. derby-6371-2a.diff
          2 kB
          Knut Anders Hatlen
        2. derby-6371-1a.diff
          6 kB
          Knut Anders Hatlen
        3. compSchema.diff
          0.9 kB
          Knut Anders Hatlen

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            knutanders Knut Anders Hatlen
            knutanders Knut Anders Hatlen
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment