Changeset: 23602fa6a4db for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=23602fa6a4db
Modified Files:
sql/test/Tests/seq-default.stable.out
sql/test/seq-default.sql
Branch: Aug2018
Log Message:
Extended sequence default test with usage of explicitly created sequence which
is used in two tables as default next value for seq_####
The sequence should not be dropped when the table is dropped, despite its name
being the same format as the system generated sequence names.
Now the insert on the second table - which still references the sequence -
causes a server crash.
This should not be allowed.
diffs (188 lines):
diff --git a/sql/test/Tests/seq-default.stable.out
b/sql/test/Tests/seq-default.stable.out
--- a/sql/test/Tests/seq-default.stable.out
+++ b/sql/test/Tests/seq-default.stable.out
@@ -15,19 +15,6 @@ stdout of test 'seq-default` in director
# MonetDB/SQL module v2.27.0 loaded
Ready.
-# falling back to direct mode
-# falling back to direct mode
-# falling back to direct mode
-# falling back to direct mode
-# falling back to direct mode
-# falling back to direct mode
-# falling back to direct mode
-# falling back to direct mode
-# falling back to direct mode
-#function user.main():void;
-# clients.quit();
-#end main;
-
# 12:37:18 >
# 12:37:18 > Mtimeout -timeout 60 MapiClient -lsql -u monetdb -P monetdb
--host=localhost --port=45579 < seq-default.sql
@@ -49,12 +36,28 @@ Ready.
[ 2005-09-23 12:34:26.736000, 4 ]
[ 2005-09-23 12:34:26.736000, 5 ]
[ 2005-09-23 12:34:26.736000, 6 ]
+TABLE sys.seqdeftest
+SEQUENCE sys.test_seq
+#drop table seqdeftest;
+#drop sequence test_seq;
+#create table seqdeftest (
+# d date,
+# id serial,
+# count int auto_increment,
+# bla int generated always as identity (
+# start with 100 increment by 2 no minvalue maxvalue 1000
+# cache 2 cycle)
+#);
+#insert into seqdeftest(d) values ('2005-10-01');
+[ 1 ]
+#insert into seqdeftest(d) values ('2005-10-01');
+[ 1 ]
+#insert into seqdeftest(d) values ('2005-10-01');
[ 1 ]
[ 1 ]
[ 1 ]
[ 1 ]
-[ 1 ]
-[ 1 ]
+#select * from seqdeftest;
% sys.seqdeftest, sys.seqdeftest, sys.seqdeftest, sys.seqdeftest #
table_name
% d, id, count, bla # name
% date, int, int, int # type
@@ -65,8 +68,68 @@ Ready.
[ 2005-10-01, 4, 4, 106 ]
[ 2005-10-01, 5, 5, 108 ]
[ 2005-10-01, 6, 6, 110 ]
+SEQUENCE sys.seq_8499
+SEQUENCE sys.seq_8501
+SEQUENCE sys.seq_8503
+TABLE sys.seqdeftest
+#drop table seqdeftest;
+#CREATE SEQUENCE seq_5700 AS INTEGER;
+#CREATE TABLE kvk (
+# id INTEGER NOT NULL DEFAULT next value for
seq_5700,
+# kvk BIGINT,
+# bedrijfsnaam VARCHAR(256),
+# CONSTRAINT kvk_id_pkey PRIMARY KEY (id)
+#);
+#insert into kvk(kvk, bedrijfsnaam) values (1, 'ones');
+[ 1 ]
+#insert into kvk(kvk, bedrijfsnaam) values (2, 'toos');
+[ 1 ]
+#select * from kvk;
+% sys.kvk, sys.kvk, sys.kvk # table_name
+% id, kvk, bedrijfsnaam # name
+% int, bigint, varchar # type
+% 1, 1, 4 # length
+[ 1, 1, "ones" ]
+[ 2, 2, "toos" ]
+#CREATE TABLE kvk_cp (
+# id INTEGER NOT NULL DEFAULT next value for
seq_5700,
+# kvk BIGINT,
+# bedrijfsnaam VARCHAR(256),
+# CONSTRAINT kvk_cp_id_pkey PRIMARY KEY (id)
+#);
+#insert into kvk_cp(kvk, bedrijfsnaam) values (3, 'dries');
+[ 1 ]
+#insert into kvk_cp(kvk, bedrijfsnaam) values (4, 'viers');
+[ 1 ]
+#select * from kvk_cp;
+% sys.kvk_cp, sys.kvk_cp, sys.kvk_cp # table_name
+% id, kvk, bedrijfsnaam # name
+% int, bigint, varchar # type
+% 1, 1, 5 # length
+[ 3, 3, "dries" ]
+[ 4, 4, "viers" ]
+TABLE sys.kvk
+TABLE sys.kvk_cp
+SEQUENCE sys.seq_5700
+#drop TABLE kvk;
+#insert into kvk_cp(kvk, bedrijfsnaam) values (5, 'vijfs');
+[ 1 ]
+#select * from kvk_cp;
+% sys.kvk_cp, sys.kvk_cp, sys.kvk_cp # table_name
+% id, kvk, bedrijfsnaam # name
+% int, bigint, varchar # type
+% 1, 1, 5 # length
+[ 3, 3, "dries" ]
+[ 4, 4, "viers" ]
+[ 5, 5, "vijfs" ]
+TABLE sys.kvk_cp
+SEQUENCE sys.seq_5700
+#drop TABLE kvk_cp;
+SEQUENCE sys.seq_5700
+#drop SEQUENCE seq_5700;
+#ROLLBACK;
-# 12:37:18 >
-# 12:37:18 > Done.
-# 12:37:18 >
+# 12:44:28 >
+# 12:44:28 > "Done."
+# 12:44:28 >
diff --git a/sql/test/seq-default.sql b/sql/test/seq-default.sql
--- a/sql/test/seq-default.sql
+++ b/sql/test/seq-default.sql
@@ -10,9 +10,12 @@ insert into seqdeftest(ts) values ('2005
insert into seqdeftest(ts) values ('2005-09-23 12:34:26.736');
select * from seqdeftest;
+\dts
+
drop table seqdeftest;
drop sequence test_seq;
+
create table seqdeftest (
d date,
id serial,
@@ -29,4 +32,45 @@ insert into seqdeftest(d) values ('2005-
insert into seqdeftest(d) values ('2005-10-01');
select * from seqdeftest;
+\dts
+
+drop table seqdeftest;
+
+
+-- create a user created sequence with name pattern 'seq_####' similar to the
system generated sequences as in the above table seqdeftest
+CREATE SEQUENCE seq_5700 AS INTEGER;
+CREATE TABLE kvk (
+ id INTEGER NOT NULL DEFAULT next value for
seq_5700,
+ kvk BIGINT,
+ bedrijfsnaam VARCHAR(256),
+ CONSTRAINT kvk_id_pkey PRIMARY KEY (id)
+);
+insert into kvk(kvk, bedrijfsnaam) values (1, 'ones');
+insert into kvk(kvk, bedrijfsnaam) values (2, 'toos');
+select * from kvk;
+
+-- use the same sequence also in another table
+CREATE TABLE kvk_cp (
+ id INTEGER NOT NULL DEFAULT next value for
seq_5700,
+ kvk BIGINT,
+ bedrijfsnaam VARCHAR(256),
+ CONSTRAINT kvk_cp_id_pkey PRIMARY KEY (id)
+);
+insert into kvk_cp(kvk, bedrijfsnaam) values (3, 'dries');
+insert into kvk_cp(kvk, bedrijfsnaam) values (4, 'viers');
+select * from kvk_cp;
+
+\dts
+
+-- when dropping the tables the user defined sequence should *not* be dropped
implicitly
+drop TABLE kvk;
+
+insert into kvk_cp(kvk, bedrijfsnaam) values (5, 'vijfs');
+select * from kvk_cp;
+drop TABLE kvk_cp;
+
+-- the user created sequence should not have been implicitly dropped at this
point
+\dts
+drop SEQUENCE seq_5700;
+
ROLLBACK;
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
