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
Comments
Modified by: @asfernandesassignee: Adriano dos Santos Fernandes [ asfernandes ] |
Modified by: @asfernandesstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 4.0 RC 1 [ 10930 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] QA Status: No test => Covered by another test(s) Test Details: See fbt-repo\tests\functional\generator\alter_01.fbt |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @mrotteveel |
I'm looking in SQL Standard 2016 and here are what I see:
below this General Rules 2e says:
And it seems reasonable. Am I wrong? |
@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.")). |
I understand "base value" as "initial value" in terms of Firebird. This means "current value" is not "base value". The rule 9c clearly says
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. |
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. |
Or in other words, Syntax Rule 9a defines that if you do |
Or, to quote from section 4.27 Sequence generators:
|
and
|
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. |
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
The text was updated successfully, but these errors were encountered: