Primary Key Column order lost (a problem if there are multiple Primary Keys)
----------------------------------------------------------------------------
Key: DDLUTILS-214
URL: https://issues.apache.org/jira/browse/DDLUTILS-214
Project: DdlUtils
Issue Type: Bug
Components: Core (No specific database)
Affects Versions: 1.0
Environment: Any
Reporter: Rijk van Haaften
Assignee: Thomas Dudziak
Priority: Critical
Fix For: 1.0
Symptom:
14558 [btpool0-5] DEBUG
org.apache.ddlutils.platform.postgresql.PostgreSqlPlatform - About to execute
SQL DELETE FROM "a" WHERE "p" = ? AND "q" = ?
The delete sometimes works, sometimes I get this:
14569 [btpool0-5] WARN
org.apache.ddlutils.platform.postgresql.PostgreSqlPlatform - Attempted to
delete a single row "a": q = 6, p = 3 in table "a" but changed 0 row(s).
Symptom analysis:
The query
DELETE FROM "a" WHERE "p" = ? AND "q" = ?
is filled in order (6,3), without regarding the field names (q = 6, p = 3)
resulting in
DELETE FROM "a" WHERE "p" = 6 AND "q" = 3
Code analysis
1. SqlBuilder.getDeleteSql
SqlBuilder.getDeleteSql uses the Map pkValues iterator to generate the prepared
statement.
2. PlatformImplBase.toColumknValues
This has a major problem: Map iterator order is not defined. Java
documentation: "Some map implementations, like the TreeMap class, make specific
guarantees as to their order; others, like the HashMap class, do not." In this
case, the Map used is created by PlatformImplBase.toColumknValues:
protected HashMap toColumnValues(SqlDynaProperty[] properties, DynaBean
bean) {
HashMap result = new HashMap();
...
}
so iterator order is undefined.
3. PlatformImplBase.delete (for example)
a)
String sql = createDeleteSql(model, dynaClass, primaryKeys, null);
createDeleteSql uses getDeleteSql (1) and due to (2), the order the columns in
the generated String sql is undefined. So, this could generate either
DELETE FROM "a" WHERE "p" = ? AND "q" = ?
DELETE FROM "a" WHERE "q" = ? AND "p" = ?
which one cannot be determined
b) setObject uses a deterministic array order sqlIndex to fill the ? - marks
SqlDynaProperty[] primaryKeys = dynaClass.getPrimaryKeyProperties();
for (int idx = 0; idx < primaryKeys.length; idx++)
{
setObject(statement, idx + 1, dynaBean, primaryKeys[idx]);
}
so there is a chance of 1/(number of PK columns) this will run correctly.
Solution:
use LinkedHashMap or TreeMap: they retain put-order, which is based on the
SqlDynaProperty[] so by definition has the same order as the setObject loop:
protected HashMap toColumnValues(SqlDynaProperty[] properties, DynaBean
bean)
{
HashMap result = new LinkedHashMap();
...
}
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
