Firebird 3 vs Firebird 4 SEQUENCE RESTART

[Martijn Tonies (Upscene Productions)]

Hi all,

 

There’s a difference in behavior for the

 

ALTER SEQUENCE name RESTART WITH value

 

between Firebird 3 and Firebird 4.

 

I think this should be clarified in the Note for this command in the documentation https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/fblangref40-ddl-sequence.html#fblangref40-ddl-sequence-alter

 

 

The difference is, in Firebird 3, executing a NEXT VALUE FOR will use the specified value and increment by 1 (or the specified increment during creation), in Firebird 4, it will return the specified value as the next value.

 

The current value is set to the specified RESTART WITH value in Firebird 3, but to the value minus the increment in Firebird 4.

 

Although the documentation does explain how to properly set the value to get to the expected value in Firebird 4, the difference in behavior isn’t documented.

 

With regards,

Martijn Tonies
Upscene Productions
https://www.upscene.com

Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL, MySQL, MariaDB, InterBase, SQLite, NexusDB and Firebird.

[Dimitry Sibiryakov]

Martijn Tonies (Upscene Productions) wrote 27.05.2024 14:18:
> Although the documentation does explain how to properly set the value to get to
> the expected value in Firebird 4, the difference in behavior isn’t documented.

It is documented in Release Notes:
https://firebirdsql.org/file/documentation/release_notes/html/en/4_0/rlsnotes40.html#rnfb40-compat-sql-sequence-start-value

--
WBR, SD.

[Virgo Pärna]

On 27.05.2024 15:18, Martijn Tonies (Upscene Productions) wrote:
> Although the documentation does explain how to properly set the value to
> get to the expected value in Firebird 4, the difference in behavior
> isn’t documented.
>

Was it not mentioned in release notes of Firebird 4?

--
Virgo Pärna
Gaiasoft OÜ
vi...@gaiasoft.ee

[Martijn Tonies (Upscene Productions)]

The Release Notes say this:

"
Before Firebird 4.0 a sequence was created with its current value set to its
starting value (or zero by default). So a sequence with starting value = 0
and increment = 1 starts at 1. While such a sequence has the same result in
Firebird 4.0 (i.e. also starts at 1), the underlying implementation is
different, thus making other cases different.

Now a sequence is created (or restarted) with its current value set to its
starting value minus its increment. And the default starting value is
changed to 1. Then a sequence with starting value = 100 and increment = 10
has its first NEXT VALUE equal to 100 now, while it was 110 before.
Likewise, this sequence has its first GEN_ID(SEQ, 1) equal to 91 now, while
it was 101 before.
"


Could be me, but I don't find this particularly clear with regards to a
behavioral change or RESTART WITH differences. :)

With regards,

Martijn Tonies
Upscene Productions
https://www.upscene.com

Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL,
MySQL, MariaDB, InterBase, SQLite, NexusDB and Firebird.

[Mark Rotteveel]

On 27/05/2024 14:18, Martijn Tonies (Upscene Productions) wrote:
> Hi all,
> There’s a difference in behavior for the
> ALTER SEQUENCE name RESTART WITH value
> between Firebird 3 and Firebird 4.
> I think this should be clarified in the Note for this command in the
> documentation

> https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/fblangref40-ddl-sequence.html#fblangref40-ddl-sequence-alter <https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/fblangref40-ddl-sequence.html#fblangref40-ddl-sequence-alter>

> The difference is, in Firebird 3, executing a NEXT VALUE FOR will use
> the specified value and increment by 1 (or the specified increment
> during creation), in Firebird 4, it will return the specified value as
> the next value.
> The current value is set to the specified RESTART WITH value in Firebird
> 3, but to the value minus the increment in Firebird 4.

The SQL standard requires that RESTART WITH <value> indicates the next
value to be generated, but instead in Firebird 3.0 it was set as the
current value of the sequence, resulting in NEXT VALUE FOR generating
<value> + <increment> instead of <value> as specified by the standard.
This was fixed in Firebird 4.0.

> Although the documentation does explain how to properly set the value to
> get to the expected value in Firebird 4, the difference in behavior
> isn’t documented.

The current behaviour is documented on the page you link:

"""
The RESTART WITH newvalue clause allows you to set the next value
generated by NEXT VALUE FOR seq_name.
"""

I notice that the paragraph before that and the table are a bit
confusing in that regard as they still mention it sets the current
value. I'll see if I can improve that.

The Language Reference does not and will not document the *difference*
in behaviour compared to Firebird 3.0, as basically we try to avoid
historical information like that in the documentation (though we don't
always succeed), as that is what release notes are for. On the other
hand, the Firebird 3.0 Language Reference does explicitly call out the
wrong behaviour as a bug[1] under "Bugs with RESTART" and says it'll be
fixed in Firebird 4.0.

Mark

[1]:
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html#fblangref30-ddl-sequence-alter

--
Mark Rotteveel

[Mark Rotteveel]

On 27/05/2024 14:28, Martijn Tonies (Upscene Productions) wrote:
> The Release Notes say this:
>
> "
> Before Firebird 4.0 a sequence was created with its current value set to
> its starting value (or zero by default). So a sequence with starting
> value = 0 and increment = 1 starts at 1. While such a sequence has the
> same result in Firebird 4.0 (i.e. also starts at 1), the underlying
> implementation is different, thus making other cases different.
>
> Now a sequence is created (or restarted) with its current value set to
> its starting value minus its increment. And the default starting value
> is changed to 1. Then a sequence with starting value = 100 and increment
> = 10 has its first NEXT VALUE equal to 100 now, while it was 110 before.
> Likewise, this sequence has its first GEN_ID(SEQ, 1) equal to 91 now,
> while it was 101 before.
> "
>
>
> Could be me, but I don't find this particularly clear with regards to a
> behavioral change or RESTART WITH differences. :)

The behavioral change is:
CREATE SEQUENCE SEQ_X START WITH 100 INCREMENT BY 10;
or
ALTER SEQUENCE SEQ_X RESTART WITH 100 INCREMENT BY 10;

With Firebird 3.0

(1) NEXT VALUE FOR SEQ_X -> 110 (not SQL compliant!)
(2) GEN_ID(SEQ_X, 1) -> 101

(that is, either (1) or (2) is executed immediately after the CREATE
SEQUENCE or ALTER SEQUENCE)

With Firebird 4.0:

(1) NEXT VALUE FOR SEQ_X -> 100 (SQL compliant!)
(2) GEN_IDX(SEQ_X, 1) -> 91

With this, behaviour (1) is now compliant with the SQL standard, but the
change of behaviour (2) might be unexpected for some, and is the result
of that we needed to "correct" the current value of the sequence so the
NEXT VALUE FOR produces the right result. The alternative was modifying
how sequences were used by considering the current value the next value,
which had far more messy implications, as people sometimes use
GEN_ID(..., 0) to obtain the last generated value.

Mark
--
Mark Rotteveel

[Martijn Tonies (Upscene Productions)]

Hello Mark,

Thanks for the detailed explanation.

With regards,

Martijn Tonies
Upscene Productions
https://www.upscene.com

Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL,
MySQL, MariaDB, InterBase, SQLite, NexusDB and Firebird.

-----Original Message-----

--
You received this message because you are subscribed to the Google Groups
"firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit
https://groups.google.com/d/msgid/firebird-support/a58e9854-e145-4a10-8f45-df2ba15126d1%40lawinegevaar.nl.