Date: 2017-10-19 13:55:52 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.27.5 (Jul2017-SP1)
CC: @njnes

Last updated: 2018-03-29 15:39:02 +0200

Comment 25765

Date: 2017-10-19 13:55:52 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:56.0) Gecko/20100101 Firefox/56.0
Build Identifier:

The SQL command: DROP SCHEMA <schema_name> [ CASCADE | RESTRICT ]
allows optional keywords CASCADE or RESTRICT.
When specifying RESTRICT it is accepted but NOT enforced, so producing unwanted results: the schema including all its related objects are dropped cascading!

Note that the current default behaviour when not specified is CASCADE!
When the RESTRICT behaviour is implemented and enforced it could also become the new default behavior, as is the case with DROP TABLE and DROP VIEW, to prevent accidental dropping of schema objects.

Reproducible: Always

Steps to Reproduce:

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 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;

Actual Results:

bash-4.4$ mclient
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.27.8 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE SCHEMA xyz;
operation successful (9.965ms)
sql>CREATE TABLE xyz.t1 (col1 int Primary Key);
operation successful (7.958ms)
sql>CREATE VIEW xyz.v1 as SELECT col1 FROM xyz.t1 WHERE col1 > 0 ORDER BY col1;
operation successful (4.855ms)
sql>INSERT INTO xyz.t1 VALUES (0), (2), (1);
3 affected rows (2.986ms)
sql>
sql>SELECT * FROM xyz.t1;
+------+
| col1 |
+======+
| 0 |
| 2 |
| 1 |
+------+
3 tuples (2.694ms)
sql>SELECT * FROM xyz.v1;
+------+
| col1 |
+======+
| 1 |
| 2 |
+------+
2 tuples (2.962ms)
sql>
sql>DROP SCHEMA xyz RESTRICT;
operation successful (9.146ms)
sql>-- this should return an error as there are objects depending on the schema
sql>SELECT * FROM xyz.t1;
SELECT: no such schema 'xyz'
sql>SELECT * FROM xyz.v1;
SELECT: no such schema 'xyz'
sql>
sql>DROP SCHEMA xyz CASCADE;
DROP SCHEMA: name xyz does not exist
sql>-- this should return success. Also all depending objects should be dropped
sql>SELECT * FROM xyz.t1;
SELECT: no such schema 'xyz'
sql>SELECT * FROM xyz.v1;
SELECT: no such schema 'xyz'
sql>

Expected Results:

The DROP SCHEMA xyz RESTRICT;
command should returns an error indicating that the schema could not be dropped as there are dependent objects.

On https://www.monetdb.org/Documentation/SQLreference/Schema
it includes the sentence:
The drop_action option is supported for compatibility with the SQL standard, however it is not implemented yet.
Once this is implemented, the documentation should be updated also.

Comment 25770

Date: 2017-10-19 20:19:55 +0200
From: @njnes

implemented (question is if the change of default is allowed in the release branch).

Comment 25776

Date: 2017-10-21 11:32:04 +0200
From: MonetDB Mercurial Repository <>

Changeset 901a54cbadd9 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=901a54cbadd9

Changeset description:

Fixes for bug #6440 (ie properly use semi/anti join for in)

Implement drop schema schema_name restrict (see bug #6438)
We keep the current default (cascade) in the release branch and switch
to the SQL correct default in the default branch.

Comment 25860

Date: 2017-11-09 18:15:41 +0100
From: MonetDB Mercurial Repository <>

Changeset 5469bd8b51d6 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

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

Comment 25862

Date: 2017-11-09 18:30:56 +0100
From: Martin van Dinther <<martin.van.dinther>>

This fix is implemented now in the default branch.
It will be included in the next feature release.

Comment 26298

Date: 2018-03-29 15:39:02 +0200
From: @sjoerdmullender

The Mar2018 version has been released.