MERGE INTO for table containing AUTO_INCREMENT columns?

[cowwoc]

Hi,

When I execute:

MERGE INTO audit_transaction_ids (uuid, time) KEY(id) VALUES
(TRANSACTION_ID(), NOW())

on the following schema:

CREATE TABLE audit_transaction_ids (id IDENTITY PRIMARY KEY, uuid
VARCHAR UNIQUE NOT NULL, `time` TIMESTAMP NOT NULL);

I get this error:

org.h2.jdbc.JdbcSQLException: Column "ID" contains null values; SQL
statement:
MERGE INTO audit_transaction_ids (uuid, time) KEY(id) VALUES
(TRANSACTION_ID(), NOW()) [90081-155]

In the above case the MERGE operation should always insert. Granted I
can (and will) replace it with an INSERT statement but I'm curious:
why is this failing? What is the correct way of invoking MERGE INTO
for a table containing an IDENTITY or AUTO_INCREMENT column?

Thanks,
Gili

[cowwoc]

Actually, I really need to use MERGE INTO in this case.

1. This table is used by an audit trigger. As such, it will get
invoked multiple times in the same transaction.

2. The first time the trigger is invoked, I want it to insert a new
row with the current TRANSACTION_ID() and time.

3. The subsequent times the trigger is invoked, I expect it to return
the existing "id" (I invoke Statement.getGeneratedKeys() to that end)
without altering "uuid" or "time".

I'm fairly sure my existing design alters "time" the subsequent times
the trigger is invoked. How do I fix both these problems?

Thanks,
Gili

[Thomas Mueller]

Hi,

This problem was later discussed at
http://stackoverflow.com/questions/6306592/merge-into-table-containing-auto-increment-columns

Regards,
Thomas

[Evan]

I really don't see the problem here..

Been using MERGE with auto_increment columns,
never a problem (except for constraint errors, mine of course).

instead of this:

MERGE INTO audit_transaction_ids (uuid, time) KEY(id) VALUES
(TRANSACTION_ID(), NOW())

do this:
MERGE INTO audit_transaction_ids (id, uuid, time) KEY(id)
SELECT null, TRANSACTION_ID(), NOW()

there goes your problem.

your problem is, u set the key to be [id]
but don't provide [id] itself in columns to be merged
then how would you expect the merge to be done?

it supposed to check [id] column from the values you provided,
and when null, insert null or if exists, default(which is [id]'s
NEXT_VAL)

Cheers,
Evan

[cowwoc]

Evan,

I'm shocked that this works (I just confirmed it on my end). Why
does this work?

MERGE INTO AUDIT_TRANSACTION_IDS (id, uuid, time) KEY (id) SELECT null,
TRANSACTION_ID(), NOW();

but this fails?

MERGE INTO AUDIT_TRANSACTION_IDS (id, uuid, time) KEY (id)
VALUES(DEFAULT,TRANSACTION_ID(), NOW());

1. According to http://www.h2database.com/html/grammar.html I should be
able to use DEFAULT with a MERGE command. Is this a bug?
2. Why should use statement work while the other fails?
3. According to "Default expressions are used if no explicit value was
used when adding a row." It later on goes on to say "Identity and
auto-increment columns are columns with a sequence as the default."
which seems to contradict the behavior I'm seeing (omitting "id" gives
it a default value of null, not the default).
4. I don't fully understand which part of your MERGE command converts
null into a default value. Is the act of inserting null into an
AUTO_INCREMENT column converting it to DEFAULT? Or is it the act of
SELECTing null?

Thanks,
Gili

[Evan]

First things first, MERGE isn't like your ordinary SQL,
it is basically a UPDATE which INSERTs when no record found to be
updated.

wikipedia gives a more concise, standardized syntax of MERGE
http://en.wikipedia.org/wiki/Merge_(SQL)
but you have to supply your own update and insert.
(whether this will be supported in H2 or not is not mine to answer)

So how do you update a record using MERGE in H2?
You define a key to be looked up for,
if it is found you update the row (with column names you supply,
and you can define DEFAULT here, to reset your columns to its
defaults),
otherwise you insert the row.

Now what is Null? Null means unknown, not found, undefined,
anything which is not what you're looking for.

That is why Null works as key to be looked up for. Because it means
the record is not found.
MERGE INTO table1 (id, col1, col2) KEY(id) VALUES (Null, 1, 2)
Null has a value. it IS a value.

Now let's see your second SQL.
MERGE INTO table1 (id, col1, col2) KEY(id) VALUES (DEFAULT, 1, 2)

What is that implying? To me, it says
I have this [default, 1, 2], ___find me a default in column id___,
then update
col1 to 1, col2 to 2, if found.
otherwise, insert default to id, 1 to col1, 2 to col2.

See what I underlined there? what does that even mean? what is
default?
how do you compare _default_ to id?
Null is a SQL-standard value, DEFAULT is just a keyword.

You can do stuff like,
MERGE INTO table1 (id, col1, timeStampCol) KEY(id) VALUES (Null, 1,
DEFAULT)
but don't put DEFAULT in the key column....

It's just.. I don't know.. doesn't make any sense, at least to me.

Hope this clears things up a bit.

One final note, IMO H2's merge statement is simple and to the point,
but as more RDBMS supports merge, I believe a more concise, less
ambigue syntax
is preferable to minimize vendor's implementation differences.

Cheers,
Evan

On Jun 14, 10:28 pm, cowwoc <cow...@bbs.darktech.org> wrote:
> Evan,
>
>      I'm shocked that this works (I just confirmed it on my end). Why
> does this work?
>
> MERGE INTO AUDIT_TRANSACTION_IDS (id, uuid, time) KEY (id) SELECT null,
> TRANSACTION_ID(), NOW();
>
>      but this fails?
>
> MERGE INTO AUDIT_TRANSACTION_IDS (id, uuid, time) KEY (id)
> VALUES(DEFAULT,TRANSACTION_ID(), NOW());
>

> 1. According tohttp://www.h2database.com/html/grammar.htmlI should be

[cowwoc]

Hi Evan,

That's an excellent answer. Can you post it here?
http://stackoverflow.com/questions/6306592/merge-into-table-containing-auto-increment-columns

More replies below.

On 14/06/2011 10:20 AM, Evan wrote:
> First things first, MERGE isn't like your ordinary SQL,
> it is basically a UPDATE which INSERTs when no record found to be
> updated.
>
> wikipedia gives a more concise, standardized syntax of MERGE
> http://en.wikipedia.org/wiki/Merge_(SQL)
> but you have to supply your own update and insert.
> (whether this will be supported in H2 or not is not mine to answer)
>
> So how do you update a record using MERGE in H2?
> You define a key to be looked up for,
> if it is found you update the row (with column names you supply,
> and you can define DEFAULT here, to reset your columns to its
> defaults),
> otherwise you insert the row.
>
> Now what is Null? Null means unknown, not found, undefined,
> anything which is not what you're looking for.
>
> That is why Null works as key to be looked up for. Because it means
> the record is not found.
> MERGE INTO table1 (id, col1, col2) KEY(id) VALUES (Null, 1, 2)
> Null has a value. it IS a value.

1. It's not obvious to me why H2 understands that when you search
for a null key you mean to insert the default value, but when you
specify omit "id" from VALUES it doesn't know to do the same.
2. On second glance, your solution doesn't actually work properly.
It always inserts a new row even if there is already a matching row with
the same [uuid, time] values.

> Now let's see your second SQL.
> MERGE INTO table1 (id, col1, col2) KEY(id) VALUES (DEFAULT, 1, 2)
>
> What is that implying? To me, it says
> I have this [default, 1, 2], ___find me a default in column id___,
> then update
> col1 to 1, col2 to 2, if found.
> otherwise, insert default to id, 1 to col1, 2 to col2.
>
> See what I underlined there? what does that even mean? what is
> default?
> how do you compare _default_ to id?
> Null is a SQL-standard value, DEFAULT is just a keyword.
>
> You can do stuff like,
> MERGE INTO table1 (id, col1, timeStampCol) KEY(id) VALUES (Null, 1,
> DEFAULT)
> but don't put DEFAULT in the key column....
>
> It's just.. I don't know.. doesn't make any sense, at least to me.
>
> Hope this clears things up a bit.
>
> One final note, IMO H2's merge statement is simple and to the point,
> but as more RDBMS supports merge, I believe a more concise, less
> ambigue syntax
> is preferable to minimize vendor's implementation differences.

The MERGE syntax is formally specified by the SQL:2008 standard
(which is freely available online). I find it to be far less ambiguous
than H2's. If I understand them correctly, I should be able to:

MERGE INTO AUDIT_TRANSACTION_IDS
USING audit_transaction_ids ON audit_transaction_ids.uuid = TRANSACTION_ID()
WHEN NOT MATCHED THEN INSERT (uuid, time) VALUES (TRANSACTION_ID(), NOW())

That being said, I don't think I can use MERGE INTO for my purposes. It seems that MERGE INTO does not return any values, so if there *is* a match there is no way for me to get back the matching id. I'll need to use separate SELECT/UPDATE statements to implement what I want.

PS: In case you are curious about the official MERGE INTO syntax, here
is a sniplet from the 2008 specification:

Function
Conditionally update rows of a table, or insert new rows into a table,
or both.

Format
<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 THEN <merge update specification>

<merge when not matched clause> ::=
WHEN NOT MATCHED THEN <merge insert specification>

<merge update specification> ::=
UPDATE SET <set clause list>

<merge insert specification> ::=
INSERT [ <left paren> <insert column list> <right paren> ]
[ <override clause> ]
VALUES <merge insert value list>

<merge insert value list> ::=
<left paren>
<merge insert value element> [ { <comma> <merge insert value element> }... ]
<right paren>

<merge insert value element> ::=
<value expression>
| <contextually typed value specification>

Gili

[Evan]

Oh and one more thing.
You mentioned that you would like to return the associated id
if the [uuid, time] is found.

well MERGE don't do that.
as I quoted above, it will return 0 if it updates anything.

guess you have to revert on SELECT then INSERT or UPDATE.

[Evan]

Damn my post gets eaten..

Good day,

On Jun 15, 2:22 am, cowwoc <cow...@bbs.darktech.org> wrote:
> Hi Evan,
>

>      That's an excellent answer. Can you post it here?http://stackoverflow.com/questions/6306592/merge-into-table-containin...

>
>      More replies below.
>
> On 14/06/2011 10:20 AM, Evan wrote:

Sorry haven't registered there. That's why I replied here.

>
>      1. It's not obvious to me why H2 understands that when you search
> for a null key you mean to insert the default value, but when you
> specify omit "id" from VALUES it doesn't know to do the same.
>      2. On second glance, your solution doesn't actually work properly.
> It always inserts a new row even if there is already a matching row with
> the same [uuid, time] values.

What? Didn't you said my answer was excellent?

I think I get what you're trying to do here.
You want to update if it founds the same [uuid, time], insert if
otherwise.

Then try this.
MERGE INTO AUDIT_TRANSACTION_IDS (uuid, time) KEY (uuid, time)
VALUES (TRANSACTION_ID(), NOW());

*little performance tip: make sure uuid is indexed*

as the docs said,
If the table contains an auto-incremented key or identity column, and
the row was updated, the generated key is set to 0; otherwise it is
set to the new key.

So observe the jdbc driver's Statement.getGeneratedKeys ResultSet
and you should be fine.

Whatever floats your boat, man.

I've stated before that I liked H2's syntax, but
I wouldn't mind a more standardized, concise syntax, especially if
it means better(and more) vendor-compatibility.

Cheers,
evan

[Evan]

Good day,

On Jun 15, 2:22 am, cowwoc <cow...@bbs.darktech.org> wrote:

> Hi Evan,
>
>      That's an excellent answer. Can you post it here?http://stackoverflow.com/questions/6306592/merge-into-table-containin...

>
>      More replies below.
>
> On 14/06/2011 10:20 AM, Evan wrote:
>

I'm sorry, I did look into when Thomas pointed it out,
but since I haven't registered yet, I replied here.

What? I thought you said my answer was excellent. ?

I think I get what you're trying to do here.

if it has the same [uuid, time] then you want to update the record.
otherwise, insert.
Am I right?

Then you miss the MERGE point altogether.

try this.
MERGE INTO audit_transaction_ids (uuid, time) KEY (uuid, time)
VALUES (TRANSACTION_ID(), NOW())

*little performance tip: make sure you have uuid indexed.*

Now, according to the docs (Haven't confirm yet)

If the table contains an auto-incremented key or identity column, and
the row was updated, the generated key is set to 0; otherwise it is
set to the new key.

So observe the jdbc driver's Statement.getGeneratedKeys ResultSet and
you should be fine.



>
>

Well anything that floats your boat.

I stated too, that I liked H2's implementation,
but it wouldn't hurt too much having a less ambiguous syntax,
especially cross-vendor one.

Cheers,
evan