[
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-04-af-deleteAction.diff
Attaching derby-3155-04-af-deleteAction.diff. This patch enables the DELETE
action of the MERGE statement, capitalizing on the row location work done by
derby-3155-03-ah-backingStoreHashtableWithRowLocation.diff. I am running tests
now.
With this patch, I am able to use the MERGE statement to DELETE rows. Various
combinations of the following conditions have been tested:
1) MERGE statements with and without refined conditions in the WHEN MATCHED
clause.
2) Search conditions which cause the optimizer to choose a nested-loop strategy.
3) Search conditions which cause the optimizer to choose a hash-join strategy.
4) Target tables with before and after per-row triggers.
5) Target tables with before and after per-statement triggers.
The basic operation of the MERGE statement is described by the header comment
in MergeNode. The following additional execution behavior is worth mentioning:
The MergeResultSet loops through the rows coming from the driving left-join,
assigning each row to a WHEN [ NOT ] MATCHED action. After all of the rows have
been assigned, the MergeResultSet then calls logic in each matching clause
action to process the buffered rows assigned to that action. The matching
clause action does the following:
i) Pushes the corresponding INSERT/UPDATE/DELETE ConstantAction onto a stack of
constant actions, introduced by this patch. This makes that
INSERT/UPDATE/DELETE ConstantAction the current ConstantAction for the
Activation. Introducing a stack of ConstantActions caused fewer code changes
than wrenching around the constructors and code generators for the
Insert/Update/DeleteResultSets so that they could handle fetching a
ConstantAction out of a variable in the Activation. The
Insert/Update/DeleteResultSets continue to take whatever ConstantAction is at
the top of the stack.
ii) Pokes the temporary ResultSet of buffered rows into a variable which is
pushed as an argument to the Insert/Update/DeleteResultSet.
iii) Invokes a method to instantiate the Insert/Update/DeleteResultSet.
iv) Opens the Insert/Update/DeleteResultSet in order to cause it to execute.
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
M java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
M java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java
M java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java
M java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
M java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java
M java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
M java/engine/org/apache/derby/impl/sql/compile/CurrentOfNode.java
M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
Changes for binding and code-generating MERGE statements with DELETE actions.
See the header comment in MergeNode for more details.
---------------
M java/engine/org/apache/derby/iapi/sql/Activation.java
M java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java
M java/engine/org/apache/derby/impl/sql/GenericActivationHolder.java
Introduces a stack of ConstantActions.
---------------
M java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
M
java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
M java/engine/org/apache/derby/impl/sql/execute/DMLWriteResultSet.java
A java/engine/org/apache/derby/impl/sql/execute/MergeResultSet.java
Execution machinery to process the left-join, assign qualifying rows to the
appropriate MatchingClauseConstantAction, and then invoke those ConstantActions
in order after the left-join is drained.
---------------
M java/engine/org/apache/derby/iapi/sql/execute/ConstantAction.java
M java/engine/org/apache/derby/iapi/services/io/RegisteredFormatIds.java
M java/engine/org/apache/derby/iapi/services/io/StoredFormatIds.java
A java/engine/org/apache/derby/impl/sql/execute/MergeConstantAction.java
A
java/engine/org/apache/derby/impl/sql/execute/MatchingClauseConstantAction.java
M
java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java
New Formatable ConstantActions: one for the overall MERGE statement and another
for its WHEN [ NOT ] MATCHED clauses.
---------------
M java/engine/org/apache/derby/impl/sql/execute/ScanResultSet.java
M java/engine/org/apache/derby/impl/sql/execute/HashScanResultSet.java
M
java/engine/org/apache/derby/impl/sql/execute/BulkTableScanResultSet.java
Changes to retrieve the target table's RowLocation during the processing of the
driving left-join.
---------------
M java/engine/org/apache/derby/impl/sql/execute/DeleteResultSet.java
M java/engine/org/apache/derby/impl/store/access/heap/HeapRowLocation.java
Change HeapRowLocation.getObject() to return itself rather than null. This is
the economical way to retrieve a RowLocation from a bulk scan.
---------------
M java/engine/org/apache/derby/loc/messages.xml
Re-worded an error message so it could be re-used for a MERGE-related error
condition.
---------------
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java
Tests for the new functionality.
> 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: 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,
> MergeStatement.html, MergeStatement.html, MergeStatement.html
>
>
> 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.1#6144)
