[
https://issues.apache.org/jira/browse/DERBY-7144?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17573878#comment-17573878
]
Richard N. Hillegas commented on DERBY-7144:
--------------------------------------------
Attaching derby-7144.sql, a simpler repro of the problem. This script creates a
target and a source table, puts some rows into the source table, and then
issues a MERGE statement. The MERGE fails as described by Stanimir and it kills
the connection.
Here is the script:
{noformat}
CONNECT 'jdbc:derby:memory:db;create=true';
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)
);
CREATE TABLE sourceData
(
CATEGORY INTEGER,
VALUE DOUBLE,
ATTIME TIMESTAMP,
AGGDATE DATE,
AGGCOUNT INTEGER
);
INSERT INTO sourceData VALUES
(1 , 45.67 , TIMESTAMP('2022-07-29 01:24:21.336'
), DATE('2022-07-29'), 3 ),
(4 , 37.15 , TIMESTAMP('2022-07-29 01:24:21.336'
), DATE('2022-07-29'), 3 ),
(2 , 123.34 , TIMESTAMP('2022-07-31 01:38:24.66'
), DATE('2022-07-31'), 3 ),
(1 , 78.9 , TIMESTAMP('2022-07-31 01:38:24.66'
), DATE('2022-07-31'), 2 ),
(3 , 1.2 , TIMESTAMP('2022-07-31 01:38:24.66'
), DATE('2022-07-31'), 1 ),
(4 , 5.6 , TIMESTAMP('2022-07-31 01:38:24.66'
), DATE('2022-07-31'), 1 )
;
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 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);
{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.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)
