Issue Details (XML | Word | Printable)

Key: CORE-2005
Type: Improvement Improvement
Status: Resolved Resolved
Resolution: Fixed
Priority: Minor Minor
Assignee: Adriano dos Santos Fernandes
Reporter: Nickolay Samofatov
Votes: 2
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Support SQL 2008 syntax for MERGE statement with DELETE extension

Created: 18/Jul/08 04:17 PM   Updated: 28/Sep/15 01:11 PM
Component/s: Engine
Affects Version/s: 2.1.0
Fix Version/s: 3.0 Alpha 1

Issue Links:
Duplicate
 
Relate
 

QA Status: Done successfully


 Description  « Hide
Merge statement is designed to simplify/optimize final step of ETL processing for data warehousing, but when I tried to use it, I found that existing Firebird implementation is nearly impossible to use for this purpose.
In the situation when source data is mostly unchanged Firebird's MERGE logic triggers expensive row updates and recalculation of aggregates for unchanged rows.

The request is to implement SQL2008 MERGE syntax with DELETE extension:

<merge statement> ::=
MERGE INTO <target table> [ [ AS ] <merge correlation name> ]
USING <table reference>
ON <search condition> <merge operation specification>

<merge correlation name> ::=
<correlation name>

<merge operation specification> ::=
<merge when clause>...

<merge when clause> ::=
<merge when matched clause>
| <merge when not matched clause>

<merge when matched clause> ::=
WHEN MATCHED [ AND <search condition> ]
THEN <merge update specification>

<merge when not matched clause> ::=
WHEN NOT MATCHED [ AND <search condition> ]
THEN <merge insert specification>

<merge update specification> ::=
UPDATE ...
| DELETE

DELETE is the useful extension to standard MERGE syntax supported by DB2.


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 23/Mar/10 04:06 PM
Nickolay, please test it.

Karol Bieniaszewski added a comment - 13/Oct/11 09:53 AM
Is possible to see this Fix also in 2.5 series not only in FB3 (long long time to wait for stable version of FB3)
Or fix is too complicated to backport?

Adriano dos Santos Fernandes added a comment - 13/Oct/11 10:42 AM
I think the current DELETE extension is not very useful the way it was done.

I really think we should still revise it, so I would not even try to make it in 2.5 now.

Oracle MERGE satisfies my needs, although it's very strange in some aspects (with UPDATE + DELETE).

Karol Bieniaszewski added a comment - 13/Oct/11 12:56 PM
Delete statement is not priority
only <search condition> to remove unnecessary updates and record versions

Adriano dos Santos Fernandes added a comment - 13/Oct/11 01:14 PM
And why you can't put your <search condition> in the ON clause?

Karol Bieniaszewski added a comment - 14/Oct/11 05:20 AM - edited
Because i do not test only Target_field=Source_field but also Target_field<>Source_field
when i put this into on then i got unnecessary inserts


#######################################################
and i have also proposition to relax merge clause to this format

MERGE

..
ON ..

WHEN MATCHED [ AND <search condition> ] THEN
..
WHEN NOT MATCHED [ AND <search condition> ] THEN
..
WHEN MATCHED [ AND <search condition> ] THEN
..
WHEN NOT MATCHED [ AND <search condition> ] THEN

######################################################################################
above is equivalent to case scenario and i suppose that this is still conform sql standard but do extension for it

MERGE

..
ON ..
CASE
WHEN MATCHED [ AND <search condition> ] THEN
BEGIN
..
END
WHEN NOT MATCHED [ AND <search condition> ] THEN
BEGIN

END
WHEN MATCHED [ AND <search condition> ] THEN
BEGIN

END
WHEN NOT MATCHED [ AND <search condition> ] THEN
BEGIN

END

###################################################################

and inside "block"
you can do any script operation like
MERGE
...
ON (...)
WHEN MATCHED [ AND <search condition> ] THEN
INSERT (...) values(...);
UPDATE SET TARGET_FIELD=xxx;
/* but also */
UPDATE SOME_TABLE ST SET ST.X=.. WHERE ST.Y=...;

WHEN NOT MATCHED [ AND <search condition> ] THEN
and the same here

.....

###################################################################

above work like execute block to do any more tasks then only update/insert/delete on target table
but still do optimisations to updates target table in one loop cursor

is this good concept or you see some problems here?
If yes - should i post this as new ticket to tracker?


Adriano dos Santos Fernandes added a comment - 14/Oct/11 11:09 AM
No, this new syntax is not good. For that there is explicit cursors and PSQL.

Karol Bieniaszewski added a comment - 16/Oct/11 10:28 AM
Yes you have right for executing any script - better is explicit cursor - and then we can use also variables.

But what about this extending many WHEN MATCHED statement - this is very useful and follow by merge specification - and i suppose is simple to implement.

MERGE

..
ON ..

WHEN MATCHED [ AND <search condition> ] THEN
UPDATE SET TARGET.FIELD = TARGET.FIELD1*0.5, TARGET.FIELD2 = 4;
WHEN MATCHED [ AND <search condition> ] THEN
UPDATE SET TARGET.FIELD = TARGET.FIELD1*0.7, TARGET.FIELD2 = 8;
WHEN MATCHED [ AND <search condition> ] THEN
UPDATE SET TARGET.FIELD = TARGET.FIELD1*1.2, TARGET.FIELD2 = 7;
WHEN NOT MATCHED [ AND <search condition> ] THEN
INSERT ...;

this is better and faster then doing this in this way
if we are limited to only one chck WHEN MATCHED

MERGE

..
ON ..

WHEN MATCHED [ AND <search condition> ] THEN
UPDATE SET TARGET.FIELD = CASE WHEN ... THEN TARGET.FIELD1*0.5 WHEN ... THEN TARGET.FIELD1*0.7 WHEN .. THEN TARGET.FIELD1*1.2 END, CASE WHEN .. THEN TARGET.FIELD2 = 4 WHEN .. THEN TARGET.FIELD2 = 8; WHEN .. THEN TARGET.FIELD2 = 7 END
WHEN NOT MATCHED [ AND <search condition> ] THEN
INSERT ...;

###########
this is simple situation but in complicated situation your code is not readable and complicated if e.g you must in some case update Field1, but in other only field 2 , Field3, ...


Adriano dos Santos Fernandes added a comment - 17/Oct/11 02:24 PM
> But what about this extending many WHEN MATCHED statement - this is very useful and follow by merge specification - and i suppose is simple to implement.

Seems you're right about this. And this should be very useful, specially related to my saying about current unusefulness of WHEN MATCHED ... DELETE.