Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

CREATE SEQUENCE seq; fails #6744

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

CREATE SEQUENCE seq; fails #6744

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant