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.

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

ALTER SEQUENCE RESTART WITH <n> should not change the initial sequence START value [CORE6386] #6625

Closed
firebird-automations opened this issue Aug 11, 2020 · 13 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @asfernandes

Replaces CORE5133

Standalone ALTER SEQUENCE RESTART WITH and IDENTITY COLUMN RESTART WITH has different behavior.

If standalone sequence is created with initial value 10, then later
RESTARTed WITH value 20 and then RESTARTed, it's restarted to 20.

With identity column RESTART WITH value is not saved and the last
restart goes to 10.

Accordingly to the standard, the START value is part of the "descriptor", so it's stored in metadata.

But in ALTER SEQUENCE RESTART WITH, that descriptor is not updated.

It does only update the sequence's current value.

We had this behavior but it was changed with CORE5133.

Commits: e94c66b FirebirdSQL/fbt-repository@95fb600

====== Test Details ======

See fbt-repo\tests\functional\generator\alter_01.fbt

@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

Modified by: @pavel-zotov

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

QA Status: No test => Covered by another test(s)

Test Details: See fbt-repo\tests\functional\generator\alter_01.fbt

@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 CORE5133 [ CORE5133 ]

@romansimakov
Copy link
Collaborator

romansimakov commented Aug 16, 2022

I'm looking in SQL Standard 2016 and here are what I see:
Chapter 9.31 Altering a sequence generator
Page 677
Syntax Rules 9b says:

b) If <alter sequence generator restart option> is specified and contains a <sequence generator restart
value>, then let NEWVAL be that <sequence generator restart value>.

below this General Rules 2e says:

e) The current base value is set to NEWVAL.

And it seems reasonable. Am I wrong?

@romansimakov romansimakov reopened this Aug 16, 2022
@mrotteveel
Copy link
Member

mrotteveel commented Aug 16, 2022

@romansimakov The rules you quote are about the current value (base value) of the sequence. The current behaviour complies with the SQL standard. What you're confused about is that the SQL standard describes it from the point of view that the next value to be generated is stored, while Firebird stores the last generated value. So, to be able to conform, we need to adjust.

The SQL standard rules do not say anything about modifying the start value, what this ticket is about (notice the total absence of an equivalent of rule 2c of 9.30 creation of a sequence generator ("The start value, set to START.")).

@romansimakov
Copy link
Collaborator

romansimakov commented Aug 16, 2022

I understand "base value" as "initial value" in terms of Firebird. This means "current value" is not "base value". The rule 9c clearly says

If <alter sequence generator restart option> is not specified, then let NEWVAL be the current base
value of SEQ.

I proofs my point because otherwise current value will not be changed.

So if restart option specifies NEWVAL than according to general rule current base must be set to NEWVAL.

@mrotteveel
Copy link
Member

mrotteveel commented Aug 16, 2022

The Syntax Rules explain how the different values NEWIV, NEWMAX, NEWMIN, NEWCYCLE and NEWVAL are derived so they can be set in General Rules 2. What rule Syntax Rule 9a is: "If <alter sequence generator restart option> is not specified, then let NEWVAL be the current base value of SEQ.", which - rephrased - means that if the restart option is not specified, then NEWVAL is the current base value, so the effect of applying General Rule 2e is that the current base value doesn't change.

@mrotteveel
Copy link
Member

mrotteveel commented Aug 16, 2022

Or in other words, Syntax Rule 9a defines that if you do ALTER SEQUENCE SEQ INCREMENT BY 5 that the generator retains its current base value.

@mrotteveel
Copy link
Member

mrotteveel commented Aug 16, 2022

Or, to quote from section 4.27 Sequence generators:

A sequence generator has a time-varying current base value, which is a value of its data type. A sequence generator has a cycle which consists of all the possible values between the minimum value and the maximum value which are expressible as (current base value + N * increment), where N is a non-negative number.

When a sequence generator is created, its current base value is initialized to the start value. Subsequently, the current base value is set to the value of the lowest non-issued value in the cycle for an ascending sequence generator, or the highest non-issued value in the cycle for a descending sequence generator.

@mrotteveel
Copy link
Member

and

Any time after a sequence generator is created, its current base value can be set to an arbitrary value of its data type by an <alter sequence generator statement>.

@romansimakov
Copy link
Collaborator

It seems you are right. 4.27 makes clear different between "current base value" and "start value". It looks SQL Standard does not provide a way to change start value other than recreation. Users ask for it. I suppose it's a good feature to have.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

4 participants