Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

CREATE SEQUENCE START WITH has wrong initial value [CORE6084] #6334

Closed
firebird-automations opened this issue Jun 18, 2019 · 14 comments
Closed

CREATE SEQUENCE START WITH has wrong initial value [CORE6084] #6334

firebird-automations opened this issue Jun 18, 2019 · 14 comments
Assignees
@asfernandes
Labels
affect-version: 3.0.4 affect-version: 4.0 Beta 1 component: engine fix-version: 4.0 RC 1 priority: major qa: done successfully type: bug

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @mrotteveel

Link: This issue relate to CORE6376 [ CORE6376 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 RC 1 [ 10930 ]

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @mrotteveel

Link: This issue replaces CORE4349 [ CORE4349 ]

@mrotteveel mrotteveel mentioned this issue Dec 31, 2021
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@asfernandes asfernandes

Labels
affect-version: 3.0.4 affect-version: 4.0 Beta 1 component: engine fix-version: 4.0 RC 1 priority: major qa: done successfully type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@asfernandes @firebird-automations