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, 20 Aug 2013 19:57:52 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-01-ac-grammar.diff

Attaching derby-3155-01-ac-grammar.diff. This is the first increment of work for implementing
the MERGE statement. This patch covers the grammar and bind() logic. I will run tests.

Although permissions descriptors are attached at bind() time, I did not tackle any of the
permissions logic. The GRANT/REVOKE behavior of the MERGE statement will have to be verified
and debugged when the execution logic is written. This may cascade changes back into the bind()

It is also possible that I have modelled the bind() structures in a way which will be frustrating
for code-generation or execution. When I tackle code-generation and execution, I may need
to adjust the bind() logic further.

However, at this point I think that this increment is big enough for submission. This is a
convenient checkpoint of work on the MERGE statement.

Here is the basic model I have adopted:

1) The WHEN MATCHED ... THEN UPDATE clause is modelled by an internally generated UpdateNode.

2) The WHEN MATCHED ... THEN DELETE clause is modelled by an internally generated DeleteNode.

3) The WHEN NOT MATCHED ... THEN INSERT clause is modelled by an internally generated InsertNode.

4) The WHEN [ NOT ] MATCHED clauses are driven by an internally generated HalfOuterJoinNode
which represents the following query:

    sourceTable LEFT OUTER JOIN targetTable ON searchCondition

Along the way, I had to tweak other compile-time classes. However, I believe that these tweaks
are minor. So far, the MERGE statement has not caused a lot of disruption.

A valid MERGE statement currently raises the following error at the end of the bind phase:

ERROR 0A000: Feature not implemented: MERGE.

Touches the following files:


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

Grammar changes, including the introduction of two new non-reserved keywords: MERGE and MATCHED.


A       java/engine/org/apache/derby/impl/sql/compile/MergeNode.java
A       java/engine/org/apache/derby/impl/sql/compile/MatchingClauseNode.java

Adds two new query nodes. MergeNode is a new DMLModStatementNode parallel to InsertNode, UpdateNode,
and DeleteNode. MatchingClauseNode is a single class which can represent all three variants
of the WHEN [ NOT ] MATCHED clause.


M       java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java
M       java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java

When we internally generate an UpdateNode for a WHEN MATCHED ... THEN UPDATE clause, we have
to allow references to the source table on the right side of the SET operator. For example,
in the following statement...

merge into t1
using t2
on t1.c1 = t2.c1
when matched and t1.c2 != t2.c2 then update set c2 = t2.c2;

...we allow "set c2 = t2.c2" even though the table being updated is t1, not t2. This means
that for this case we must disable a little piece of logic which was deliberately nulling
out the tablename in expressions on the right side of the SET operator.

In addition, I disabled a Sanity check which prevented us from having more than one table
in the query which drives an UPDATE statement. This was necessary in order to use a left join
to drive the WHEN MATCHED ... THEN UPDATE clause.


M       java/engine/org/apache/derby/impl/sql/compile/UnionNode.java
M       java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
M       java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java

Some extra book-keeping was added in order to support DEFAULT values for generated columns
in WHEN NOT MATCHED ... THEN INSERT clauses. The handling of generated columns (including
identity columns) is already a set of brittle special cases. This makes the handling more
brittle and flags an area which may need extra testing.


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

Two new methods were created in order to abstract out some bind() logic. That logic is now
used both by ordinary left joins and by the internally generated left join which drives the
MERGE statement.


M       java/engine/org/apache/derby/loc/messages.xml
M       java/shared/org/apache/derby/shared/common/reference/SQLState.java

Adds 3 new messages for errors discovered at bind() time.


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

Adds a battery of tests for the MERGE statement's bind() logic. Mostly these are negative
tests, stressing error conditions.

> 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
>              Labels: derby_triage10_10
>         Attachments: derby-3155-01-ac-grammar.diff, MergeStatement.html
> 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 is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

View raw message