[DERBY-7144] MERGE INSERT failing when target has GENERATED IDENTITY column

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.14.2.0, 10.15.2.0, 10.16.1.1
• Fix Version/s: 10.15.2.1, 10.16.1.2, 10.17.1.0
• Component/s: SQL
• Environment: Windows 10, JDK 8, Derby 10.14.2.0; ; Windows 10, JDK 11, Derby 10.15.2.0; ; Windows 10, JDK 17, Derby 10.16.1.1.
• Issue & fix info: Repro attached, Workaround attached
• Bug behavior facts: Crash, Seen in production
• Assignee: Richard N. Hillegas
• Reporter: Stanimir Stamenkov
• Created: 30/Jul/22 11:32
• Updated: 11/Oct/22 16:34
• Resolved: 19/Aug/22 15:03

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.

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