[
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() logic.
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
