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, 25 Feb 2014 18:54:19 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-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)

Mime
View raw message