[
https://issues.apache.org/jira/browse/DERBY-6623?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14041768#comment-14041768
]
Dag H. Wanvik commented on DERBY-6623:
--------------------------------------
This time, the instability is in the method
EmbedDatabaseMetaData#getBestRowIdentifier. The table in question looks like
this:
{code}
create table brit3 (i int not null unique, j int not null unique) -- cf line
3405 in DatabaseMetaDataTest
{code}
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:
{code}
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
{code}
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:
{code}
// 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);
}
{code}
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.
> DatabaseMetaDataTest fails intermittently
> -----------------------------------------
>
> Key: DERBY-6623
> URL: https://issues.apache.org/jira/browse/DERBY-6623
> Project: Derby
> Issue Type: Bug
> Components: Test
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Fix For: 10.11.0.0
>
> Attachments: derby-6623.diff
>
>
> Saw this on the trunk
> [[http://download.java.net/javadesktop/derby/request_5592737/javadb-task-3883745.html][regression
> tests]]:
> testGetXXportedKeys:
> {code}
> 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)
> {code}
--
This message was sent by Atlassian JIRA
(v6.2#6252)
