[
https://issues.apache.org/jira/browse/DERBY-7144?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17573387#comment-17573387
]
Stanimir Stamenkov edited comment on DERBY-7144 at 7/31/22 11:34 AM:
---------------------------------------------------------------------
I want to point out the magic numbers (ratio?) again:
{noformat:title=MERGE resulting in UPDATEs only – OK}
$ java -cp "classes;lib/*" net.example.derby.BugDemo -seed -merge -print
{noformat}
{noformat:title=MERGE resulting in 6 UPDATEs and 5 INSERTs – OK}
$ java -cp "classes;lib/*" net.example.derby.BugDemo -seed 6 -merge -print
{noformat}
{noformat:title=MERGE resulting in 5 UPDATEs and 6 INSERTs – FAILING}
$ java -cp "classes;lib/*" net.example.derby.BugDemo -seed 5 -merge -print
{noformat}
{noformat:title=MERGE resulting in INSERTs only – FAILING}
$ java -cp "classes;lib/*" net.example.derby.BugDemo -merge -print
{noformat}
The {{-workaround}} performs multiple MERGEs of a single row, for each row of
the source.
was (Author: stanio):
I want to point out the magic numbers (ratio?) again:
{noformat:title=MERGE resulting in UPDATEs only – OK}
$ java -cp "classes;lib/*" net.example.derby.BugDemo -seed -merge -print
{noformat}
{noformat:title=MERGE resulting in 6 UPDATEs and 5 INSERTs – OK}
$ java -cp "classes;lib/*" net.example.derby.BugDemo -seed 6 -merge -print
{noformat}
{noformat:title=MERGE resulting in 5 UPDATEs and 6 INSERTs – FAILING}
$ java -cp "classes;lib/*" net.example.derby.BugDemo -seed 5 -merge -print
{noformat}
{noformat:title=MERGE resulting in INSERTs only – FAILING}
$ java -cp "classes;lib/*" net.example.derby.BugDemo -merge -print
{noformat}
> 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: bug-demo.zip, bug-demo2.zip, 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)
