SEQUENCES retrieved incorrectly?

[Evan Nelson]

I've been banging my head trying to figure out why my new code for
SEQUENCEs isn't passing the tests stored in the testing script. I
think it's because the tests are all based on an incorrect
implementation of SEQUENCE!

In the current version of the code, try the following:

CREATE SEQUENCE testSeq START WITH 5;
SELECT testSeq.currval;

Because this sequence is starting with 5 and we haven't modified it,
it stands to reason that currval would return 5, doesn't it? It
doesn't do that, though. Instead, it returns 4. Similarly, the
following SQL code...

CREATE SEQUENCE testSeq START WITH 10 INCREMENT BY 3;
SELECT testSeq.currval;

...will return 7. In Sequence.java, the value is stored correctly, but
the function "getCurrentValue()" returns the current value minus the
increment. However, all the script tests seem to think this is normal.
Am I missing something, or isn't this incorrect behavior?

-Evan

[Johann Schleier-Smith]

The START WITH value for a sequence is returned after the first call to NEXTVAL, so the H2 implementation and tests are sensible.

For comparison I ran this code in Oracle.  It throws an error (ORA-08002) if call TESTSEQ.CURRVAL before TESTSEQ.NEXTVAL.  Returning start with minus increment rather than an error for an initial call to CURRVAL is a reasonable implementation.

[Evan Nelson]

Interesting. That just seems like a blatantly stupid way to implement
a sequence... but, if that's the way Oracle does it, then I guess
that's the way we need to support it.

Thanks for the info, I appreciate it.

-Evan

On Mar 2, 1:28 am, Johann Schleier-Smith <jssm...@gmail.com> wrote:
> The START WITH value for a sequence is returned after the first call to
> NEXTVAL, so the H2 implementation and tests are sensible.
> For comparison I ran this code in Oracle.  It throws an error (ORA-08002) if
> call TESTSEQ.CURRVAL before TESTSEQ.NEXTVAL.  Returning start with minus
> increment rather than an error for an initial call to CURRVAL is a
> reasonable implementation.
>

[Johann Schleier-Smith]

Yeah, I hear you.  It's basically the iterator pattern though, e.g., just as in a jdbc result set - you call next() before you can read any data.  Also, in a transactional MVCC context, a call to CURRVAL that is not preceded by a call NEXTVAL would be be ill-defined.  When you call NEXTVAL you get a unique value from the sequence that is defined for your transaction.  Calling CURRVAL afterwards will give you the value of NEXTVAL last returned for your transaction.  However, if your transaction/session calls CURRVAL before NEXTVAL the database would have to return the value from some other transaction, and if multiple transactions are in progress this would not be uniquely determined.  Thus the error.

[Maarten Bosteels]

IMHO h2 should also throw an error when you call CURRVAL without a preceded call to NEXTVAL on the same session, because that would most probably indicate a bug.

Maarten

[Thomas Mueller]

Hi,

PostgreSQL also throws an error if NEXTVAL was not yet called:

drop sequence abc;
create sequence abc;
select currval('ABC');
-- PostgreSQL: ERROR: currval of sequence "abc" is not yet defined in
this session 55000/0
-- H2: 0
select nextval('ABC'); -- 1
select currval('ABC'); -- 1

It looks like in PostgreSQL and Oracle, CURRVAL is session specific. I
didn't know that. In H2, CURRVAL is the last value (independent of the
session). This is an incompatibility, I guess I need to fix that.

The current value is also available in the system table
INFORMATION_SCHEMA.SEQUENCES, in that case it's probably OK to return
the last used value (as done now), or maybe NULL if NEXTVAL was never
called by any session.

Regards,
Thomas

[Thomas Mueller]

Hi,

It's quite complicated to change the behavior. I will not implement
this for the next release. Instead, I will better document the current
behavior: "CURRVAL: Returns the current (last) value of the sequence,
independent of the session. If the sequence was just created, the
method returns (start - interval)."

I'm sorry that this doesn't match how Oracle and PostgreSQL work. I
have also added a feature request, but currently with low priority. If
you need this feature, please tell me. If you want to implement it,
feel free to submit a patch.

Regards,
Thomas

[dzoettl]

+1

calling Currval before Nextval should give an error. If the last value
returned is needed it should be read from the INFORMATION_SCHEMA .