db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-3155) Support for SQL:2003 MERGE statement
Date Mon, 10 Mar 2014 16:12:47 GMT

     [ 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-38-aa-datatypes.diff

Attaching derby-3155-38-aa-datatypes.diff. This patch adds tests for using all datatypes in
MERGE statements. I am running tests now.

These tests uncovered one bug: We were not able to use XML datatypes in MERGE statements.
The driving left-join failed to compile because the compiler thought that the XML types were
going to be returned to the client. The solution was to make CursorNode not forbid XML types
in its select list when it has been cooked up to support a MERGE statement.


Touches the following files:

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

M       java/engine/org/apache/derby/impl/sql/compile/MergeNode.java
M       java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
M       java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj

Allow XML types in the select lists of the driving left-joins which support MERGE statements.

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

M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java
M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/IntArray.java

Tests for MERGE statements which exercise all Derby datatypes.


> 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
>
>
> 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)

Mime
View raw message