[
https://issues.apache.org/jira/browse/DERBY-7144?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17583174#comment-17583174
]
Richard N. Hillegas commented on DERBY-7144:
--------------------------------------------
Thanks for the svn advice. I did as you recommended yesterday, in order to
merge the patch into the 10.14 codeline. The MergeStatementTest has the
expected extra two cases and it runs cleanly. That is an encouraging sign.
Unfortunately, there are a couple errors when I run the full test suite. I
believe that these are caused by changes to my environment since the last time
that I ran tests on the 10.14 branch. I have created a fresh 10.14 sandbox
without the patch and I am running the full suite there to see if the same
errors also occur in that clean sandbox.
> 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.15.2.1, 10.16.1.2, 10.17.0.0
>
> Attachments: branches-10.14.diff, 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, svn-merge.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)
