[
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-20-aa-reworkColumnMatching.diff
Attaching derby-3155-20-aa-reworkColumnMatching.diff. This patch substantially
reworks how column references in WHEN [ NOT ] MATCHED clauses are matched to
the correct values in the rows produced by the left join which drives the MERGE
statement's execution. Tests passed cleanly for me.
Consider the following MERGE statement:
{noformat}
merge into t1_027 x
using t2_027 y on x.a = y.c
when matched and x.b > 100 then update set x.b = y.d
when matched and x.b <= 100 then delete
when not matched and y.d > 3000 then insert values ( y.c, y.d );
{noformat}
At execution time, column references in all of the following expressions must
be mapped to the rows coming back from the driving left join:
{noformat}
x.b > 100
x.b <= 100
y.d > 3000
set x.b = y.d
values ( y.c, y.d )
{noformat}
Before this patch, that mapping was accomplished through
FromTable.getMatchingColumn(). In order to smooth over differences in the
column matching for SELECTs and UPDATEs, correlation names were replaced with
fully qualified table names in column references. But that was crude and caused
many simple use cases to fail.
The new approach is to do the following:
1) Before compiling the INSERT/UPDATE/DELETE actions, all of the column
references in the WHEN [ NOT ] MATCHED clauses are marked with whether they
come from the source or the target table.
2) The columns in the SELECT list of the driving left join are similarly marked.
3) At code generation time, these markers are used to match the WHEN [ NOT ]
MATCHED clauses to the SELECT list.
I think that the new approach will handle more use cases.
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/ColumnReference.java
New machinery for linking ColumnReferences to the source or target table of a
MERGE statement.
-----------------
M java/engine/org/apache/derby/impl/sql/compile/FromTable.java
M java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
Special processing for these linked ColumnReferences to that we don't blur the
distinction between correlation names and fully qualified names.
-----------------
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java
A new test which succeeds with the new scheme but failed with the old scheme.
> 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, 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
>
>
> 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.5#6160)
