[
https://issues.apache.org/jira/browse/DERBY-7144?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17573937#comment-17573937
]
Richard N. Hillegas commented on DERBY-7144:
--------------------------------------------
Attaching a further revised version of derby-7144.sql. This script strips the
target table down to one column and it strips the source table down to one
column which is not involved in the driving join of the MERGE statement. I
changed the identity column to ALWAYS rather than BY DEFAULT. The problem still
occurs, provided that there are at least 6 rows in the source table.
Here is the revised script:
{noformat}
CONNECT 'jdbc:derby:memory:db;create=true';
CREATE TABLE AGGREGATEDATA (
ID BIGINT GENERATED ALWAYS AS IDENTITY
);
CREATE TABLE sourceData
(
CATEGORY INTEGER
);
INSERT INTO sourceData VALUES
(10),
(20),
(30),
(40),
(50),
(60)
;
SELECT * FROM sourceData;
-- fails with this error:
--
-- ERROR XJ001: Java exception: 'ASSERT FAILED row template is null for
column[0].: org.apache.derby.shared.common.sanity.AssertFailure'.
--
-- kills the connection
MERGE INTO AGGREGATEDATA target
USING sourceData source
ON 1 < 2
WHEN NOT MATCHED THEN
INSERT (id)
VALUES (DEFAULT);
{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-7144-1.sql,
> derby-7144-2.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)
