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

Last updated: 2019-09-02 16:05:26 +0200

Comment 27211

Date: 2019-08-13 17:52:41 +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:

According to the SQL documentation it should be possible to run command:
CREATE SEQUENCE seq;
However it fails with:
Error: syntax error, unexpected SCOLON in: "create sequence seq;"
SQLState: 42000

Other RDBMS allow this command without an error. They use default data type bigint, start with 1 and increment by 1.

Reproducible: Always

Steps to Reproduce:

create sequence seq;
-- this fails

create sequence seq1 AS int;
create sequence seq2 START WITH 2;
create sequence seq3 INCREMENT BY 3;
create sequence seq4 MINVALUE 4;
create sequence seq5 NO MINVALUE;
create sequence seq6 MAXVALUE 6;
create sequence seq7 NO MAXVALUE;
create sequence seq8 CACHE 8;
create sequence seq9 NO CYCLE;
create sequence seq0 CYCLE;

select schema_id, name, start, minvalue, maxvalue, increment, cacheinc, cycle from sequences;

drop sequence seq;
drop sequence seq1;
drop sequence seq2;
drop sequence seq3;
drop sequence seq4;
drop sequence seq5;
drop sequence seq6;
drop sequence seq7;
drop sequence seq8;
drop sequence seq9;
drop sequence seq0;

Actual Results:

sql>create sequence seq;
syntax error, unexpected SCOLON in: "create sequence seq;"
sql>-- this fails
sql>
sql>create sequence seq1 AS int;
operation successful
sql>create sequence seq2 START WITH 2;
operation successful
sql>create sequence seq3 INCREMENT BY 3;
operation successful
sql>create sequence seq4 MINVALUE 4;
operation successful
sql>create sequence seq5 NO MINVALUE;
operation successful
sql>create sequence seq6 MAXVALUE 6;
operation successful
sql>create sequence seq7 NO MAXVALUE;
operation successful
sql>create sequence seq8 CACHE 8;
operation successful
sql>create sequence seq9 NO CYCLE;
operation successful
sql>create sequence seq0 CYCLE;
operation successful
sql>
sql>select schema_id, name, start, minvalue, maxvalue, increment, cacheinc, cycle from sequences;
+---------+-----------+-------+---------+---------+---------+---------+-------+
| schema_ | name | start | minvalu | maxvalu | increme | cachein | cycle |
: id : : : e : e : nt : c : :
+=========+===========+=======+=========+=========+=========+=========+=======+
| 2000 | seq_22285 | 1 | 0 | 0 | 1 | 1 | false |
| 2000 | seq1 | 1 | 0 | 0 | 1 | 1 | false |
| 2000 | seq2 | 2 | 0 | 0 | 1 | 1 | false |
| 2000 | seq3 | 1 | 0 | 0 | 3 | 1 | false |
| 2000 | seq4 | 1 | 4 | 0 | 1 | 1 | false |
| 2000 | seq5 | 1 | 0 | 0 | 1 | 1 | false |
| 2000 | seq6 | 1 | 0 | 6 | 1 | 1 | false |
| 2000 | seq7 | 1 | 0 | 0 | 1 | 1 | false |
| 2000 | seq8 | 1 | 0 | 0 | 1 | 8 | false |
| 2000 | seq9 | 1 | 0 | 0 | 1 | 1 | false |
| 2000 | seq0 | 1 | 0 | 0 | 1 | 1 | false |
+---------+-----------+-------+---------+---------+---------+---------+-------+
11 tuples
sql>
sql>drop sequence seq;
DROP SEQUENCE: no such sequence 'seq'
sql>drop sequence seq1;
operation successful
sql>drop sequence seq2;
operation successful
sql>drop sequence seq3;
operation successful
sql>drop sequence seq4;
operation successful
sql>drop sequence seq5;
operation successful
sql>drop sequence seq6;
operation successful
sql>drop sequence seq7;
operation successful
sql>drop sequence seq8;
operation successful
sql>drop sequence seq9;
operation successful
sql>drop sequence seq0;
operation successful
sql>

Expected Results:

create sequence seq;
should succeed without an error

Also note that for seq0 the cycle value is false but should have been true.

https://www.monetdb.org/Documentation/Manuals/SQLreference/SerialTypes
https://www.monetdb.org/Documentation/Manuals/SQLreference/SQLSyntaxOverviewCREATE_SEQUENCE

Comment 27216

Date: 2019-08-14 11:34:31 +0200
From: MonetDB Mercurial Repository <>

Changeset 369967e06405 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=369967e06405

Changeset description:

Added test and fix for bug #6744.