Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 319136

Summary:	Incorrect START WITH value creating native sequence
Product:	z_Archived	Reporter:	Doug Clarke <douglas.clarke>
Component:	Eclipselink	Assignee:	Nobody - feel free to take it <nobody>
Status:	RESOLVED WORKSFORME	QA Contact:
Severity:	normal
Priority:	P4	CC:	joels, tom.ware
Version:	unspecified
Target Milestone:	---
Hardware:	All
OS:	All
Whiteboard:

Description Doug Clarke

2010-07-07 10:24:10 EDT

When I configure the employee example to use sequence generators defined as:

<sequence-generator name="EMP_SEQ" sequence-name="EMP_SEQ" allocation-size="25" />

I get the following correct SQL to create the sequence:

CREATE SEQUENCE EMP_SEQ INCREMENT BY 25 START WITH 25

When I change the definition to be:

<sequence-generator name="EMP_SEQ" sequence-name="EMP_SEQ" allocation-size="25" initial-value="25" />

The generate SQL to create the sequence is:

CREATE SEQUENCE EMP_SEQ INCREMENT BY 25 START WITH 49

If I change the initial-value to be 100 then the START WITH becomes 124.

The SQKL generated during the schema-gen is also a little odd to me:

[EL Fine]: DROP SEQUENCE EMP_SEQ
[EL Fine]: SELECT EMP_SEQ.NEXTVAL FROM DUAL
[EL Fine]: SELECT 1 FROM DUAL
[EL Warning]: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.0.v20100614-r7608): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-02289: sequence does not exist

Error Code: 2289
Call: SELECT EMP_SEQ.NEXTVAL FROM DUAL
Query: ValueReadQuery(sql="SELECT EMP_SEQ.NEXTVAL FROM DUAL")
[EL Fine]: CREATE SEQUENCE EMP_SEQ INCREMENT BY 25 START WITH 124

The commands are:
1. DROP SEQUENCE
2. SELECT dropped sequence
3. On failure do SELECT 1 from DUAL to verify we are connected
4. CREATE SEQUENCE

Comment 1 Doug Clarke

2010-07-07 10:27:09 EDT

OK. I see my issue (30 seconds later). 

We add the start-with value to the initial-value and subtract 1 so that the first time we ask for a value we can start issuing sequence numbers on the user provided initial-value.

The generated SQL is still confusing me though

Comment 2 Joel Schuster

2010-08-19 21:04:42 EDT

I'm running right up against this using EclipseLink.

Is there a work around? Some set of values for allocation-size and initial-value that works?

Comment 3 Doug Clarke

2010-08-23 17:13:59 EDT

I have found that the SQL that generates the sequences sets the initial value to be the starts-with + increment - 1. This is done so that the first time a sequence is retrieved it can start assigning values starting with the initial-value you have specified.

Although I found it confusing I believe it creates and uses the sequence so that your JPA configured values can be honoured.

Comment 4 Doug Clarke

2010-08-23 17:15:32 EDT

closing as RESOLVED - WORKSFORME

Comment 5 Eclipse Webmaster

2022-06-09 10:27:11 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink