Date: 2018-06-26 14:53:00 +0200
From: @yzchang
To: SQL devs <>
Version: 11.29.3 (Mar2018)
CC: @njnes, @yzchang

Last updated: 2018-08-31 13:23:25 +0200

Comment 26502

Date: 2018-06-26 14:53:00 +0200
From: @yzchang

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/11.1.1 Safari/605.1.15
Build Identifier:

When creating a table with an auto_increment column, a sequence is created automatically. However, the dependency on this sequence is not checked when one tries to drop this sequence.

Also, the dependency of the table on the sequence is not recorded in 'sys'.'dependences'.

Reproducible: Always

Steps to Reproduce:

The following queries demonstrate the two problems mentioned above:

select * from sequences;
create table t (i int auto_increment, val int);
select * from sequences;
-- however, the dependency is not recorded in the "dependencies" table:
select * from dependencies d, sequences s where d.id = s.id or d.depend_id = s.id;
insert into t(val) values (425), (492);
drop sequence seq_8595; -- this shouldn't succeed
insert into t(val) values (425), (492); -- now we can't insert values any more

Actual Results:

1. no record in the "dependencies" table to register the dependency of table "t" on the automatically created sequence for column "i".
2. the new sequence can be dropped, while "t" still exists. After this, insert into "t" returns error on column "i"

Expected Results:

1. The dependency of "t" on the sequence should be recorded in "dependencies" table.
2. Dropping the sequence while "t" exists should return a dependency violated error.

Comment 26505

Date: 2018-06-26 15:12:58 +0200
From: @yzchang

I updated the subject of this bug, because:

1. The problem also happens with explicitly created sequence:

sql>CREATE SEQUENCE "my_test_seq" as integer START WITH 2;
operation successful
sql>CREATE TABLE test (t int DEFAULT NEXT VALUE FOR "my_test_seq", v char);
operation successful
sql>INSERT INTO test(v) VALUES ('a');
1 affected row, last generated key: 2
sql>drop sequence my_test_seq;
operation successful
sql>INSERT INTO test(v) VALUES ('a');
NEXT VALUE FOR: no such sequence 'sys'.'my_test_seq'

2. Some parts of MonetDB, e.g. DROP TABLE, handles the column=>sequence dependency correctly. The queries below show that DROP TABLE also deletes the automatically created sequence (but not an explicitly created sequence). For now, I only know that DROP SEQUENCE doesn't check the dependency.

sql>select * from sequences;
+----+-----------+------+-------+----------+----------+-----------+----------+-------+
| id | schema_id | name | start | minvalue | maxvalue | increment | cacheinc | cycle |
+====+===========+======+=======+==========+==========+===========+==========+=======+
+----+-----------+------+-------+----------+----------+-----------+----------+-------+
0 tuples
sql>create table t (i int auto_increment);
operation successful
sql>select * from sequences;
+------+-----------+----------+-------+----------+----------+-----------+----------+-------+
| id | schema_id | name | start | minvalue | maxvalue | increment | cacheinc | cycle |
+======+===========+==========+=======+==========+==========+===========+==========+=======+
| 8627 | 2000 | seq_8623 | 1 | 0 | 0 | 1 | 1 | false |
+------+-----------+----------+-------+----------+----------+-----------+----------+-------+
1 tuple
sql>drop table t;
operation successful
sql>select * from sequences;
+----+-----------+------+-------+----------+----------+-----------+----------+-------+
| id | schema_id | name | start | minvalue | maxvalue | increment | cacheinc | cycle |
+====+===========+======+=======+==========+==========+===========+==========+=======+
+----+-----------+------+-------+----------+----------+-----------+----------+-------+
0 tuples
sql>CREATE SEQUENCE "my_test_seq" as integer START WITH 2;
operation successful
sql>select * from sequences;
+------+-----------+-------------+-------+----------+----------+-----------+----------+-------+
| id | schema_id | name | start | minvalue | maxvalue | increment | cacheinc | cycle |
+======+===========+=============+=======+==========+==========+===========+==========+=======+
| 8630 | 2000 | my_test_seq | 2 | 0 | 0 | 1 | 1 | false |
+------+-----------+-------------+-------+----------+----------+-----------+----------+-------+
1 tuple
sql>CREATE TABLE test (t int DEFAULT NEXT VALUE FOR "my_test_seq", v char);
operation successful
sql>select * from sequences;
+------+-----------+-------------+-------+----------+----------+-----------+----------+-------+
| id | schema_id | name | start | minvalue | maxvalue | increment | cacheinc | cycle |
+======+===========+=============+=======+==========+==========+===========+==========+=======+
| 8630 | 2000 | my_test_seq | 2 | 0 | 0 | 1 | 1 | false |
+------+-----------+-------------+-------+----------+----------+-----------+----------+-------+
1 tuple
sql>drop table test;
operation successful
sql>select * from sequences;
+------+-----------+-------------+-------+----------+----------+-----------+----------+-------+
| id | schema_id | name | start | minvalue | maxvalue | increment | cacheinc | cycle |
+======+===========+=============+=======+==========+==========+===========+==========+=======+
| 8630 | 2000 | my_test_seq | 2 | 0 | 0 | 1 | 1 | false |
+------+-----------+-------------+-------+----------+----------+-----------+----------+-------+
1 tuple

Comment 26531

Date: 2018-06-29 14:36:32 +0200
From: MonetDB Mercurial Repository <>

Changeset bc6eeffbd123 made by Ying Zhang y.zhang@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Added tests for Bug #6618

Comment 26544

Date: 2018-07-11 16:47:02 +0200
From: MonetDB Mercurial Repository <>

Changeset 532e145f1c85 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

add dependencies on default functions (including sequences), solves
bug #6618