Details
Description
Saw this on the trunk [http://download.java.net/javadesktop/derby/request_5592737/javadb-task-3883745.html[regression tests]]:
testGetXXportedKeys:
junit.framework.AssertionFailedError: Column value mismatch @ column 'FK_NAME', row 3:
Expected: >FKEY3<
Found: >FKEY2<
at org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1303)
at org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1215)
at org.apache.derbyTesting.junit.JDBC.assertFullResultSetMinion(JDBC.java:1102)
at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:1025)
at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:982)
at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.assertFullResultSet(DatabaseMetaDataTest.java:3930)
at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.testGetXXportedKeys(DatabaseMetaDataTest.java:4117)
at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:118)
at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBareOverridable(BaseJDBCTestCase.java:440)
at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBare(BaseJDBCTestCase.java:457)
Attachments
Attachments
- derby-6623.diff
- 48 kB
- Dag H. Wanvik
- derby-6623-2.diff
- 4 kB
- Dag H. Wanvik
- derby-6623-2a.diff
- 4 kB
- Dag H. Wanvik
- derby-6623-2a.status
- 0.9 kB
- Dag H. Wanvik
- derby-6623-3.diff
- 2 kB
- Dag H. Wanvik
Issue Links
- is related to
-
DERBY-6968 Instability in DatabaseMetaDataTest.testGetBestRowIdentifier
- Open
Activity
Looking at the API specification for DatabaseMetaData#getImported keys I see:
They are ordered by PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ.
For the foreign keys FKEY2 and FKEY3, I think all these values can be equal since they both refer to the same
primary key in the primary table ("C30", "S").
One solution would be have the test use JDBC#assertUnorderedResultSet.
Patch derby-6623.diff introduces JDBC#assertunorderedResultSet to solve the issue. It also cleans up a number of IDE warnings (missing Javadocs, old style loop code, variable shadowing).
Running regressions.
Commit 1603944 from Dag H. Wanvik in branch 'code/trunk'
[ https://svn.apache.org/r1603944 ]
DERBY-6623 DatabaseMetaDataTest fails intermittently
Patch derby-6623 introduced JDBC#assertunorderedResultSet to solve the
issue. It also cleans up a number of IDE warnings (missing Javadocs,
old style loop code, variable shadowing).
Reopening - last night I saw another error in the nightly regressions on trunk:
http://download.java.net/javadesktop/derby/request_5593027
with this signature:
org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.testGetBestRowIdentifier
stack trace.
junit.framework.AssertionFailedError: Column value mismatch @ column 'COLUMN_NAME', row 1:
Expected: >J<
Found: >I<
at org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1303)
at org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1215)
at org.apache.derbyTesting.junit.JDBC.assertFullResultSetMinion(JDBC.java:1102)
at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:1025)
at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:982)
at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.verifyBRIResults(DatabaseMetaDataTest.java:3729)
at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.testGetBestRowIdentifier(DatabaseMetaDataTest.java:3485)
at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:118)
at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBareOverridable(BaseJDBCTestCase.java:440)
at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBare(BaseJDBCTestCase.java:457)
This time, the instability is in the method EmbedDatabaseMetaData#getBestRowIdentifier. The table in question looks like this:
create table brit3 (i int not null unique, j int not null unique) -- cf line 3405 in DatabaseMetaDataTest
Now, the Javadoc for the DBMD method says:
"Retrieves a description of a table's optimal set of columns that uniquely identifies a row. They are ordered by SCOPE. "
In "metadata.properties" I see this internal query for this method:
getBestRowIdentifierUniqueConstraint=\
SELECT CONS.CONSTRAINTID, IDX.DESCRIPTOR.numberOfOrderedColumns() AS NUMCOLS \
FROM SYS.SYSSCHEMAS SCHEMAS, SYS.SYSTABLES TABS, \
SYS.SYSCONSTRAINTS cons, SYS.SYSKEYS keys, SYS.SYSCONGLOMERATES IDX \
WHERE TABS.TABLEID = conS.TABLEID AND SCHEMAS.SCHEMAID = TABS.SCHEMAID \
AND conS.CONSTRAINTID = KEYS.CONSTRAINTID AND IDX.DESCRIPTOR IS NOT NULL \
AND KEYS.CONGLOMERATEID = IDX.CONGLOMERATEID AND IDX.ISCONSTRAINT \
AND conS.type = 'U' \
AND ((1=1) OR ? IS NOT NULL) \
AND (SCHEMAS.SCHEMANAME LIKE ?) \
AND (TABS.TABLENAME=?) \
ORDER BY NUMCOLS
Now, in this case, the table has two columns which both are declared as UNIQUE NOT NULL and the query returns both, but it does not specify an order between them.
The code that analyzes the query (EmbedDatabaseeMetaData#doGetBestRowId) has this section:
// see if there is a primary key, use it. ps = getPreparedQuery("getBestRowIdentifierPrimaryKey"); : (not the case here, so we proceed to look at unique columns) // get the unique constraint with the fewest columns. ps = getPreparedQuery("getBestRowIdentifierUniqueConstraint"); ps.setString(1,catalogPattern); ps.setString(2,schemaPattern); ps.setString(3,table); rs = ps.executeQuery(); done = rs.next(); if (done) { constraintId = rs.getString(1); }
As can be seen, once a row is read, that row is used. The test asserts it to be "J", but in the failing regression it was in fact "I", which is allowed since no ordering is specified on which constraint to return first. I presume what happened was that another query plan was used which gave the rows in the opposite order, causing the assert error.
Both unique constraints are equally good here, so which should be returned? Both? The API Javadoc doesn't really address this scenario, nor does the Derby code.
My expectation would be that it only returned one of them. The javadoc says: "Retrieves a description of a table's optimal set of columns that uniquely identifies a row." And (I, J) sounds like a less optimal set than (I) or (J), since it's bigger and has some redundancy.
I think the current behaviour is fine, but I see no harm in adding some extra ordering so that it is deterministic which constraint is used in case of ties.
The reference manual actually has a topic that describes how getBestRowIdentifier() works. It's more detailed than the JDBC specification, but it does not say anything about ties.
Seen again during last night's run on Solaris_amd64_jdk8 against the head of the 10.11.1 trunk: http://javadb.se.oracle.com/javadb-testing/request_5593027/
Yes, I think users would appreciate if such queries were deterministic... I'll make a patch to that effect.
Attaching [^derby-6623-2], which adds an extra ordering using IndexDescriptor's toString-method. This imposes a total order on the rows returned from the metadata query, so hopefully we get rid of the Heisenbug. Note that this caused another unique constraint to be chosen in the example that failed. DababaseMetaDataTest passed. Re-running regressions.
One could argue that it fragile to use the toString method in this way. I did it to avoid having to extend the IndexDescriptor interface with another method with an ordering value. But we could consider
adding some such:
IndexDescriptor#getFirstBaseColumn
and order on that. Opinions?
Maybe it would be good enough to order by SYS.SYSCONSTRAINTS.CONSTRAINTNAME? So that in case of a tie the alphabetically first constraint/index would be chosen?
Attaching patch version 2b: changed to sorting on the output of Arrays.toString(IndexDescriptor.baseColumnPosition). This makes sorting stable iff:
- the row layout doesn't change
- no indexes or constraint are added or removed.
Mostly, indexes with early base columns will be sorted first (and selected as best), although not always: "[11,1]" sorts before "[2,1]", but the main thing is the same index will be selected for the same (unchanged) database always. Rerunning regressions.
Commit 1605478 from Dag H. Wanvik in branch 'code/trunk'
[ https://svn.apache.org/r1605478 ]
DERBY-6623 DatabaseMetaDataTest fails intermittently
Patch derby-6623-2a, which adds an extra ordering [1] using
IndexDescriptor's getBaseColumns method as input to
Arrays.toString(). This imposes a total order on the rows returned
from the metadata query, so hopefully we get rid of the
Heisenbug. Note that this caused another unique constraint to be
chosen in the example that failed.
[1] after the number of columns in the index: small numbers
before higher numbers since constraints with a small number of
columns are considered better
The ordering makes sorting stable iff:
- the row layout doesn't change
- no indexes or constraint are added or removed.
Mostly, indexes with early base columns will be sorted first (and
selected as best), although not always: "[11,1]" sorts before "[2,1]",
but the main thing is the same index will be selected for the same
(unchanged) database always.
Reopening this, I saw this error on http://download.java.net/javadesktop/derby/request_5594301/javadb-task-3907241.html
junit.framework.AssertionFailedError: Column value mismatch @ column 'FK_NAME', row 3:
Expected: >FKEY3<
Found: >FKEY2<
PKTABLE_CAT,PKTABLE_SCHEM,PKTABLE_NAME,PKCOLUMN_NAME,FKTABLE_CAT,FKTABLE_SCHEM,FKTABLE_NAME,FKCOLUMN_NAME,KEY_SEQ,UPDATE_RULE,DELETE_RULE,FK_NAME,PK_NAME,DEFERRABILITY
----------- ------------- ------------ ------------- ----------- ------------- ------------ ------------- ------- ----------- ----------- ------- ------- -------------
[, DMDT, KT1, VC10, , DMDT, REFTAB, VC10, 1, 3, 3, FKEY1, PRIMKEY, 7]
[, DMDT, KT1, I, , DMDT, REFTAB, I, 2, 3, 3, FKEY1, PRIMKEY, 7]
[, DMDT, KT1, C30, , DMDT, REFTAB, C30, 1, 3, 3, FKEY2, UNIQUEKEY, 7]
[, DMDT, KT1, C30, , DMDT, REFTAB, C30, 1, 3, 3, FKEY3, UNIQUEKEY, 7]
[, DMDT, KT1, S, , DMDT, REFTAB, S2, 2, 3, 3, FKEY2, UNIQUEKEY, 7]
[, DMDT, KT1, S, , DMDT, REFTAB, S, 2, 3, 3, FKEY3, UNIQUEKEY, 7]
[, DMDT, REFTAB, DPRIM, , DMDT, REFTAB, DFOR, 1, 3, 3, FKEYSELF, PKEY_REFTAB, 7]
at org.apache.derbyTesting.junit.BaseTestCase.newAssertionFailedError(BaseTestCase.java:1164)
at org.apache.derbyTesting.junit.JDBC.addRsToReport(JDBC.java:1878)
at org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1377)
at org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1275)
at org.apache.derbyTesting.junit.JDBC.assertFullResultSetMinion(JDBC.java:1137)
at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:1048)
at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:1005)
at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.assertFullResultSet(DatabaseMetaDataTest.java:4111)
at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.testGetXXportedKeys(DatabaseMetaDataTest.java:4331)
Now, the expected set of rows is:
{"",schema,"KT1","VC10","",schema,"REFTAB","VC10","1","3","3","FKEY1","PRIMKEY","7"}, {"",schema,"KT1","I","",schema,"REFTAB","I","2","3","3","FKEY1","PRIMKEY","7"}, {"",schema,"KT1","C30","",schema,"REFTAB","C30","1","3","3","FKEY3","UNIQUEKEY","7"}, {"",schema,"KT1","C30","",schema,"REFTAB","C30","1","3","3","FKEY2","UNIQUEKEY","7"}, {"",schema,"KT1","S","",schema,"REFTAB","S","2","3","3","FKEY3","UNIQUEKEY","7"}, {"",schema,"KT1","S","",schema,"REFTAB","S2","2","3","3","FKEY2","UNIQUEKEY","7"}, {"",schema,"REFTAB","DPRIM","",schema,"REFTAB","DFOR","1","3","3","FKEYSELF","PKEY_REFTAB","7"}};
so we can see that row #3 and #4 have changed places.
Attaching derby-6623-3.diff:
Another instance of the test neglecting to consider that the rows may
legitimately come back in another order. Switched to using
assertFullUnorderedResultSet instead of assertFullResultSet.
Commit 1615821 from Dag H. Wanvik in branch 'code/trunk'
[ https://svn.apache.org/r1615821 ]
DERBY-6623 DatabaseMetaDataTest fails intermittently
Another instance of the test neglecting to consider that the rows may
legimimately come back in another order. Switched to using
assertFullUnorderedResultSet instead of assertFullResultSet.
Commit 1616520 from Dag H. Wanvik in branch 'code/branches/10.11'
[ https://svn.apache.org/r1616520 ]
DERBY-6623 DatabaseMetaDataTest fails intermittently
Backported cleanly from trunk as:
svn merge -c 1615821 https://svn.apache.org/repos/asf/db/derby/code/trunk
Another instance of the test neglecting to consider that the rows may
legimimately come back in another order. Switched to using
assertFullUnorderedResultSet instead of assertFullResultSet.
I notice that the value FKEY3 (and FKEY2) in the test are foreign keys. Looking at the test, we are asking for imported keys (line 4116).The query we use (internally) for embedded metadata queries (cd java/engine/org/apache/derby/impl/jdbc/metadata.properties query "getImportedKeys") has the following
ORDER BY clause at the end:
As far as I can, it does not order the rows on FK_NAME, which is the foreign key name. This could possibly be the reason the rows come back in an unexpected order in the test.