[ https://issues.apache.org/jira/browse/DERBY-7144?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Stanimir Stamenkov updated DERBY-7144: -------------------------------------- Attachment: (was: bug-demo2.zip) > MERGE INSERT failing when target has GENERATED INDENTITY column > --------------------------------------------------------------- > > Key: DERBY-7144 > URL: https://issues.apache.org/jira/browse/DERBY-7144 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.14.2.0, 10.15.2.0, 10.16.1.1 > 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. > Reporter: Stanimir Stamenkov > Priority: Major > Attachments: derby.log, sysinfo.out > > > _TL;DR:_ The following statement fails (most often) when the target table has > a GENERATED BY DEFAULT AS IDENTITY primary key: > {code:sql} > 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) > {code} > {noformat} > 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 > {noformat} > With the debug-version JARs I'm getting: > {noformat} > 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 > {noformat} > The target table definition is: > {code:sql} > 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) > ) > {code} > 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: > {noformat} > $ javac -d classes src/net/example/derby/*.java > {noformat} > Run: > {noformat} > $ java -cp "classes;lib/*" net.example.derby.BugDemo > Usage (one or more): -seed [limit] | -merge | -workaround | -print > {noformat} > To see the problem: > {noformat} > $ java -cp "classes;lib/*" net.example.derby.BugDemo -merge > {noformat} > --- > There's something funny here, the following succeeds: > {noformat} > $ java -cp "classes;lib/*" net.example.derby.BugDemo -seed -merge -print > {noformat} > The {{-seed}} option prepopulates the target table causing the MERGE > statement to result in UPDATEs only. > The following doesn't: > {noformat} > $ java -cp "classes;lib/*" net.example.derby.BugDemo -seed 5 -merge -print > {noformat} > 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: > {noformat} > $ java -cp "classes;lib/*" net.example.derby.BugDemo -seed 6 -merge -print > {noformat} > If I remove the GENERATED BY DEFAULT AS IDENTITY column from the target > table, the problem is not seen, also. -- This message was sent by Atlassian Jira (v8.20.10#820010)