[jira] [Updated] (DERBY-3155) Support for SQL:2003 MERGE statement

["Rick Hillegas (JIRA)" <ji...@apache.org>]

     [ 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-53-aa-transitionSimpleColumn.diff

Attaching derby-3155-53-aa-transitionSimpleColumn.diff. This patch addresses the problem Knut just found. I am running regression tests now.

The problem arises because of the special logic which was put into UpdateNode with revision 418933 as part of the work on DERBY-1043. That logic was put into UpdateNode to handle another issue with triggers. That logic is supposed to null out the table name in each column on the left side of a SET clause. It's a creepy thing to do, and that logic has caused a lot of grief for the UPDATE actions of MERGE statements. That logic breaks if what is on the right side of the SET clause is a column from another table. The logic does not break for the existing MergeStatementTest.test_018_updateFromTriggerTransitionTables()  test case. That is because, for that test case, what's on the right side of the SET clause isn't just a column, it's an expression. Without this current patch the following trigger definition works:

{noformat}
create trigger tr2 after insert on t1
referencing new table as new
merge into t2
using new on x = y
when matched then update set y = 2 * x;
{noformat}

Why does this not break outside a MERGE statement? Because MERGE gives rise to the only situation in which a plain column reference on the right side of a SET clause can be a column from a table other than the one being updated.

The fix is to ignore the nulling-out of table names if we are compiling an UPDATE action of a MERGE statement. I think this should be ok because the MERGE statement already has substantial logic to correct for the effects of DERBY-1043 and should work regardless of whether the table names are nulled out. But I'm not promising that there are no edge cases on this edge case.


Touches the following files:

------------------

M       java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java
M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.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, 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, derby-3155-52-aa-upgrade.diff, derby-3155-53-aa-transitionSimpleColumn.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)