SQL environment settings are updated even when the set statement fails

[monetdb-team]

Date: 2013-09-24 21:46:38 +0200
From: Tim H. <<monetdb.bug.reporter>>
To: SQL devs <>
Version: 11.15.15 (Feb2013-SP4)
CC: @njnes

Last updated: 2013-10-22 14:42:07 +0200

Comment 19176

Date: 2013-09-24 21:46:38 +0200
From: Tim H. <<monetdb.bug.reporter>>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/29.0.1547.66 Safari/537.36
Build Identifier:

The built-in SQL environment settings can be updated by a SET statement. For example, "set role hr" updates current_role to "hr". The issue is that current_role is updated even when the SET statement fails (e.g. when there is no role named "hr"). This bug affects all of the built-in session variables except "optimizer".

Reproducible: Always

Steps to Reproduce:

1. Run the following SQL statement while logged into a new database with the monetdb user:
   set role blah;
   select current_role;
   set schema xyz;
   select current_schema;

Actual Results:

sql>set role blah;
Role (blah) missing
sql>select current_role;
+--------------+
| single_value |
+==============+
| blah |
+--------------+
1 tuple (4.3s)
sql>set schema xyz;
Schema (xyz) missing
sql>select current_schema;
+--------------+
| single_value |
+==============+
| xyz |
+--------------+
1 tuple (1.389ms)

Expected Results:

sql>set role blah;
Role (blah) missing
sql>select current_role;
+--------------+
| single_value |
+==============+
| monetdb |
+--------------+
1 tuple (4.3s)
sql>set schema xyz;
Schema (xyz) missing
sql>select current_schema;
+--------------+
| single_value |
+==============+
| sys |
+--------------+
1 tuple (1.389ms)

It's not clear what the purpose of the following commands are:
set user='some_user';
set session authorization 'some_user';
Both update the current_user variable, even when no user with the given name exists. And even when the user does exist, having the updated current_user variable does not change the effective user for the session (controlled by user_id in the mvc struct).

Comment 19190

Date: 2013-09-27 09:54:03 +0200
From: @njnes

Added test (env_errors.Bug-3370.sql). The bug is fixed by only applying the changes after checking if the value is valid.

Comment 19194

Date: 2013-09-27 10:33:39 +0200
From: MonetDB Mercurial Repository <>

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

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=ca5728fc9b7f

Changeset description:

fixed bug #3370