[
https://issues.apache.org/jira/browse/DERBY-3155?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-3155:
---------------------------------
Attachment: derby-3155-44-aa-lobsInTriggers.diff
Attaching derby-3155-44-aa-lobsInTriggers.diff. This patch materializes LOBs
before handing them to the WHEN [ NOT ] MATCHED clauses.
This patch fixes a bug which surfaced when MERGE statements fired triggers
which referenced LOBs. The LOBs could not be read because the scan underneath
them had been closed.
Right now the MERGE statement has 2 execution phases:
1) Scan the driving left join and buffer up qualified rows for processing by
the WHEN [ NOT ] MATCHED clauses.
2) Then process each clause in turn, performing an INSERT/UPDATE/DELETE on the
rows buffered up for it.
Between these two steps, we close the driving left join. This means that we
can't use references to LOBs inside the driving left join. Instead, we have to
do our work on LOB values. This may be an inefficient way to handle LOBs, and
we may want to optimize this later on. Perhaps we can leave the driving left
join open. However, then I think we will run into the problems we've seen with
reading the same LOB reference more than once. I do not want to fix those
problems as part of implementing MERGE.
Touches the following files:
-------------
M java/engine/org/apache/derby/impl/sql/execute/MergeResultSet.java
Materialize LOBs from their references before buffering them up for handling by
the WHEN [ NOT ] MATCHED clauses.
-------------
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java
Tests for MERGE-fired triggers which reference LOBs.
> Support for SQL:2003 MERGE statement
> ------------------------------------
>
> Key: DERBY-3155
> URL: https://issues.apache.org/jira/browse/DERBY-3155
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Trejkaz
> Assignee: Rick Hillegas
> Labels: derby_triage10_10
> Attachments: MergeStatement.html, MergeStatement.html,
> MergeStatement.html, MergeStatement.html, derby-3155-01-ac-grammar.diff,
> derby-3155-02-ag-fixParserWarning.diff,
> derby-3155-03-ae-backingStoreHashtableWithRowLocation.diff,
> derby-3155-03-af-backingStoreHashtableWithRowLocation.diff,
> derby-3155-03-ag-backingStoreHashtableWithRowLocation.diff,
> derby-3155-03-ah-backingStoreHashtableWithRowLocation.diff,
> derby-3155-04-ae-deleteAction.diff, derby-3155-04-af-deleteAction.diff,
> derby-3155-05-aa-triggerTransitionTableAsTarget.diff,
> derby-3155-06-aa-triggerTransitionTableAsSource.diff,
> derby-3155-07-ad-insertAction.diff, derby-3155-08-ah-updateAction.diff,
> derby-3155-09-aa-correlationNames.diff,
> derby-3155-10-aa-correlationNames.diff,
> derby-3155-11-ab-beforeTriggersCantFireMerge.diff,
> derby-3155-12-aa-canOmitInsertColumnList.diff,
> derby-3155-13-aa-allowSystemAndTempTables.diff,
> derby-3155-14-aa-replaceCorrelationNamesOnLeftSideOfSETclauses.diff,
> derby-3155-15-aa-replumbMergeResultSetCleanup.diff,
> derby-3155-16-aa-treatCurrentRowLocationNodeLikeBaseColumnNode.diff,
> derby-3155-17-aa-serializingRowLocations.diff,
> derby-3155-18-aa-basicView.diff,
> derby-3155-19-aa-forbidSubqueriesInMatchedClauses.diff,
> derby-3155-20-aa-reworkColumnMatching.diff,
> derby-3155-21-ac-cleanupAndForbidSynonyms.diff,
> derby-3155-22-ad-testIdentifiersOnLeftSideOfSetClauses.diff,
> derby-3155-23-aa-forbidDerivedColumnLists.diff,
> derby-3155-24-aa-supportParameters.diff,
> derby-3155-25-aa-parametersAsInsertValues.diff,
> derby-3155-26-aa-copyRowLocationForIndexScans.diff,
> derby-3155-27-aa-adjustMatchingRefinements.diff,
> derby-3155-28-aa-cardinalityViolations.diff,
> derby-3155-29-aa-missingSchema.diff,
> derby-3155-30-ab-moreCorrelationNames.diff,
> derby-3155-31-aa-deletePrivs.diff, derby-3155-32-aa-newTestFunction.diff,
> derby-3155-33-ab-insertPrivs.diff, derby-3155-34-aa-updatePrivs.diff,
> derby-3155-34-ab-updatePrivs.diff, derby-3155-35-aa-allPrivsTest.diff,
> derby-3155-36-aa-lockModeComment.diff, derby-3155-37-aa-printSubNodes.diff,
> derby-3155-38-aa-datatypes.diff, derby-3155-39-aa-fixBuild.diff,
> derby-3155-40-aa-bigLobs.diff, derby-3155-41-aa-nullGeneratedColumns.diff,
> derby-3155-42-aa-triggersAndGeneratedColumns.diff,
> derby-3155-43-aa-eliminateDuplicateColumnRefs.diff,
> derby-3155-44-aa-lobsInTriggers.diff
>
>
> A relatively common piece of logic in a database application is to check for
> a row's existence and then either update or insert depending on its existence.
> SQL:2003 added a MERGE statement to perform this operation. It looks like
> this:
> MERGE INTO table_name USING table_name ON (condition)
> WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
> WHEN NOT MATCHED THEN INSERT column1 [, column2 ...] VALUES (value1 [,
> value2 ...])
> At the moment, the only workaround for this would be to write a stored
> procedure to do the same operation, or to implement the logic client-side.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
