Bug 6745 - CREATE SEQUENCE seqbool AS boolean (or char or CLOB or BLOB or inet or uuid or other non-integer datatype) should NOT be allowed
Summary: CREATE SEQUENCE seqbool AS boolean (or char or CLOB or BLOB or inet or uuid o...
Status: RESOLVED FIXED
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: 11.33.3 (Apr2019)
Hardware: All All
: Normal normal
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-08-13 19:35 CEST by Martin van Dinther
Modified: 2019-09-12 13:07 CEST (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Martin van Dinther cwiconfidential 2019-08-13 19:35:51 CEST
User-Agent:       Mozilla/5.0 (Windows NT 6.1; rv:68.0) Gecko/20100101 Firefox/68.0
Build Identifier: 

Creating sequence with non-integer datatype should not be allowed.
Currently they are accepted. It appears the datatype is not used/honered, they default back to an integer (I guess), see steps to reproduce below.

Also there is no way to query the sequences datatype after creation. This information should be added to the table sys.sequences (if it is anything other than bigint).

Reproducible: Always

Steps to Reproduce:
CREATE SEQUENCE seqBool AS boolean;
CREATE SEQUENCE seqChar AS char;
CREATE SEQUENCE seqClob AS CLOB;
CREATE SEQUENCE seqBlob AS BLOB;
CREATE SEQUENCE seqDate AS date;
CREATE SEQUENCE seqTime AS time;
CREATE SEQUENCE seqInet AS inet;
CREATE SEQUENCE seqUuid AS uuid;

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

select get_value_for('sys','seqbool');
select get_value_for('sys','seqchar');
select get_value_for('sys','seqclob');
select get_value_for('sys','seqblob');
select get_value_for('sys','seqdate');
select get_value_for('sys','seqtime');
select get_value_for('sys','seqinet');
select get_value_for('sys','sequuid');

select next_value_for('sys','seqbool');
select next_value_for('sys','seqchar');
select next_value_for('sys','seqclob');
select next_value_for('sys','seqblob');
select next_value_for('sys','seqdate');
select next_value_for('sys','seqtime');
select next_value_for('sys','seqinet');
select next_value_for('sys','sequuid');

select NEXT VALUE FOR sys.seqBool;
select NEXT VALUE FOR sys.seqChar;
select NEXT VALUE FOR sys.seqClob;
select NEXT VALUE FOR sys.seqBlob;
select NEXT VALUE FOR sys.seqDate;
select NEXT VALUE FOR sys.seqTime;
select NEXT VALUE FOR sys.seqInet;
select NEXT VALUE FOR sys.seqUuid;

DROP SEQUENCE seqBool;
DROP SEQUENCE seqChar;
DROP SEQUENCE seqClob;
DROP SEQUENCE seqBlob;
DROP SEQUENCE seqDate;
DROP SEQUENCE seqTime;
DROP SEQUENCE seqInet;
DROP SEQUENCE seqUuid;

Actual Results:  
sql>CREATE SEQUENCE seqBool AS boolean;
operation successful
sql>CREATE SEQUENCE seqChar AS char;
operation successful
sql>CREATE SEQUENCE seqClob AS CLOB;
operation successful
sql>CREATE SEQUENCE seqBlob AS BLOB;
operation successful
sql>CREATE SEQUENCE seqDate AS date;
operation successful
sql>CREATE SEQUENCE seqTime AS time;
operation successful
sql>CREATE SEQUENCE seqInet AS inet;
operation successful
sql>CREATE SEQUENCE seqUuid AS uuid;
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 | seqbool   |     1 |       0 |       0 |       1 |       1 | false |
|    2000 | seqchar   |     1 |       0 |       0 |       1 |       1 | false |
|    2000 | seqclob   |     1 |       0 |       0 |       1 |       1 | false |
|    2000 | seqblob   |     1 |       0 |       0 |       1 |       1 | false |
|    2000 | seqdate   |     1 |       0 |       0 |       1 |       1 | false |
|    2000 | seqtime   |     1 |       0 |       0 |       1 |       1 | false |
|    2000 | seqinet   |     1 |       0 |       0 |       1 |       1 | false |
|    2000 | sequuid   |     1 |       0 |       0 |       1 |       1 | false |
+---------+-----------+-------+---------+---------+---------+---------+-------+
8 tuples
sql>
sql>select get_value_for('sys','seqbool');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>select get_value_for('sys','seqchar');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>select get_value_for('sys','seqclob');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>select get_value_for('sys','seqblob');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>select get_value_for('sys','seqdate');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>select get_value_for('sys','seqtime');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>select get_value_for('sys','seqinet');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>select get_value_for('sys','sequuid');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>
sql>select next_value_for('sys','seqbool');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>select next_value_for('sys','seqchar');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>select next_value_for('sys','seqclob');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>select next_value_for('sys','seqblob');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>select next_value_for('sys','seqdate');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>select next_value_for('sys','seqtime');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>select next_value_for('sys','seqinet');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>select next_value_for('sys','sequuid');
+------+
| L2   |
+======+
|    1 |
+------+
1 tuple
sql>
sql>select NEXT VALUE FOR sys.seqBool;
+------+
| L2   |
+======+
|    2 |
+------+
1 tuple
sql>select NEXT VALUE FOR sys.seqChar;
+------+
| L2   |
+======+
|    2 |
+------+
1 tuple
sql>select NEXT VALUE FOR sys.seqClob;
+------+
| L2   |
+======+
|    2 |
+------+
1 tuple
sql>select NEXT VALUE FOR sys.seqBlob;
+------+
| L2   |
+======+
|    2 |
+------+
1 tuple
sql>select NEXT VALUE FOR sys.seqDate;
+------+
| L2   |
+======+
|    2 |
+------+
1 tuple
sql>select NEXT VALUE FOR sys.seqTime;
+------+
| L2   |
+======+
|    2 |
+------+
1 tuple
sql>select NEXT VALUE FOR sys.seqInet;
+------+
| L2   |
+======+
|    2 |
+------+
1 tuple
sql>select NEXT VALUE FOR sys.seqUuid;
+------+
| L2   |
+======+
|    2 |
+------+
1 tuple
sql>
sql>DROP SEQUENCE seqBool;
operation successful
sql>DROP SEQUENCE seqChar;
operation successful
sql>DROP SEQUENCE seqClob;
operation successful
sql>DROP SEQUENCE seqBlob;
operation successful
sql>DROP SEQUENCE seqDate;
operation successful
sql>DROP SEQUENCE seqTime;
operation successful
sql>DROP SEQUENCE seqInet;
operation successful
sql>DROP SEQUENCE seqUuid;
operation successful
sql>

Expected Results:  
Disallow creation of sequences on non-integer datatypes.
Extend sys.sequences with "datatype" column, showing the datatype.
Comment 1 MonetDB Mercurial Repository cwiconfidential 2019-08-14 13:31:52 CEST
Changeset d3433f5b5acb, made by Pedro Ferreira <pedro.ferreira@monetdbsolutions.com> in the MonetDB repo, refers to this bug.

For complete details, see https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d3433f5b5acb

Changeset description:

	Added test and fix for bug 6745.
Comment 2 MonetDB Mercurial Repository cwiconfidential 2019-08-14 13:37:37 CEST
Changeset 29b761a34680, made by Pedro Ferreira <pedro.ferreira@monetdbsolutions.com> in the MonetDB repo, refers to this bug.

For complete details, see https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=29b761a34680

Changeset description:

	Another check for bug 6745 test.
Comment 3 Pedro Ferreira 2019-08-14 13:51:30 CEST
Internally MonetDB stores sequences as big integers, so storing the "type" of the sequence is not effective as of now. We should do if we are willing to store other data types as well.