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

Stale data retrieved when using new collation=TERRITORY_BASED:PRIMARY feature

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 10.8.1.2, 10.9.1.0
    • Fix Version/s: 10.8.2.2, 10.9.1.0
    • Component/s: Store
    • Labels:
      None
    • Environment:
      Mac OS X, Windows
    • Bug behavior facts:
      Data corruption

      Description

      Our product recently upgraded to version 10.8.1.2 in order to take advantage of the new 'case-insensitive' mode offered by Derby in the form of the "collation=TERRITORY_BASED:PRIMARY" connection parameter.

      Unfortunately, we have run into an issue whereby stale data appears to be retrieved from an index, even though the data in the table itself has changed.

      You can see this issue in the IJ session below. The database in question was created using this Java parameter when invoking IJ:

      -Dij.database=jdbc:derby:test;create=true;collation=TERRITORY_BASED:PRIMARY

      Here is the IJ session:

      CONNECTION0* - jdbc:derby:test

      • = current connection

      ij> CREATE TABLE tag (
      tag_id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
      tag VARCHAR(255) NOT NULL,
      CONSTRAINT tag_pk PRIMARY KEY (tag_id),
      CONSTRAINT tag_tag_unique UNIQUE (tag)
      );
      0 rows inserted/updated/deleted

      ij> – first insert a value 'Test', note the upper-case 'T' in 'Test'
      ij> INSERT INTO tag (tag) VALUES ('Test');
      1 row inserted/updated/deleted

      ij> SELECT * FROM tag;
      TAG_ID |TAG
      --------------------------------------------------------------------------------------------------------------------------------------------
      1 |Test

      1 row selected

      ij> – Now delete the row
      ij> DELETE FROM tag WHERE tag='Test';
      1 row inserted/updated/deleted

      ij> – You could run another SELECT here to verify it is gone, but it is.

      ij> – Now insert a new value 'test', note the lower-case 't' in 'test'
      ij> INSERT INTO tag (tag) VALUES ('test');
      1 row inserted/updated/deleted

      ij> – Now verify that the table contains only the lower-case version: 'test'
      ij> SELECT * FROM tag;
      TAG_ID |TAG
      --------------------------------------------------------------------------------------------------------------------------------------------
      2 |test

      1 row selected

      ij> – Now, here is the bug.
      ij> SELECT tag FROM tag;
      TAG
      --------------------------------------------------------------------------------------------------------------------------------
      Test

      1 row selected
      ij>

      Note in the last SELECT we specify the 'tag' column specifically. When we 'SELECT *', Derby performs a table-scan and the result is correct. However, when we 'SELECT tag', Derby appears to use the index created for the 'tag_tag_unique' unique constraint. As an optimization Derby, like many databases, will use values directly from the index in the case where the index covers all requested columns.

      The bigger question is, why doesn't the DELETE action cause the entry in the tag_tag_unique index to be deleted? Is this a further optimization? If so, it is imperative that the index at least be updated when the new value is inserted.

      This is rather a severe bug for us that causes stale data to be returned.

        Attachments

        1. derby-5367-4c-fix_with_optimization_improved.diff
          22 kB
          Kristian Waagan
        2. derby-5367-4b-fix_with_optimization_improved.diff
          21 kB
          Kristian Waagan
        3. derby-5367-4a-fix_with_optimization_improved.diff
          5 kB
          Kristian Waagan
        4. derby-5367-3a-update_field_by_field_preview.diff
          1 kB
          Kristian Waagan
        5. derby-5367-2a-minimal_fix.diff
          2 kB
          Kristian Waagan
        6. derby-5367-1b-update_row_fully.stat
          0.3 kB
          Kristian Waagan
        7. derby-5367-1b-update_row_fully.diff
          8 kB
          Kristian Waagan
        8. derby-5367-1a-update_row_fully.diff
          4 kB
          Kristian Waagan

          Activity

            People

            • Assignee:
              kristwaa Kristian Waagan
              Reporter:
              brettw Brett Wooldridge
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: