[JIRA] Created: (CAY-548) remove uncomitted flattened many-to-many

["Tore Halset (JIRA)" <ji...@apache.org>]

remove uncomitted flattened many-to-many
----------------------------------------

         Key: CAY-548
         URL: http://issues.apache.org/cayenne/browse/CAY-548
     Project: Cayenne
        Type: Bug

  Components: Cayenne Core Library  
    Versions: 1.2 [BETA]    
 Environment: Derby/PostgreSQL
    Reporter: Tore Halset
     Fix For: 1.2 [BETA]


Given the following flattened relationship:
Artist<<-->>ArtGroup

The many-to-many join table are named artist_group and has (artist_id and group_id) as a primary key.

I first create 3 ArtGroups. No problem - inserted and everything is ok:
        ArtGroup ag1 = (ArtGroup) ctxt.createAndRegisterNewObject(ArtGroup.class);
        ag1.setName("ag1");
        ArtGroup ag2 = (ArtGroup) ctxt.createAndRegisterNewObject(ArtGroup.class);
        ag2.setName("ag2");
        ArtGroup ag3 = (ArtGroup) ctxt.createAndRegisterNewObject(ArtGroup.class);
        ag3.setName("ag3");
        ctxt.commitChanges();

Then I create an Artist, add all the ArtGroups and remove one of them. This is done by a gui as the user are (re-)selecting ArtGroups for the current Artist.

        Artist art = (Artist) ctxt.createAndRegisterNewObject(Artist.class);
        art.setArtistName("a1");
        art.addToGroupArray(ag1);
        art.addToGroupArray(ag2);
        art.addToGroupArray(ag3);
        art.removeFromGroupArray(ag2);
        ctxt.commitChanges();

The problem here is the sql that is generated:
INFO  QueryLogger: --- will run 3 queries.
INFO  QueryLogger: --- transaction started.
INFO  QueryLogger: INSERT INTO ARTIST (ARTIST_ID, ARTIST_NAME, DATE_OF_BIRTH) VALUES (?, ?, ?)
INFO  QueryLogger: [bind: 204, 'a1', NULL]
INFO  QueryLogger: === updated 1 row.
INFO  QueryLogger: INSERT INTO ARTIST_GROUP (ARTIST_ID, GROUP_ID) VALUES (?, ?)
INFO  QueryLogger: [bind: 204, 212]
INFO  QueryLogger: === updated 1 row.
INFO  QueryLogger: [bind: 204, 210]
INFO  QueryLogger: === updated 1 row.
INFO  QueryLogger: DELETE FROM ARTIST_GROUP WHERE ARTIST_ID = ? AND GROUP_ID = ?
INFO  QueryLogger: [bind: NULL, 211]
INFO  QueryLogger: === updated 0 rows.
INFO  QueryLogger: +++ transaction committed.

PostgreSQL does not like the delete query:
INFO  QueryLogger: *** error.
org.postgresql.util.PSQLException: No value specified for parameter 2.
	at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:102)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2485)
	at org.objectstyle.cayenne.access.jdbc.BatchAction.runAsBatch(BatchAction.java:164)
	at org.objectstyle.cayenne.access.jdbc.BatchAction.performAction(BatchAction.java:114)
	at org.objectstyle.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:95)
	at org.objectstyle.cayenne.access.DataNode.performQueries(DataNode.java:309)
	at org.objectstyle.cayenne.access.DataDomainFlushAction.runQueries(DataDomainFlushAction.java:255)
	at org.objectstyle.cayenne.access.DataDomainFlushAction.flush(DataDomainFlushAction.java:177)
	at org.objectstyle.cayenne.access.DataDomain.onSyncFlush(DataDomain.java:828)
	at org.objectstyle.cayenne.access.DataDomain$2.transform(DataDomain.java:799)
	at org.objectstyle.cayenne.access.DataDomain.runInTransaction(DataDomain.java:854)
	at org.objectstyle.cayenne.access.DataDomain.onSync(DataDomain.java:796)
	at org.objectstyle.cayenne.access.DataContext.flushToParent(DataContext.java:1261)
	at org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.java:1165)

Derby does not like it either:
INFO  QueryLogger: *** error.
ERROR 07000: At least one parameter to the current statement is uninitialized.
	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
	at org.apache.derby.impl.sql.execute.BaseActivation.throwIfMissingParms(Unknown Source)
	at org.apache.derby.exe.acf526c18ex010bx3786x9a0dx000000047df04f.execute(Unknown Source)
	at org.apache.derby.impl.sql.GenericActivationHolder.execute(Unknown Source)
	at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
	at org.objectstyle.cayenne.access.jdbc.BatchAction.runAsIndividualQueries(BatchAction.java:224)
	at org.objectstyle.cayenne.access.jdbc.BatchAction.performAction(BatchAction.java:117)
	at org.objectstyle.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:95)
	at org.objectstyle.cayenne.access.DataNode.performQueries(DataNode.java:309)
	at org.objectstyle.cayenne.access.DataDomainFlushAction.runQueries(DataDomainFlushAction.java:255)
	at org.objectstyle.cayenne.access.DataDomainFlushAction.flush(DataDomainFlushAction.java:177)
	at org.objectstyle.cayenne.access.DataDomain.onSyncFlush(DataDomain.java:828)
	at org.objectstyle.cayenne.access.DataDomain$2.transform(DataDomain.java:799)
	at org.objectstyle.cayenne.access.DataDomain.runInTransaction(DataDomain.java:854)
	at org.objectstyle.cayenne.access.DataDomain.onSync(DataDomain.java:796)
	at org.objectstyle.cayenne.access.DataContext.flushToParent(DataContext.java:1261)
	at org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.java:1165)

Looks like there are at two errors here:
 1. The delete-query should not have been issued.
 2. PostgreSQL and Derby complain about missing parameters in the query. hsqldb does not complain.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/cayenne/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira