Date: 2019-08-13 17:14:01 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.33.3 (Apr2019)
CC: @njnes

Last updated: 2019-09-12 13:08:56 +0200

Comment 27210

Date: 2019-08-13 17:14:01 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (Windows NT 6.1; rv:68.0) Gecko/20100101 Firefox/68.0
Build Identifier:

I expected that NEXT VALUE FOR and next_value_for() return the incremented value instead of the current value of the sequence.

Reproducible: Always

Steps to Reproduce:

create sequence seq as int;
-- select * from sys.sequences

select get_value_for('sys','seq');
-- returns 1
select get_value_for('sys','seq');
-- returns 1

select next_value_for('sys','seq');
-- returns 1, expected 2
select next_value_for('sys','seq');
-- returns 2, expected 3

select get_value_for('sys','seq');
-- returns 3
select get_value_for('sys','seq');
-- returns 3

select next_value_for('sys','seq');
-- returns 3, expected 4
select get_value_for('sys','seq');
-- returns 3

SELECT NEXT VALUE FOR sys.seq;
-- returns 4, expected 5
select get_value_for('sys','seq');
-- returns 5

drop sequence seq;

Actual Results:

sql>create sequence seq as int;
operation successful
sql>-- select * from sys.sequences
sql>select get_value_for('sys','seq');
+------+
| L2 |
+======+
| 1 |
+------+
1 tuple
sql>-- returns 1
sql>select get_value_for('sys','seq');
+------+
| L2 |
+======+
| 1 |
+------+
1 tuple
sql>-- returns 1
sql>select next_value_for('sys','seq');
+------+
| L2 |
+======+
| 1 |
+------+
1 tuple
sql>-- returns 1, expected 2
sql>select next_value_for('sys','seq');
+------+
| L2 |
+======+
| 2 |
+------+
1 tuple
sql>-- returns 2, expected 3
sql>select get_value_for('sys','seq');
+------+
| L2 |
+======+
| 3 |
+------+
1 tuple
sql>-- returns 3
sql>select get_value_for('sys','seq');
+------+
| L2 |
+======+
| 3 |
+------+
1 tuple
sql>-- returns 3
sql>select next_value_for('sys','seq');
+------+
| L2 |
+======+
| 3 |
+------+
1 tuple
sql>-- returns 3, expected 4
sql>select get_value_for('sys','seq');
+------+
| L2 |
+======+
| 4 |
+------+
1 tuple
sql>-- returns 3
sql>SELECT NEXT VALUE FOR sys.seq;
+------+
| L2 |
+======+
| 4 |
+------+
1 tuple
sql>-- returns 4, expected 5
sql>select get_value_for('sys','seq');
+------+
| L2 |
+======+
| 5 |
+------+
1 tuple
sql>-- returns 5
sql>drop sequence seq;
operation successful
sql>

Expected Results:

I expected that NEXT VALUE FOR and next_value_for() return the incremented value instead of the current value of the sequence.

Comment 27224

Date: 2019-08-14 13:59:27 +0200
From: MonetDB Mercurial Repository <>

Changeset c6ed9d7db2fa made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=c6ed9d7db2fa

Changeset description:

Added test for bug #6743.

Comment 27225

Date: 2019-08-14 14:39:42 +0200
From: @njnes

the semantics are that the get_value returns the to be given out sequence number,
without incrementing the sequence number.
the next_value gives out the next (free) sequence number. At the start
the first value (start value) isn't used jet.

Comment 27277

Date: 2019-09-12 13:08:56 +0200
From: MonetDB Mercurial Repository <>

Changeset cfc1ba660c53 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=cfc1ba660c53

Changeset description:

Approved output for bug #6743