[
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-30-ab-moreCorrelationNames.diff
Attaching derby-3155-30-ab-moreCorrelationNames.diff. This patch fixes more
column resolution problems. I am running tests now.
Working on this patch has helped me understand a defect (or at least a
brittleness) in my approach to implementing the UPDATE action of the MERGE
statement. The dummy UPDATE statement I create is one which can not arise via
the supported Derby grammar. That is because this UPDATE statement is driven by
a SELECT having more than one table. An ordinary Derby UPDATE can be driven by
a multi-table SELECT but only if the SELECT is hidden behind a WHERE CURRENT OF
clause.
The existing logic takes advantage of an assumption that the driving SELECT
only has columns from the table being UPDATEd. In particular, there is some
UpdateNode logic (around line 1530) which clears out table identifiers from the
UPDATE statement's column list. It appears that that logic was added as part of
the work on DERBY-1043. But without those table identifiers, I can't
distinguish between columns coming from the source vs. the target table of a
MERGE statement.
My original solution to this problem was to skip the logic which clears out
table identifiers when I am binding a MERGE statement. That worked for a long
time until I started trying to compile UPDATE actions which supplement their
column lists with extra columns needed to evaluate constraints, triggers, and
generation expressions. I re-instated id-clearing logic in order to make those
statements compile.
This revived the ambiguity when the source and target tables had columns with
the same name. My solution is to double-down on associating all columns with
the source vs. target tables before binding the INSERT/UPDATE/DELETE actions.
It's possible that I will continue to be buried under a pile of column
resolution problems. I may need to fall back and re-implement the UPDATE action
so that it uses some dummy column list which could be concocted via SQL and
then add some substitution logic to map between the dummy column list and the
expressions in the SET clauses.
However, for the moment I'm doubling down on the current implementation.
Touches the following files:
----------------
M java/engine/org/apache/derby/iapi/sql/compile/TagFilter.java
M java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java
Columns added to satisfy constraints, triggers, and generation clauses are
assocated with the TARGET table. The table-id-clearing logic is re-instated.
----------------
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/DMLModStatementNode.java
M java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
M java/engine/org/apache/derby/impl/sql/compile/FromTable.java
More logic to associate columns with the source or target table.
----------------
M java/engine/org/apache/derby/impl/sql/compile/FromList.java
Use the pre-computed association of columns with source/target tables to
resolve column references.
----------------
M java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java
Minor tweak to improve encapsulation and tracing of this class.
----------------
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java
Additional tests.
> 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, 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,
> derby-3155-27-aa-adjustMatchingRefinements.diff,
> derby-3155-28-aa-cardinalityViolations.diff,
> derby-3155-29-aa-missingSchema.diff,
> derby-3155-30-ab-moreCorrelationNames.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)
