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 15:46: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-26-aa-copyRowLocationForIndexScans.diff

Attaching derby-3155-26-aa-copyRowLocationForIndexScans.diff. This patch fixes a compilation
problem with a MERGE statement involving a view. Regression tests ran cleanly for me on this
patch.

The problem query, found by Knut, was this:

{noformat}
create view sr_039( i ) as values 1;
create table t1_039( x int, y int, z int );
create unique index idx on t1_039( x, y );
insert into t1_039 values ( 1, 100, 1000 ), ( 2, 200, 2000 );

merge into t1_039
using sr_039 on ( x = 1 )
when matched then delete;
{noformat}

The fix involves two changes:

1) There was a place in FromBaseTable where a ResultColumnList was being cloned but conglomerate
information wasn't being propagated from the original list. That just looks like an existing
bug to me. However, I don't now why we haven't tripped over it before.

2) Once I cleaned that up, I stumbled on another bug: Index probes into base tables weren't
equipped to hand back the RowLocations needed by the MERGE statement's driving left join.

After fixing these bugs, Knut's original test case works. But I'm not declaring victory yet.
I have tried a variation on that original test case and tripped across yet another bug. I
think that the fix for that bug deserves its own patch so that we can clearly reconstruct
which changes correspond to which problems.

Here is the new test case which I'm going to tackle next. It fails during compilation because
a result set number hasn't been assigned to the column reference in the matching refinement
of the WHEN NOT MATCHED clause:

{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, 0, 0 );
{noformat}

Touches the following files:

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

M       java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java

The fix for 1).

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

M       java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
M       java/engine/org/apache/derby/impl/sql/compile/IndexToBaseRowNode.java
M       java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
M       java/engine/org/apache/derby/impl/sql/execute/IndexRowToBaseRowResultSet.java

The fix for 2).

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

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

A new test case to track this improvement.


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