You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Richard N. Hillegas (Jira)" <ji...@apache.org> on 2022/08/19 15:04:00 UTC
[jira] [Resolved] (DERBY-7144) MERGE INSERT failing when target has GENERATED INDENTITY column
[ https://issues.apache.org/jira/browse/DERBY-7144?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Richard N. Hillegas resolved DERBY-7144.
----------------------------------------
Fix Version/s: 10.16.1.2
10.17.0.0
Resolution: Fixed
> 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
> Fix For: 10.16.1.2, 10.17.0.0
>
> Attachments: bug-demo.zip, bug-demo2.zip, derby-7144-01-aa-reformatTemporaryRowHolderImpl.diff, derby-7144-02-ae-reformat.diff, derby-7144-03-aa-computeRowTemplateAndTrackIdentityColumnsBetter.diff, derby-7144-1.sql, derby-7144-2.sql, derby-7144-3.sql, derby-7144-default.sql, derby-7144.sql, 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)