Creation of serial types does not accept negative numbers

[monetdb-team]

Date: 2018-12-06 13:46:06 +0100
From: @swingbit
To: SQL devs <>
Version: 11.29.7 (Mar2018-SP1)
CC: @PedroTadim

Last updated: 2019-01-14 17:29:14 +0100

Comment 26711

Date: 2018-12-06 13:46:06 +0100
From: @swingbit

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.110 Safari/537.36
Build Identifier:

From https://www.monetdb.org/Documentation/Manuals/SQLreference/SerialTypes I see that sequence parameters are defined as "non-zero" values.

seq_params:
[ AS datatype ]
[ START WITH nonzero-bigint ]
[ INCREMENT BY nonzerol-bigint ]

That raises a few questions for me:

1. Why cannot the start value be 0? In fact, it can (so this is just a documentation issue):

sql>create sequence myseq as integer start with 0 increment by 1;
operation successful

2. Why cannot START WITH be negative? This cannot indeed be done, but I see no reason for it:

sql>create sequence myseq as integer start with -1 increment by 1;
syntax error, unexpected '-', expecting sqlINT in: "create sequence myseq as integer start with -"

Notice that the error reports "sqlINT" as expected. As far as I can tell, sqlINT includes negative integers.

3. Same as above, but for INCREMENT BY. That should also be allowed to be negative, but it isn't:

sql>create sequence myseq as integer start with 1 increment by -1;
syntax error, unexpected '-', expecting sqlINT in: "create sequence myseq as integer start with 1 increment by -"

Again, the error is misleading.

Reproducible: Always

Comment 26712

Date: 2018-12-06 13:47:09 +0100
From: @swingbit

The above applies of course also for the syntax: GENERATED ALWAYS AS IDENTITY [ '(' seq_params ')' ]

Comment 26713

Date: 2018-12-06 14:27:25 +0100
From: @PedroTadim

I just checked the SQL standard, and indeed negative values for sequences are allowed, as well increments. I'm working to add this feature.

Comment 26714

Date: 2018-12-06 16:46:23 +0100
From: MonetDB Mercurial Repository <>

Changeset b2e362c68b8b 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=b2e362c68b8b

Changeset description:

Added test and fixes for bug #6665, by allowing sql sequences with negative values.

Comment 26721

Date: 2018-12-11 14:56:39 +0100
From: @sjoerdmullender

I have updated the text on the references web page and the help text that you get in mclient using \help.
Pedro already fixed the parser, so I think we can now close this bug report.