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 Wed, 05 Feb 2014 17:04:09 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-20-aa-reworkColumnMatching.diff

Attaching derby-3155-20-aa-reworkColumnMatching.diff. This patch substantially reworks how
column references in WHEN [ NOT ] MATCHED clauses are matched to the correct values in the
rows produced by the left join which drives the MERGE statement's execution. Tests passed
cleanly for me.

Consider the following MERGE statement:

    merge into t1_027 x
    using t2_027 y on x.a = y.c
    when matched and x.b > 100 then update set x.b = y.d
    when matched and x.b <= 100 then delete
    when not matched and y.d > 3000 then insert values ( y.c, y.d );

At execution time, column references in all of the following expressions must be mapped to
the rows coming back from the driving left join:

    x.b > 100
    x.b <= 100
    y.d > 3000
    set x.b = y.d
    values ( y.c, y.d )

Before this patch, that mapping was accomplished through FromTable.getMatchingColumn(). In
order to smooth over differences in the column matching for SELECTs and UPDATEs, correlation
names were replaced with fully qualified table names in column references. But that was crude
and caused many simple use cases to fail.

The new approach is to do the following:

1) Before compiling the INSERT/UPDATE/DELETE actions, all of the column references in the
WHEN [ NOT ] MATCHED clauses are marked with whether they come from the source or the target

2) The columns in the SELECT list of the driving left join are similarly marked.

3) At code generation time, these markers are used to match the WHEN [ NOT ] MATCHED clauses
to the SELECT list.

I think that the new approach will handle more use cases.

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
M       java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java

New machinery for linking ColumnReferences to the source or target table of a MERGE statement.


M       java/engine/org/apache/derby/impl/sql/compile/FromTable.java
M       java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java

Special processing for these linked ColumnReferences to that we don't blur the distinction
between correlation names and fully qualified names.


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

A new test which succeeds with the new scheme but failed with the old scheme.

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

View raw message