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 Tue, 18 Feb 2014 20:32:20 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-27-aa-adjustMatchingRefinements.diff

Attaching derby-3155-27-aa-adjustMatchingRefinements.diff. This patch fixes the compilation
problem with a matching refinement clause that referenced a column in a source table which
was a view wrapping a values clause.

I think that the view was flattened but this information was not communicated to the matching
refinement clause. The solution was to point the column references in the matching refinement
clauses at the row coming out of the driving left join.

This fixes the following problem case:

{noformat}
create view sr_040( i ) as values ( 1 );
create table t1_040( x int, y int, z int );
create unique index idx on t1_040( x, y );

insert into t1_040 values
( 1, 100, 1000 ), ( 1, 101, 1000 ), ( 1, 102, 1000 ), ( 1, 103, 1000 ), ( 2, 200, 2000 );

merge into t1_040
using sr_040 on ( x = 1 )
when matched and y = 101 then delete
when matched and y = 102 then update set z = -1000
when not matched and i > 1 then insert values ( -1, i, 0 );

select * from t1_040 order by x, y, z;

merge into t1_040
using sr_040 on ( x = 3 )
when matched and y = 103 then delete
when matched and y = 102 then update set z = -10000
when not matched and i = 1 then insert values ( -1, i, 0 );

select * from t1_040 order by x, y, z;
{noformat}

But we're still not out of the woods with the original problem case which Knut found. Adding
another row to the values view causes us to fail on a missing row:

{noformat}
create view sr_040( i ) as values ( 1 ), ( 3 );
create table t1_040( x int, y int, z int );
create unique index idx on t1_040( x, y );

insert into t1_040 values
( 1, 100, 1000 ), ( 1, 101, 1000 ), ( 1, 102, 1000 ), ( 1, 103, 1000 ), ( 2, 200, 2000 );

merge into t1_040
using sr_040 on ( x = 1 )
when matched and y = 101 then delete
when matched and y = 102 then update set z = -1000
when not matched and i > 1 then insert values ( -1, i, 0 );
{noformat}

That, again, looks like a different bug which deserves its own separate patch.

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

The fix.

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

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

The test case.


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

Mime
View raw message