Changeset: 5469bd8b51d6 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=5469bd8b51d6 Added Files: sql/test/BugTracker-2017/Tests/drop_schema_restrict.Bug-6438.sql sql/test/BugTracker-2017/Tests/drop_schema_restrict.Bug-6438.stable.err sql/test/BugTracker-2017/Tests/drop_schema_restrict.Bug-6438.stable.out Modified Files: sql/ChangeLog sql/server/sql_parser.y sql/test/BugTracker-2017/Tests/All Branch: default Log Message:
Implemented correct DROP SCHEMA my_schema RESTRICT behavior in default branch Also the default behavior of DROP SCHEMA my_schema is now RESTRICT instead of CASCADE. Added a note in the sql Changelog Added test This fixes Bug 6438 diffs (276 lines): diff --git a/sql/ChangeLog b/sql/ChangeLog --- a/sql/ChangeLog +++ b/sql/ChangeLog @@ -1,6 +1,12 @@ # ChangeLog file for sql # This file is updated with Maddlog +* Thu Nov 9 2017 Martin van Dinther <[email protected]> +- Implemented behavior for DROP SCHEMA my_schema RESTRICT command. + Previously the RESTRICT keyword was accepted but not obeyed. It would + always do a CASCADE operation. Also the default behavior of DROP SCHEMA + my_schema command is now changed into RESTRICT behavior (was CASCADE). + * Tue Nov 7 2017 Sjoerd Mullender <[email protected]> - The internal NULL representation of the REAL (FLOAT) and DOUBLE types was changed from the smallest representable finite value to NaN diff --git a/sql/server/sql_parser.y b/sql/server/sql_parser.y --- a/sql/server/sql_parser.y +++ b/sql/server/sql_parser.y @@ -848,7 +848,7 @@ schema: | drop SCHEMA if_exists qname drop_action { dlist *l = L(); append_list(l, $4); - append_int(l, 1 /*$5 use CASCADE in the release */); + append_int(l, $5); append_int(l, $3); $$ = _symbol_create_list( SQL_DROP_SCHEMA, l); } ; diff --git a/sql/test/BugTracker-2017/Tests/All b/sql/test/BugTracker-2017/Tests/All --- a/sql/test/BugTracker-2017/Tests/All +++ b/sql/test/BugTracker-2017/Tests/All @@ -97,6 +97,7 @@ sqlitelogictest-aggregation-distinct-coa sqlsmith.Bug-6432 sqlitelogictest-select-in.Bug-6433 sqlitelogictest-select-not-in.Bug-6435 +drop_schema_restrict.Bug-6438 sqlitelogictest-cast-decimal.Bug-6445 HAVE_LIBPY?table_returning_with.Bug-6444 insert_into_multiple_subqueries.Bug-6448 diff --git a/sql/test/BugTracker-2017/Tests/drop_schema_restrict.Bug-6438.sql b/sql/test/BugTracker-2017/Tests/drop_schema_restrict.Bug-6438.sql new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2017/Tests/drop_schema_restrict.Bug-6438.sql @@ -0,0 +1,50 @@ +CREATE SCHEMA xyz; +CREATE TABLE xyz.t1 (col1 int Primary Key); +CREATE VIEW xyz.v1 as SELECT col1 FROM xyz.t1 WHERE col1 > 0 ORDER BY col1; +INSERT INTO xyz.t1 VALUES (0), (2), (1); + +SELECT * FROM xyz.t1; +SELECT * FROM xyz.v1; + +DROP SCHEMA xyz RESTRICT; +-- this should return an error as there are objects (table, column, pkey, view) depending on the schema +SELECT * FROM xyz.t1; +SELECT * FROM xyz.v1; + +DROP SCHEMA xyz; +-- this should return an error as the default behavior should be RESTRICT and there are objects (table, column, pkey, view) depending on the schema +SELECT * FROM xyz.t1; +SELECT * FROM xyz.v1; + +DROP SCHEMA xyz CASCADE; +-- this should return success. Also all depending objects should be dropped +SELECT * FROM xyz.t1; +SELECT * FROM xyz.v1; + +DROP SCHEMA xyz CASCADE; +-- this should return an error as the schema should not exist anymore + +DROP SCHEMA IF EXISTS xyz CASCADE; +-- this should return success. + +CREATE SCHEMA xyz2; +CREATE TABLE xyz2.t1 (col1 int Primary Key); +CREATE VIEW xyz2.v1 as SELECT col1 FROM xyz2.t1 WHERE col1 > 0 ORDER BY col1; +INSERT INTO xyz2.t1 VALUES (0), (2), (1); + +DROP SCHEMA xyz2 RESTRICT; +-- this should return an error as there are objects (table, column, pkey, view) depending on the schema +SELECT * FROM xyz2.t1; +SELECT * FROM xyz2.v1; + +DROP TABLE xyz2.t1 CASCADE; +-- this should drop the table and the dependent view +SELECT * FROM xyz2.t1; +SELECT * FROM xyz2.v1; + +DROP SCHEMA xyz2 RESTRICT; +-- this should return success as there are no depending objects anymore + +DROP SCHEMA IF EXISTS xyz2 CASCADE; +-- this should return success. + diff --git a/sql/test/BugTracker-2017/Tests/drop_schema_restrict.Bug-6438.stable.err b/sql/test/BugTracker-2017/Tests/drop_schema_restrict.Bug-6438.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2017/Tests/drop_schema_restrict.Bug-6438.stable.err @@ -0,0 +1,66 @@ +stderr of test 'drop_schema_restrict.Bug-6438` in directory 'sql/test/BugTracker-2017` itself: + + +# 17:29:19 > +# 17:29:19 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=33045" "--set" "mapi_usock=/var/tmp/mtest-16183/.s.monetdb.33045" "--set" "monet_prompt=" "--forcemito" "--dbpath=/export/scratch1/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2017" +# 17:29:19 > + +# builtin opt gdk_dbpath = /export/scratch1/dinther/INSTALL/var/monetdb5/dbfarm/demo +# builtin opt gdk_debug = 0 +# builtin opt gdk_vmtrim = no +# 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 = 33045 +# cmdline opt mapi_usock = /var/tmp/mtest-16183/.s.monetdb.33045 +# cmdline opt monet_prompt = +# cmdline opt gdk_dbpath = /export/scratch1/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2017 +# cmdline opt gdk_debug = 536870922 + +# 17:29:20 > +# 17:29:20 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-16183" "--port=33045" +# 17:29:20 > + +MAPI = (monetdb) /var/tmp/mtest-30606/.s.monetdb.38993 +QUERY = DROP SCHEMA xyz RESTRICT; +ERROR = !DROP SCHEMA: unable to drop schema 'xyz' (there are database objects which depend on it) +CODE = 2BM37 +MAPI = (monetdb) /var/tmp/mtest-30606/.s.monetdb.38993 +QUERY = DROP SCHEMA xyz; +ERROR = !DROP SCHEMA: unable to drop schema 'xyz' (there are database objects which depend on it) +CODE = 2BM37 +MAPI = (monetdb) /var/tmp/mtest-30606/.s.monetdb.38993 +QUERY = SELECT * FROM xyz.t1; +ERROR = !SELECT: no such schema 'xyz' +CODE = 3F000 +MAPI = (monetdb) /var/tmp/mtest-16183/.s.monetdb.33045 +QUERY = SELECT * FROM xyz.v1; +ERROR = !SELECT: no such schema 'xyz' +CODE = 3F000 +MAPI = (monetdb) /var/tmp/mtest-16183/.s.monetdb.33045 +QUERY = DROP SCHEMA xyz CASCADE; +ERROR = !DROP SCHEMA: name xyz does not exist +CODE = 3F000 +MAPI = (monetdb) /var/tmp/mtest-10026/.s.monetdb.37719 +QUERY = DROP SCHEMA xyz2 RESTRICT; +ERROR = !DROP SCHEMA: unable to drop schema 'xyz2' (there are database objects which depend on it) +CODE = 2BM37 +MAPI = (monetdb) /var/tmp/mtest-10026/.s.monetdb.37719 +QUERY = SELECT * FROM xyz2.t1; +ERROR = !SELECT: no such table 't1' +CODE = 42S02 +MAPI = (monetdb) /var/tmp/mtest-10026/.s.monetdb.37719 +QUERY = SELECT * FROM xyz2.v1; +ERROR = !SELECT: no such table 'v1' +CODE = 42S02 + +# 17:29:20 > +# 17:29:20 > "Done." +# 17:29:20 > + diff --git a/sql/test/BugTracker-2017/Tests/drop_schema_restrict.Bug-6438.stable.out b/sql/test/BugTracker-2017/Tests/drop_schema_restrict.Bug-6438.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2017/Tests/drop_schema_restrict.Bug-6438.stable.out @@ -0,0 +1,106 @@ +stdout of test 'drop_schema_restrict.Bug-6438` in directory 'sql/test/BugTracker-2017` itself: + + +# 17:29:19 > +# 17:29:19 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=33045" "--set" "mapi_usock=/var/tmp/mtest-16183/.s.monetdb.33045" "--set" "monet_prompt=" "--forcemito" "--dbpath=/export/scratch1/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2017" +# 17:29:19 > + +# MonetDB 5 server v11.28.0 +# This is an unreleased version +# Serving database 'mTests_sql_test_BugTracker-2017', using 4 threads +# Compiled for x86_64-unknown-linux-gnu/64bit with 128bit integers +# Found 31.312 GiB available main-memory. +# Copyright (c) 1993-July 2008 CWI. +# Copyright (c) August 2008-2017 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:33045/ +# Listening for UNIX domain connection requests on mapi:monetdb:///var/tmp/mtest-16183/.s.monetdb.33045 +# MonetDB/GIS module loaded +# MonetDB/SQL module loaded + +Ready. + +# 17:29:20 > +# 17:29:20 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-16183" "--port=33045" +# 17:29:20 > + +#CREATE SCHEMA xyz; +#CREATE TABLE xyz.t1 (col1 int Primary Key); +#CREATE VIEW xyz.v1 as SELECT col1 FROM xyz.t1 WHERE col1 > 0 ORDER BY col1; +#INSERT INTO xyz.t1 VALUES (0), (2), (1); +[ 3 ] +#SELECT * FROM xyz.t1; +% xyz.t1 # table_name +% col1 # name +% int # type +% 1 # length +[ 0 ] +[ 2 ] +[ 1 ] +#SELECT * FROM xyz.v1; +% xyz.v1 # table_name +% col1 # name +% int # type +% 1 # length +[ 1 ] +[ 2 ] +#SELECT * FROM xyz.t1; +% xyz.t1 # table_name +% col1 # name +% int # type +% 1 # length +[ 0 ] +[ 2 ] +[ 1 ] +#SELECT * FROM xyz.v1; +% xyz.v1 # table_name +% col1 # name +% int # type +% 1 # length +[ 1 ] +[ 2 ] +#SELECT * FROM xyz.t1; +% xyz.t1 # table_name +% col1 # name +% int # type +% 1 # length +[ 0 ] +[ 2 ] +[ 1 ] +#SELECT * FROM xyz.v1; +% xyz.v1 # table_name +% col1 # name +% int # type +% 1 # length +[ 1 ] +[ 2 ] +#DROP SCHEMA xyz CASCADE; +#DROP SCHEMA IF EXISTS xyz CASCADE; +#CREATE SCHEMA xyz2; +#CREATE TABLE xyz2.t1 (col1 int Primary Key); +#CREATE VIEW xyz2.v1 as SELECT col1 FROM xyz2.t1 WHERE col1 > 0 ORDER BY col1; +#INSERT INTO xyz2.t1 VALUES (0), (2), (1); +[ 3 ] +#SELECT * FROM xyz2.t1; +% xyz2.t1 # table_name +% col1 # name +% int # type +% 1 # length +[ 0 ] +[ 2 ] +[ 1 ] +#SELECT * FROM xyz2.v1; +% xyz2.v1 # table_name +% col1 # name +% int # type +% 1 # length +[ 1 ] +[ 2 ] +#DROP TABLE xyz2.t1 CASCADE; +#DROP SCHEMA xyz2 RESTRICT; +#DROP SCHEMA IF EXISTS xyz2 CASCADE; + +# 17:29:20 > +# 17:29:20 > "Done." +# 17:29:20 > + _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
