[
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-16-aa-treatCurrentRowLocationNodeLikeBaseColumnNode.diff
This patch addresses the following problem case:
{noformat}
ij> create table t1(x int primary key);
0 rows inserted/updated/deleted
ij> create table t2(x int);
0 rows inserted/updated/deleted
ij> merge into t1 using t2 on t1.x = 42 when not matched then insert (x) values
(42);
ERROR XSAI2: The conglomerate (0) requested does not exist.
{noformat}
This was Knut's simplification of a problem case which Dyre identified. Knut's
query is fixed and Dyre's original query is converted into a later problem
which Knut recorded:
ERROR XJ001: Java exception: 'ASSERT FAILED currentOfNode is null:
org.apache.derby.shared.common.sanity.AssertFailure'.
At a high level, this is what's going on with the corrected failure:
1) The MERGE statement cooks up a LEFT JOIN to drive its execution phase.
2) This is a LEFT JOIN which ordinary users can't create. That's because the
row location from the target table is added to the SELECT list of the LEFT JOIN.
3) The added row location column causes the LEFT JOIN to violate some
assumptions made by the compiler.
Touches the following files:
--------------------
M java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
Adjusted a case where a FromBaseTable is cloning its result columns without
propagating its heap conglomerate id.
--------------------
M java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
Fixed the construction of the referenced bit map. The old logic did not account
for the fact that a row location column can be one of the returned columns.
--------------------
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java
Added a test case for this problem query.
> 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
>
>
> 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)
