Details
Description
TL;DR: The following statement fails (most often) when the target table has a GENERATED BY DEFAULT AS IDENTITY primary key:
MERGE INTO AGGREGATEDATA target USING TABLE (AGGREGATE_BULK_DATA()) source ON target.CATEGORY = source.CATEGORY AND target.AGGDATE = source.AGGDATE WHEN MATCHED THEN UPDATE SET VALUE = target.VALUE + source.VALUE, ATTIME = CASE WHEN source.ATTIME < target.ATTIME THEN target.ATTIME ELSE source.ATTIME END, AGGCOUNT = target.AGGCOUNT + source.AGGCOUNT WHEN NOT MATCHED THEN INSERT (CATEGORY, VALUE, ATTIME, AGGDATE, AGGCOUNT) VALUES (source.CATEGORY, source.VALUE, source.ATTIME, source.AGGDATE, source.AGGCOUNT)
java.sql.SQLException: Java exception: ': java.lang.NullPointerException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.seeNextException(Unknown Source) at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(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.executeLargeUpdate(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source) at net.example.derby.BugDemo.mergeData(BugDemo.java:124) at net.example.derby.BugDemo.run(BugDemo.java:242) at net.example.derby.BugDemo.main(BugDemo.java:212) Caused by: ERROR XJ001: Java exception: ': java.lang.NullPointerException'. at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown Source) ... 15 more Caused by: java.lang.NullPointerException at org.apache.derby.impl.store.access.conglomerate.ConglomerateUtil.createFormatIds(Unknown Source) at org.apache.derby.impl.store.access.heap.Heap.create(Unknown Source) at org.apache.derby.impl.store.access.heap.HeapConglomerateFactory.createConglomerate(Unknown Source) at org.apache.derby.impl.store.access.RAMTransaction.createConglomerate(Unknown Source) at org.apache.derby.impl.sql.execute.TemporaryRowHolderImpl.insert(Unknown Source) at org.apache.derby.impl.sql.execute.MatchingClauseConstantAction.bufferThenRow(Unknown Source) at org.apache.derby.impl.sql.execute.MergeResultSet.collectAffectedRows(Unknown Source) at org.apache.derby.impl.sql.execute.MergeResultSet.open(Unknown Source) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source) ... 7 more
With the debug-version JARs I'm getting:
java.sql.SQLException: Java exception: 'ASSERT FAILED row template is null for column[0].: org.apache.derby.shared.common.sanity.AssertFailure'.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:115)
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:141)
at org.apache.derby.impl.jdbc.Util.seeNextException(Util.java:252)
at org.apache.derby.impl.jdbc.Util.javaException(Util.java:274)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:437)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2405)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:88)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1436)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1709)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeLargeUpdate(EmbedPreparedStatement.java:320)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPreparedStatement.java:309)
at net.example.derby.BugDemo.mergeData(BugDemo.java:124)
at net.example.derby.BugDemo.run(BugDemo.java:242)
at net.example.derby.BugDemo.main(BugDemo.java:212)
Caused by: ERROR XJ001: Java exception: 'ASSERT FAILED row template is null for column[0].: org.apache.derby.shared.common.sanity.AssertFailure'.
at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:290)
at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory.java:170)
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:75)
... 14 more
Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED row template is null for column[0].
at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:162)
at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:147)
at org.apache.derby.impl.store.access.conglomerate.ConglomerateUtil.createFormatIds(ConglomerateUtil.java:145)
at org.apache.derby.impl.store.access.heap.Heap.create(Heap.java:302)
at org.apache.derby.impl.store.access.heap.HeapConglomerateFactory.createConglomerate(HeapConglomerateFactory.java:213)
at org.apache.derby.impl.store.access.RAMTransaction.createConglomerate(RAMTransaction.java:803)
at org.apache.derby.impl.sql.execute.TemporaryRowHolderImpl.insert(TemporaryRowHolderImpl.java:303)
at org.apache.derby.impl.sql.execute.MatchingClauseConstantAction.bufferThenRow(MatchingClauseConstantAction.java:250)
at org.apache.derby.impl.sql.execute.MergeResultSet.collectAffectedRows(MergeResultSet.java:277)
at org.apache.derby.impl.sql.execute.MergeResultSet.open(MergeResultSet.java:114)
at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:472)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:351)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1344)
... 6 more
The target table definition is:
CREATE TABLE AGGREGATEDATA ( ID BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY NOT NULL, CATEGORY INTEGER NOT NULL, VALUE DOUBLE NOT NULL, ATTIME TIMESTAMP NOT NULL, AGGDATE DATE NOT NULL, AGGCOUNT INTEGER NOT NULL, UNIQUE (AGGDATE, CATEGORY) )
The AGGREGATE_BULK_DATA() table function produces the same result modulo the ID column. Find more details in sqlStatements.properties in the attached bug-demo.zip
– Extract; Copy the Derby JARs into a lib/ subdirectory; Compile:
$ javac -d classes src/net/example/derby/*.java
Run:
$ java -cp "classes;lib/*" net.example.derby.BugDemo Usage (one or more): -seed [limit] | -merge | -workaround | -print
To see the problem:
$ java -cp "classes;lib/*" net.example.derby.BugDemo -merge
—
There's something funny here, the following succeeds:
$ java -cp "classes;lib/*" net.example.derby.BugDemo -seed -merge -print
The -seed option prepopulates the target table causing the MERGE statement to result in UPDATEs only.
The following doesn't:
$ java -cp "classes;lib/*" net.example.derby.BugDemo -seed 5 -merge -print
This seeds some data into the target table causing the MERGE statement to result in UPDATEs and INSERTs as well. In the given demo if I seed 6 or more records, the problem is not seen:
$ java -cp "classes;lib/*" net.example.derby.BugDemo -seed 6 -merge -print
If I remove the GENERATED BY DEFAULT AS IDENTITY column from the target table, the problem is not seen, also.
Attachments
Attachments
Issue Links
- is related to
-
DERBY-7145 MERGE UPDATE failing: Restore of a serializable or SQLData object of class , attempted to read more data than was originally stored
-
- Open
-