[
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-51-aa-cleanup2.diff
Attaching derby-3155-51-aa-cleanup2.diff. This patch adds more comments to the
compiler code for the MERGE statement.
At this point, I would welcome a desk-check by anyone who has spare cycles.
Your questions and comments may discover actual bugs, suggest parts of the code
which need more testing, and generally help me improve the readability of this
code. The major classes to read are:
o org.apache.derby.impl.sql.compile.MergeNode
o org.apache.derby.impl.sql.compile.MatchingClauseNode
o org.apache.derby.impl.sql.execute.MergeResultSet
o org.apache.derby.impl.sql.execute.MatchingClauseConstantAction
In reading the code, I would suggest the following approach:
1) First read the header comment in MergeNode. That header comment lays out the
approach to implementing MERGE.
2) Then read the execution code: MergeResultSet and
MatchingClauseConstantAction.
3) Then read MergeNode itself.
4) Finish up with MatchingClauseNode.
The following principles guided the design:
i) Keep the execution code as simple as possible.
ii) Boost run-time performance by avoiding unnecessary expression evaluation.
iii) Re-use the existing INSERT/UPDATE/DELETE machinery wherever possible.
At this point, I think that the buggiest, most complex, and most brittle code
is in the compiler, concentrated in the following areas:
A) Name resolution. The MergeNode header comment explains why this is so tricky.
B) The compilation of expressions for INSERT and UPDATE values. This is
exceedingly complex in order to handle all sorts of special cases arising from
identity, generated, and default columns as well as columns involved in
constraints. Debugging this was difficult. However, I have not seen any
problems in this area for a long time.
Thanks!
Touches the following files:
M java/engine/org/apache/derby/impl/sql/compile/MergeNode.java
M java/engine/org/apache/derby/impl/sql/compile/MatchingClauseNode.java
> 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, derby-3155-45-aa-serialization.diff,
> derby-3155-46-aa-deferredDeletes.diff, derby-3155-47-aa-collations.diff,
> derby-3155-48-aa-indexScan.diff, derby-3155-49-aa-cleanup1.diff,
> derby-3155-50-aa-revampDeleteThenRows.diff, derby-3155-51-aa-cleanup2.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)
