[
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-07-ad-insertAction.diff
Attaching derby-3155-07-ad-insertAction.diff. This patch adds support for the
INSERT action of MERGE statements. I am running tests now.
The patch includes tests for the following use-cases:
i) INSERT actions with DEFAULT expressions for identity, generated, and default
columns
ii) INSERT actions on tables with CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY
constraints.
iii) INSERT actions in MERGE statements fired by triggers.
iv) INSERT actions which fire triggers.
v) MERGE statements with INSERT and DELETE actions operating together.
The following behaviors have not been implemented/tested yet:
a) UPDATE actions
b) Permissions checking
c) Dependency checking and statement invalidation
So far the bulk of the complexity remains concentrated in compilation.
Touches the following files:
---------------
M java/engine/org/apache/derby/iapi/sql/execute/NoPutResultSet.java
M
java/engine/org/apache/derby/impl/sql/execute/TemporaryRowHolderResultSet.java
Replaced a magic number with a named constant. Temporary tables don't have
result set numbers assigned to them. But now they are being used to drive the
INSERT/UPDATE/DELETE actions of MERGE statements. In order to get CHECK
constraints to work properly, the row being checked is stuffed into slot 0 of
the result set row array. That is usually the result set number corresponding
to the driving result set of an INSERT/UPDATE/DELETE statement. And it was,
coincidentally, the magic result set number of temporary tables. This decision
may need to be re-visited if we invent other situations for which temporary
tables need result set numbers.
---------------
M java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java
Improved a method header comment.
---------------
M java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
Added a new way to determine the data type of a ResultColumn. There is now a
path through the pinball machine where the old technique didn't work.
---------------
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/InsertNode.java
Compilation support for the INSERT action of a MERGE statement. The bulk of the
new complexity has to do with generating the rows which are stuffed into a
temporary table at execution time. There is considerable complexity involved in
handling the DEFAULT keyword, which can appear as the value to be inserted into
an identity, generated, or default column, as for instance, in this situation:
{
create table t1_007
(
c1 int generated always as identity,
c2 int,
c3 int generated always as ( c1 + c2 ),
c1_4 int,
c5 int default 1000
);
...
merge into t1_007
using t2_007
on t1_007.c2 = t2_007.c2
when not matched and t2_007.c5 = 'three'
then insert ( c1, c2, c3, c1_4, c5 ) values ( default, 100 * t2_007.c2,
default, t2_007.c3, default );
}
For an ordinary INSERT statement, much of that complexity is handled by the
compilation of the driving SELECT. For a MERGE statement, that SELECT is a
dummy statement which is not optimized or generated. That, in turn,
short-circuits some of the complex handling of the DEFAULT keyword. So that
logic had to be reproduced in MatchingClauseNode.
There is already too much complexity in the handling of these special columns
and now I have made the situation even more complicated. In particular, I don't
understand why GENERATED BY DEFAULT AS IDENTITY and GENERATED ALWAYS AS
IDENTITY take such different trajectories through the pinball machine. And I
don't understand why the special columns are compiled with the driving SELECT
rather than with the INSERT/UPDATE action itself. This is a source of
brittleness which we should be aware of. However, I thought that cleaning this
up was outside the scope of this JIRA.
---------------
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/execute/WriteCursorConstantAction.java
M java/engine/org/apache/derby/impl/sql/execute/DMLVTIResultSet.java
M java/engine/org/apache/derby/impl/sql/execute/UpdateVTIResultSet.java
M
java/engine/org/apache/derby/impl/sql/execute/CallStatementResultSet.java
M
java/engine/org/apache/derby/impl/sql/execute/UpdatableVTIConstantAction.java
M java/engine/org/apache/derby/impl/sql/execute/InsertVTIResultSet.java
M java/engine/org/apache/derby/impl/sql/execute/DeleteResultSet.java
M java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java
M java/engine/org/apache/derby/impl/sql/execute/MiscResultSet.java
M java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java
M java/engine/org/apache/derby/impl/sql/execute/DeleteConstantAction.java
M java/engine/org/apache/derby/impl/sql/execute/DeleteVTIResultSet.java
M
java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java
M java/engine/org/apache/derby/impl/sql/execute/UpdateConstantAction.java
Added a small amount of logic so that INSERT/UPDATE/DELETE actions know at
execution time whether they are running under a MERGE statement. If so, then we
don't close the Activation at the end of the action. The Activation is closed
by the MergeResultSet at the very end after all of the actions have run. This,
in turn, makes it possible to run multiple INSERT/DELETE/UPDATE actions in a
single MERGE statement.
---------------
M java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java
Added some special processing for generation clauses invoked by actions of
MERGE statements. This is more of the fallout from the fact that the dummy
SELECTs are not optimized and generated.
---------------
M java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
Abstracted some logic into a separate method so that it could be called when
generating the temporary row for an INSERT action of a MERGE statement.
---------------
M java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java
M java/engine/org/apache/derby/impl/sql/execute/MergeResultSet.java
M java/engine/org/apache/derby/impl/sql/execute/InsertConstantAction.java
M
java/engine/org/apache/derby/impl/sql/execute/MatchingClauseConstantAction.java
Changes to support INSERT actions under MERGE statements.
---------------
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java
Tests for the new behavior.
> 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, 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
>
>
> 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)
