Issue Details (XML | Word | Printable)

Key: CORE-6084
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Mark Rotteveel
Votes: 0
Watchers: 4
Operations

If you were logged in you would be able to see more operations.
Firebird Core

CREATE SEQUENCE START WITH has wrong initial value

Created: 18/Jun/19 11:41 AM   Updated: 19/Sep/20 03:23 PM
Component/s: Engine
Affects Version/s: 3.0.4, 4.0 Beta 1
Fix Version/s: 4.0 RC 1

Issue Links:
Relate
 
Replace
 

QA Status: Done successfully


 Description  « Hide
When using CREATE SEQUENCE seq_name START WITH n [INCREMENT BY x], then the first value generated by the sequence is n + x (where x = 1 when the INCREMENT BY clause is absent). This is wrong: the first value produced should be n. See also "9.29 Generation of the next value of a sequence generator" in SQL:2016.

Examples

CREATE SEQUENCE seq_name START WITH 1;
SELECT NEXT VALUE FOR seq_name FROM rdb$database;
-- Result = 2
-- Expected = 1
CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 2;
SELECT NEXT VALUE FOR seq_name FROM rdb$database;
-- Result = 3
-- Expected = 1
CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY -1;
SELECT NEXT VALUE FOR seq_name FROM rdb$database;
-- Result = 0
-- Expected = 1
CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY -2;
SELECT NEXT VALUE FOR seq_name FROM rdb$database;
-- Result = -1
-- Expected = 1
CREATE SEQUENCE seq_name INCREMENT BY 2;
SELECT NEXT VALUE FOR seq_name FROM rdb$database;
-- Result = 2
-- Expected = 1
CREATE SEQUENCE seq_name INCREMENT BY -1;
SELECT NEXT VALUE FOR seq_name FROM rdb$database;
-- Result = -1
-- Expected = 1
CREATE SEQUENCE seq_name INCREMENT BY -2;
SELECT NEXT VALUE FOR seq_name FROM rdb$database;
-- Result = -2
-- Expected = 1


This problem also affects ALTER SEQUENCE RESTART WITH n (see CORE-4349)

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 18/Jun/19 03:17 PM
What will be the GEN_ID(GENERATOR, 0) from the just created GENERATOR? Its initial value minus its increment?

Maybe the correct thing would be to NEXT VALUE increment and return the pre-incremented value.

Both ways would cause compatibility problems.

Mark Rotteveel added a comment - 18/Jun/19 04:14 PM - edited
The problem is that CREATE SEQUENCE seq_name START WITH 1 [INCREMENT BY 1] should behave as CREATE SEQUENCE seq_name, and currently it doesn't. So, fixing the START WITH behavior will be backwards compatible.

And INCREMENT BY is unlikely to be used in combination with GEN_ID, and even so, GEN_ID(seq_name, 0) will continue to behave correctly. Eg:

CREATE SEQUENCE seq_name;
SELECT GEN_ID(seq_name, 0) FROM RDB$DATABASE.
-- Result: 0
-- Expected: 0
CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 1;
SELECT GEN_ID(seq_name, 0) FROM RDB$DATABASE
-- Result: 1
-- Expected: 0 (same as just CREATE SEQUENCE seq_name;)
CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 2;
SELECT GEN_ID(seq_name, 0) FROM RDB$DATABASE
-- Result: 1
-- Expected: -1 (seems the most logical choice)
CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY -1;
SELECT GEN_ID(seq_name, 0) FROM RDB$DATABASE
-- Result: 1
-- Expected: 2 (seems the most logical choice)

The 'best' solution would be if RDB$INITIAL_VALUE reflects the `START WITH` value and the backing generator is offset with -1 * RDB$GENERATOR_INCREMENT (that is: its value will be set to RDB$INITIAL_VALUE - RDB$GENERATOR_INCREMENT). Alternatively, the RDB$INITIAL_VALUE and backing generator should be offset with -1 * RDB$GENERATOR_INCREMENT.

The 'best' solution would be a minor incompatible change compared to Firebird 3.0.0 with regard to the RDB$INITIAL_VALUE for CREATE SEQUENCE seqname (without START WITH) its value would become 1 instead of 0, but I think that is an acceptable price to pay for standard conformance and ease of use.

Mark Rotteveel added a comment - 18/Jun/19 05:00 PM - edited
A closer read of "9.30 Creation of a sequence generator" suggests that `CREATE SEQUENCE INCREMENT BY (a negative value)` (without START WITH) should start at its maximum value, so 2^63 - 1, so conforming behavior for the last two examples in the description should be:

CREATE SEQUENCE seq_name INCREMENT BY -1;
SELECT NEXT VALUE FOR seq_name FROM rdb$database;
-- Result = -1
-- Expected = 2^63 - 1
CREATE SEQUENCE seq_name INCREMENT BY -2;
SELECT NEXT VALUE FOR seq_name FROM rdb$database;
-- Result = -2
-- Expected = 2^63 - 1

However, that might be a bigger change from the current behavior than should be tackled in this ticket.

Pavel Zotov added a comment - 05/Aug/20 05:58 PM
4.0.0.2127, intermediate build with timestamp 05-aug-2020 01:58:

recreate table test(id int);
recreate sequence g;
insert into test(id) select gen_id(g,1) from rdb$types rows 3;
select * from test;

     ID
=======
      0
      1
      2

Lot of tests can suppose that first (minimal) ID will be 1, not zero.
What about backward compatibility of this ?

Adriano dos Santos Fernandes added a comment - 05/Aug/20 06:05 PM - edited
So seems we should change the default start to 1.

However, the case "create sequence s1 increment by 10" will have the first value as 1, not 10 (as before) - after all, this is the bug and a change is expected.

Mark Rotteveel added a comment - 05/Aug/20 06:06 PM
@Pavel This looks like a bug to me. A sequence created with RECREATE SEQUENCE without explicitly specifying a start value should start at 1, just like a sequence created with CREATE SEQUENCE.