Identity () / scope_identity () does not work in H2 when assigned

Question

Using H2's built-in Java dabatase, I have a problem with identity () and scope_identity (). These functions do not work when assigning a variable:

Let there be a simple table:

create table test3 (x int IDENTITY); 

This works as expected, returning the last inserted value:

 insert into test3 values (default); select scope_identity() 

The following construct should return the same, but return null

 insert into test3 values (default); set @a=scope_identity(); select @a; 

Am I doing something wrong or is this an error in H2?

Answer 1

The problem is that set actually resets the region identifier (to zero) because it is not a request. Only queries returning a result, such as select and call , are queries. So what you can do is:

 select @a := scope_identity(); 

It works. I agree that in this case set does not work, and I will check if it can be supported.