db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brett Wooldridge (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DERBY-5367) Stale data retrieved when using new collation=TERRITORY_BASED:PRIMARY feature
Date Tue, 02 Aug 2011 05:45:27 GMT
Stale data retrieved when using new collation=TERRITORY_BASED:PRIMARY feature

                 Key: DERBY-5367
                 URL: https://issues.apache.org/jira/browse/DERBY-5367
             Project: Derby
          Issue Type: Bug
          Components: Store
    Affects Versions:
         Environment: Mac OS X, Windows
            Reporter: Brett Wooldridge
            Priority: Critical

Our product recently upgraded to version 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:


Here is the IJ session:

CONNECTION0* - 	jdbc:derby:test
* = current connection

ij> CREATE TABLE tag (
    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;

1 row selected

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.

This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira


View raw message