Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support SQL 2008 syntax for MERGE statement with DELETE extension [CORE2005] #2442

Closed
firebird-automations opened this issue Jul 18, 2008 · 16 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @samofatov

Is duplicated by CORE3128
Is related to QA631

Votes: 2

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.

Commits: 9f16366 e0762f5 FirebirdSQL/fbt-repository@a1a4b7b

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Support SQL 2008 syntax for MERGE statment => Support SQL 2008 syntax for MERGE statement

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

summary: Support SQL 2008 syntax for MERGE statement => Support SQL 2008 syntax for MERGE statement with DELETE extension

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Nickolay, please test it.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue is duplicated by CORE3128 [ CORE3128 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

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?

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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).

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

Delete statement is not priority
only <search condition> to remove unnecessary updates and record versions

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

And why you can't put your <search condition> in the ON clause?

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

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?

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

No, this new syntax is not good. For that there is explicit cursors and PSQL.

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

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, ...

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

> 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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA631 [ QA631 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants