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

SYSCS_COMPRESS_TABLE disables unique constraints

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.4.1.3, 10.4.2.0, 10.4.2.1, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0
    • Fix Version/s: 10.4.2.1, 10.5.3.1, 10.6.2.1, 10.7.1.1
    • Component/s: SQL
    • Labels:
      None
    • Environment:
    • Issue & fix info:
      Release Note Needed, Repro attached
    • Bug behavior facts:
      Data corruption, Deviation from standard, Seen in production

      Description

      It appears that running SYSCS_UTIL.SYSCS_COMPRESS_TABLE on a table with a null-able unique constraint will disable the unique constraint. The script
      below should reproduce the problem. The expected behaviour is for the second insert to fail due to the unique constraint but instead it is allowed. The second insert will fail as expected if either the call to SYSCS_COMPRESS_TABLE is skipped or if the column is declared NOT NULL.

      I have reproduced the problem using embedded Derby 10.5.1.1, 10.5.3.0 and 10.6.1.0 using ij.

      CREATE TABLE TABLE1(NAME1 INT UNIQUE);

      CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TABLE1', 1);

      INSERT INTO TABLE1(NAME1) VALUES(1);

      INSERT INTO TABLE1(NAME1) VALUES(1);

      SELECT * FROM TABLE1;

      DROP TABLE TABLE1;

        Attachments

        1. DERBY4677_diff_patch1.txt
          9 kB
          Mamta A. Satoor
        2. DERBY4677_stat_patch1.txt
          0.4 kB
          Mamta A. Satoor
        3. releaseNote.html
          4 kB
          Lily Wei
        4. releaseNote.html
          4 kB
          Mamta A. Satoor
        5. releaseNote.html
          4 kB
          Mamta A. Satoor
        6. releaseNote.html
          4 kB
          Mamta A. Satoor

          Activity

            People

            • Assignee:
              mamtas Mamta A. Satoor
              Reporter:
              b.mason@adinstruments.com Brett Mason
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: