Stanimir Stamenkov created DERBY-7144:
-----------------------------------------
Summary: 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
Environment: Windows 10; JDK 11; Derby 10.14.0.2.
As far as I've observed the problem persists in Derby 10.15 and 10.16.
Reporter: Stanimir Stamenkov
Attachments: bug-demo.zip
_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)
