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