Changeset: 30ab14e79058 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=30ab14e79058
Added Files:
sql/test/BugTracker-2019/Tests/alter_table_set_schema.Bug-6701.sql
sql/test/BugTracker-2019/Tests/alter_table_set_schema.Bug-6701.stable.err
sql/test/BugTracker-2019/Tests/alter_table_set_schema.Bug-6701.stable.out
Modified Files:
sql/test/BugTracker-2013/Tests/empty-strings.Bug-3261.SQL.py
sql/test/BugTracker-2016/Tests/trigger_bulk.Bug-4045.sql
sql/test/BugTracker-2016/Tests/trigger_bulk.Bug-4045.stable.out
sql/test/BugTracker-2018/Tests/dependency_column_on_sequence.Bug-6618.SQL.py
sql/test/BugTracker-2019/Tests/All
sql/test/Users/Tests/grantAndRevokeUserLoggedIN.Bug-3476.SQL.py
sql/test/pg_regress/Tests/without_oid.stable.out.32bit
sql/test/remote/Tests/ssbm.SQL.py
sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.sql
sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.stable.out
testing/Mfilter.py
testing/Mtest.py.in
Branch: default
Log Message:
Merge with Apr2019 branch.
diffs (truncated from 566 to 300 lines):
diff --git a/sql/test/BugTracker-2013/Tests/empty-strings.Bug-3261.SQL.py
b/sql/test/BugTracker-2013/Tests/empty-strings.Bug-3261.SQL.py
--- a/sql/test/BugTracker-2013/Tests/empty-strings.Bug-3261.SQL.py
+++ b/sql/test/BugTracker-2013/Tests/empty-strings.Bug-3261.SQL.py
@@ -13,6 +13,10 @@ q = []
q.append(("create table bug3261 (probeid int, markername varchar(64));\n"
"copy %d records into bug3261 from stdin using delimiters "
"'\\t','\\n','' null as 'null';\n") % (1455 * 3916))
+try:
+ xrange
+except NameError:
+ xrange = range # Python 3
for i in xrange(1,1456):
v = 'rmm%d' % i
for j in xrange(3916):
diff --git a/sql/test/BugTracker-2016/Tests/trigger_bulk.Bug-4045.sql
b/sql/test/BugTracker-2016/Tests/trigger_bulk.Bug-4045.sql
--- a/sql/test/BugTracker-2016/Tests/trigger_bulk.Bug-4045.sql
+++ b/sql/test/BugTracker-2016/Tests/trigger_bulk.Bug-4045.sql
@@ -3,6 +3,7 @@ CREATE TABLE t111 (id INT, name VARCHAR(
INSERT INTO t111 VALUES(10, 'monetdb');
INSERT INTO t111 VALUES(20, 'monet');
+-- trigger which triggers itself recursively until reaching max nesting depth
or out of stack space
CREATE TRIGGER
test5
BEFORE INSERT ON t111
@@ -11,9 +12,13 @@ FOR EACH ROW
INSERT INTO t111 SELECT * FROM t111;
-SELECT * FROM t111;
+SELECT * FROM t111;
INSERT INTO t111 VALUES(30,'single');
SELECT * FROM t111;
DROP TABLE t111;
+-- fails with: unable to drop table t111 (there are database objects which
depend on it)
+
+-- cleanup table and dependent trigger
+DROP TABLE t111 CASCADE;
diff --git a/sql/test/BugTracker-2016/Tests/trigger_bulk.Bug-4045.stable.out
b/sql/test/BugTracker-2016/Tests/trigger_bulk.Bug-4045.stable.out
--- a/sql/test/BugTracker-2016/Tests/trigger_bulk.Bug-4045.stable.out
+++ b/sql/test/BugTracker-2016/Tests/trigger_bulk.Bug-4045.stable.out
@@ -90,6 +90,7 @@ stdout of test 'trigger_bulk.Bug-4045` i
% 2, 7 # length
[ 10, "monetdb" ]
[ 20, "monet" ]
+#DROP TABLE t111 CASCADE;
# 09:09:27 >
# 09:09:27 > "Done."
diff --git
a/sql/test/BugTracker-2018/Tests/dependency_column_on_sequence.Bug-6618.SQL.py
b/sql/test/BugTracker-2018/Tests/dependency_column_on_sequence.Bug-6618.SQL.py
---
a/sql/test/BugTracker-2018/Tests/dependency_column_on_sequence.Bug-6618.SQL.py
+++
b/sql/test/BugTracker-2018/Tests/dependency_column_on_sequence.Bug-6618.SQL.py
@@ -46,7 +46,7 @@ seqname = res[0][0]
try:
run(c1, 'drop sequence ' + seqname, False)
run(c1, 'insert into t(val) values (30), (40)')
-except Exception, e:
+except Exception as e:
print(e, file=sys.stderr)
print(query(c1, 'select * from t'))
run(c1, 'drop table t')
@@ -59,7 +59,7 @@ run(c1, 'insert into t2(val) values (10)
try:
run(c1, 'drop sequence myseq')
run(c1, 'insert into t2(val) values (30), (40)')
-except Exception, e:
+except Exception as e:
print(e, file=sys.stderr)
print(query(c1, 'select * from t2'))
run(c1, 'drop table t2')
diff --git a/sql/test/BugTracker-2019/Tests/All
b/sql/test/BugTracker-2019/Tests/All
--- a/sql/test/BugTracker-2019/Tests/All
+++ b/sql/test/BugTracker-2019/Tests/All
@@ -8,3 +8,4 @@ subselect-count.Bug-6686
subselect.Bug-6688
timestamptransformation.Bug-6695
duplicates-not-eliminated-long-CASE-stmt.Bug-6697
+alter_table_set_schema.Bug-6701
diff --git a/sql/test/BugTracker-2019/Tests/alter_table_set_schema.Bug-6701.sql
b/sql/test/BugTracker-2019/Tests/alter_table_set_schema.Bug-6701.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2019/Tests/alter_table_set_schema.Bug-6701.sql
@@ -0,0 +1,78 @@
+-- First check that no invalid FK references exist in the db. All next queries
should return zero rows:
+SELECT * FROM sys.columns WHERE table_id NOT IN (SELECT id FROM sys.tables);
+SELECT * FROM sys._columns WHERE table_id NOT IN (SELECT id FROM sys._tables);
+SELECT * FROM sys.keys WHERE table_id NOT IN (SELECT id FROM sys.tables);
+SELECT * FROM sys.objects WHERE id NOT IN (SELECT id FROM sys.ids);
+SELECT * FROM sys.idxs WHERE table_id NOT IN (SELECT id FROM sys.tables);
+SELECT * FROM sys.dependencies WHERE id NOT IN (SELECT id FROM sys.ids);
+SELECT * FROM sys.dependencies WHERE depend_id NOT IN (SELECT id FROM sys.ids);
+
+-- create a table with a serial column (which implicitly createa a primary key
constraint and index and a sequence)
+create table sys.test2 (col1 serial);
+select count(*) as count_rows from tables where name = 'test2';
+-- 1 row: test2 (id = 9046)
+select count(*) as count_rows from keys where table_id in (select id from
tables where name = 'test2');
+-- 1 row: test2_col1_pkey
+select count(*) as count_rows from objects where id in (select id from keys
where table_id in (select id from tables where name = 'test2'));
+-- 1 row: col1
+
+ALTER TABLE sys.test2 SET SCHEMA profiler;
+select count(*) as count_rows from tables where name = 'test2';
+-- 1 row: test2. NOTE that the id has changed from 9046 into 9048
+select count(*) as count_rows from keys where table_id in (select id from
tables where name = 'test2');
+-- 1 row: test2_col1_pkey
+select count(*) as count_rows from objects where id in (select id from keys
where table_id in (select id from tables where name = 'test2'));
+-- 2 rows! which are also identical: col1
+
+ALTER TABLE profiler.test2 SET SCHEMA json;
+select count(*) as count_rows from tables where name = 'test2';
+-- 1 row: test2. NOTE that the id has changed from 9048 into 9050
+select count(*) as count_rows from keys where table_id in (select id from
tables where name = 'test2');
+-- 1 row: test2_col1_pkey
+select count(*) as count_rows from objects where id in (select id from keys
where table_id in (select id from tables where name = 'test2'));
+-- 3 rows! which are also identical: col1
+
+ALTER TABLE json.test2 SET SCHEMA sys;
+select count(*) as count_rows from tables where name = 'test2';
+-- 1 row: test2. NOTE that the id has changed from 9050 into 9052
+select count(*) as count_rows from keys where table_id in (select id from
tables where name = 'test2');
+-- 1 row: test2_col1_pkey
+select count(*) as count_rows from objects where id in (select id from keys
where table_id in (select id from tables where name = 'test2'));
+-- 4 rows! which are also identical: col1
+
+-- Now repeat the invalid FK references exist in the db. All next queries
should return zero rows:
+SELECT * FROM sys.columns WHERE table_id NOT IN (SELECT id FROM sys.tables);
+-- lists 3 invalid rows
+SELECT * FROM sys._columns WHERE table_id NOT IN (SELECT id FROM sys._tables);
+-- lists 3 invalid rows
+SELECT * FROM sys.keys WHERE table_id NOT IN (SELECT id FROM sys.tables);
+-- lists 3 invalid rows
+SELECT * FROM sys.objects WHERE id NOT IN (SELECT id FROM sys.ids);
+-- lists 3 invalid rows
+SELECT * FROM sys.idxs WHERE table_id NOT IN (SELECT id FROM sys.tables);
+-- lists 3 invalid rows
+SELECT * FROM sys.dependencies WHERE id NOT IN (SELECT id FROM sys.ids);
+SELECT * FROM sys.dependencies WHERE depend_id NOT IN (SELECT id FROM sys.ids);
+
+drop table sys.test2;
+
+-- Now repeat the invalid FK references exist in the db. All next queries
should return zero rows:
+SELECT * FROM sys.columns WHERE table_id NOT IN (SELECT id FROM sys.tables);
+-- lists 4 invalid rows
+SELECT * FROM sys._columns WHERE table_id NOT IN (SELECT id FROM sys._tables);
+-- lists 4 invalid rows
+SELECT * FROM sys.keys WHERE table_id NOT IN (SELECT id FROM sys.tables);
+-- lists 4 invalid rows
+SELECT * FROM sys.objects WHERE id NOT IN (SELECT id FROM sys.ids);
+-- lists 4 invalid rows
+SELECT * FROM sys.idxs WHERE table_id NOT IN (SELECT id FROM sys.tables);
+-- lists 4 invalid rows
+SELECT * FROM sys.dependencies WHERE id NOT IN (SELECT id FROM sys.ids);
+SELECT * FROM sys.dependencies WHERE depend_id NOT IN (SELECT id FROM sys.ids);
+
+-- now try to recreate the dropped table
+create table sys.test2 (col1 serial);
+-- it produces Error: CONSTRAINT PRIMARY KEY: key test2_col1_pkey already
exists SQLState: 42000
+
+drop table if exists sys.test2;
+
diff --git
a/sql/test/BugTracker-2019/Tests/alter_table_set_schema.Bug-6701.stable.err
b/sql/test/BugTracker-2019/Tests/alter_table_set_schema.Bug-6701.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2019/Tests/alter_table_set_schema.Bug-6701.stable.err
@@ -0,0 +1,34 @@
+stderr of test 'alter_table_set_schema.Bug-6701` in directory
'sql/test/BugTracker-2019` itself:
+
+
+# 19:03:56 >
+# 19:03:56 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=38092" "--set"
"mapi_usock=/var/tmp/mtest-21054/.s.monetdb.38092" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/export/scratch1/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2019"
"--set" "embedded_r=yes" "--set" "embedded_py=2" "--set" "embedded_c=true"
+# 19:03:56 >
+
+# builtin opt gdk_dbpath =
/export/scratch1/dinther/INSTALL/var/monetdb5/dbfarm/demo
+# builtin opt monet_prompt = >
+# builtin opt monet_daemon = no
+# builtin opt mapi_port = 50000
+# builtin opt mapi_open = false
+# builtin opt mapi_autosense = false
+# builtin opt sql_optimizer = default_pipe
+# builtin opt sql_debug = 0
+# cmdline opt gdk_nr_threads = 0
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 38092
+# cmdline opt mapi_usock = /var/tmp/mtest-21054/.s.monetdb.38092
+# cmdline opt monet_prompt =
+# cmdline opt gdk_dbpath =
/export/scratch1/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2019
+# cmdline opt embedded_r = yes
+# cmdline opt embedded_py = 2
+# cmdline opt embedded_c = true
+
+# 19:09:41 >
+# 19:09:41 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-21731" "--port=39182"
+# 19:09:41 >
+
+
+# 19:25:10 >
+# 19:25:10 > "Done."
+# 19:25:10 >
+
diff --git
a/sql/test/BugTracker-2019/Tests/alter_table_set_schema.Bug-6701.stable.out
b/sql/test/BugTracker-2019/Tests/alter_table_set_schema.Bug-6701.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2019/Tests/alter_table_set_schema.Bug-6701.stable.out
@@ -0,0 +1,217 @@
+stdout of test 'alter_table_set_schema.Bug-6701` in directory
'sql/test/BugTracker-2019` itself:
+
+
+# 19:03:56 >
+# 19:03:56 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=38092" "--set"
"mapi_usock=/var/tmp/mtest-21054/.s.monetdb.38092" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/export/scratch1/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2019"
"--set" "embedded_r=yes" "--set" "embedded_py=2" "--set" "embedded_c=true"
+# 19:03:56 >
+
+# MonetDB 5 server v11.33.2 (hg id: fda2e61de6c7)
+# This is an unreleased version
+# Serving database 'mTests_sql_test_BugTracker-2019', using 4 threads
+# Compiled for x86_64-unknown-linux-gnu/64bit with 128bit integers
+# Found 31.305 GiB available main-memory.
+# Copyright (c) 1993 - July 2008 CWI.
+# Copyright (c) August 2008 - 2019 MonetDB B.V., all rights reserved
+# Visit https://www.monetdb.org/ for further information
+# Listening for connection requests on mapi:monetdb://catskill.da.cwi.nl:38092/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-21054/.s.monetdb.38092
+# MonetDB/GIS module loaded
+# MonetDB/SQL module loaded
+# MonetDB/Python2 module loaded
+# MonetDB/R module loaded
+
+Ready.
+
+# 19:03:57 >
+# 19:03:57 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-21054" "--port=38092"
+# 19:03:57 >
+
+#SELECT * FROM sys.columns WHERE table_id NOT IN (SELECT id FROM sys.tables);
+% .columns, .columns, .columns, .columns, .columns,
.columns, .columns, .columns, .columns, .columns #
table_name
+% id, name, type, type_digits, type_scale, table_id,
default, null, number, storage # name
+% int, varchar, varchar, int, int, int, varchar,
boolean, int, varchar # type
+% 1, 0, 0, 1, 1, 1, 0, 5, 1, 0 #
length
+#SELECT * FROM sys._columns WHERE table_id NOT IN (SELECT id FROM sys._tables);
+% sys._columns, sys._columns, sys._columns, sys._columns,
sys._columns, sys._columns, sys._columns, sys._columns, sys._columns,
sys._columns # table_name
+% id, name, type, type_digits, type_scale, table_id,
default, null, number, storage # name
+% int, varchar, varchar, int, int, int, varchar,
boolean, int, varchar # type
+% 1, 0, 0, 1, 1, 1, 0, 5, 1, 0 #
length
+#SELECT * FROM sys.keys WHERE table_id NOT IN (SELECT id FROM sys.tables);
+% sys.keys, sys.keys, sys.keys, sys.keys, sys.keys,
sys.keys # table_name
+% id, table_id, type, name, rkey, action # name
+% int, int, int, varchar, int, int # type
+% 1, 1, 1, 0, 1, 1 # length
+#SELECT * FROM sys.objects WHERE id NOT IN (SELECT id FROM sys.ids);
+% sys.objects, sys.objects, sys.objects # table_name
+% id, name, nr # name
+% int, varchar, int # type
+% 1, 0, 1 # length
+#SELECT * FROM sys.idxs WHERE table_id NOT IN (SELECT id FROM sys.tables);
+% sys.idxs, sys.idxs, sys.idxs, sys.idxs # table_name
+% id, table_id, type, name # name
+% int, int, int, varchar # type
+% 1, 1, 1, 0 # length
+#SELECT * FROM sys.dependencies WHERE id NOT IN (SELECT id FROM sys.ids);
+% sys.dependencies, sys.dependencies, sys.dependencies # table_name
+% id, depend_id, depend_type # name
+% int, int, smallint # type
+% 1, 1, 1 # length
+#SELECT * FROM sys.dependencies WHERE depend_id NOT IN (SELECT id FROM
sys.ids);
+% sys.dependencies, sys.dependencies, sys.dependencies # table_name
+% id, depend_id, depend_type # name
+% int, int, smallint # type
+% 1, 1, 1 # length
+#create table sys.test2 (col1 serial);
+#select count(*) as count_rows from tables where name = 'test2';
+% .L42 # table_name
+% count_rows # name
+% bigint # type
+% 1 # length
+[ 1 ]
+#select count(*) as count_rows from keys where table_id in (select id from
tables where name = 'test2');
+% sys.L103 # table_name
+% count_rows # name
+% bigint # type
+% 1 # length
+[ 1 ]
+#select count(*) as count_rows from objects where id in (select id from keys
where table_id in (select id from tables where name = 'test2'));
+% sys.L205 # table_name
+% count_rows # name
+% bigint # type
+% 1 # length
+[ 1 ]
+#ALTER TABLE sys.test2 SET SCHEMA profiler;
+#select count(*) as count_rows from tables where name = 'test2';
+% .L42 # table_name
+% count_rows # name
+% bigint # type
+% 1 # length
+[ 1 ]
+#select count(*) as count_rows from keys where table_id in (select id from
tables where name = 'test2');
+% sys.L103 # table_name
+% count_rows # name
+% bigint # type
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
