On 05-08-2020 10:53, Mark Rotteveel wrote:
On 05-08-2020 03:34, Adriano dos Santos Fernandes wrote:
I'm looking at different behavior of standalone ALTER SEQUENCE RESTART
and IDENTITY COLUMN RESTART.
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.
Personally I prefer the standalone sequence behavior, but it's not what
I understand in 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.
Could someone also take a look?
Should we change one or another behavior?
Reading SQL:2016, 9.31 Altering a sequence generator (specifically
General Rules point 2), it looks like an ALTER SEQUENCE RESTART WITH n
should not change the 'default' restart value of the sequence. Looking
at the syntax of ALTER SEQUENCE in the standard, that can only be
changed by dropping and recreating the sequence (because the `START
WITH` sequence option is excluded from the options allowed for ALTER
SEQUENCE.
In short, I guess that ALTER SEQUENCE RESTART WITH n in Firebird should
change to not alter the default start value on RESTART. Maybe we should
support the non-standard behaviour by allowing the START WITH option in
ALTER SEQUENCE, and adding a SET START WITH <value> to the alter
identity column syntax.
BTW: The incorrect behaviour of ALTER SEQUENCE (from a SQL standard
perspective) was introduced by
http://tracker.firebirdsql.org/browse/CORE-5133
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
