CREATE SEQUENCE START WITH has wrong initial value [CORE6084]

[firebird-automations]

Submitted by: @mrotteveel

Relate to CORE6376
Replaces CORE4349

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 CORE4349)

Commits: 44660f8 4d1ce73 23dc0c6 FirebirdSQL/fbt-repository@5f4a198 FirebirdSQL/fbt-repository@641376f FirebirdSQL/fbt-repository@70bbf35

[firebird-automations]

Modified by: @mrotteveel

description: 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

This problem also affects ALTER SEQUENCE RESTART WITH n (see CORE4349)

=>

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

This problem also affects ALTER SEQUENCE RESTART WITH n (see CORE4349)

[firebird-automations]

Modified by: @mrotteveel

description: 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

This problem also affects ALTER SEQUENCE RESTART WITH n (see CORE4349)

=>

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 CORE4349)

[firebird-automations]

Commented by: @asfernandes

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.

[firebird-automations]

Commented by: @mrotteveel

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.

[firebird-automations]

Commented by: @mrotteveel

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.

[firebird-automations]

Modified by: @mrotteveel

Link: This issue relate to CORE6376 [ CORE6376 ]

[firebird-automations]

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

[firebird-automations]

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 RC 1 [ 10930 ]

[firebird-automations]

Commented by: @pavel-zotov

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 ?

[firebird-automations]

Commented by: @asfernandes

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.

[firebird-automations]

Commented by: @mrotteveel

@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.

[firebird-automations]

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

[firebird-automations]

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Modified by: @mrotteveel

Link: This issue replaces CORE4349 [ CORE4349 ]