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 On Tue, Mar 3, 2009 at 7:15 AM, Thomas Mueller <[email protected]> wrote: > 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 > > On Mon, Mar 2, 2009 at 5:06 PM, Evan Nelson <[email protected]> wrote: >> >> 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 <[email protected]> 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. >>> >>> On Sun, Mar 1, 2009 at 5:16 PM, Evan Nelson <[email protected]> wrote: >>> >>> > 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 >> >> >> > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
