Date: 2015-07-15 16:07:32 +0200
From: @yzchang
To: SQL devs <>
Version: 11.19.15 (Oct2014-SP4)
CC: @njnes, @yzchang

Last updated: 2015-08-28 13:42:58 +0200

Comment 21002

Date: 2015-07-15 16:07:32 +0200
From: @yzchang

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10; rv:39.0) Gecko/20100101 Firefox/39.0
Build Identifier:

[This also happens in Jul2015]

In the following mclient session, although it reports "CREATE VIEW tst.v1..." succeeded (note the schema 'tst' which is different than the current schema 'sys'), SELECT from tst.v1 says "no such table". Also, the view is not found in the SQL catalogue.

sql>create table t1 (i int);
operation successful (1.891ms)
sql>create schema tst;
operation successful (1.226ms)
sql>create view tst.v1 (i) as select * from t1;
operation successful (1.142ms)
sql>select * from tst.v1;
SELECT: no such table 'v1'
sql>select * from _tables where name like '%v1%';
+----+------+-----------+-------+------+--------+---------------+--------+
| id | name | schema_id | query | type | system | commit_action | access |
+====+======+===========+=======+======+========+===============+========+
+----+------+-----------+-------+------+--------+---------------+--------+
0 tuples (3.210ms)

It doesn't helpt to switch to the other schema 'tst':
sql>set schema tst;
auto commit mode: on
sql>select * from v1;
SELECT: no such table 'v1'
sql>select * from tst.v1;
SELECT: no such table 'v1'

However, if one first switches to the other schema 'tst', and create the view. Then the view is visible in both 'tst' and 'sys'. Also, tst.v1 is added to the SQL catalogue:

sql>set schema tst;
auto commit mode: on
sql>create view v1 (i) as select * from sys.t1;
operation successful (2.324ms)
sql>select * from v1;
+---+
| i |
+===+
+---+
0 tuples (3.655ms)
sql>set schema sys;
auto commit mode: on
sql>select * from tst.v1;
+---+
| i |
+===+
+---+
0 tuples (2.598ms)
sql>select * from _tables where name like '%v1%';
+------+------+-----------+---------------------------------------------+------+--------+---------------+--------+
| id | name | schema_id | query | type | system | commit_action | access |
+======+======+===========+=============================================+======+========+===============+========+
| 7377 | v1 | 7371 | create view v1 (i) as select * from sys.t1; | 1 | false | 0 | 0 |
+------+------+-----------+---------------------------------------------+------+--------+---------------+--------+
1 tuple (5.581ms)

Reproducible: Always

Steps to Reproduce:

1. Run the following queries to reproduce the problem:

create table t1 (i int);
create schema tst;
create view tst.v1 (i) as select * from t1;
select * from tst.v1;
select * from _tables where name like '%v1%';

Actual Results:

1st select query: error
2nd select query: empty results

Expected Results:

sql>select * from tst.v1;
+---+
| i |
+===+
+---+
0 tuples (2.598ms)
sql>select * from _tables where name like '%v1%';
+------+------+-----------+---------------------------------------------+------+--------+---------------+--------+
| id | name | schema_id | query | type | system | commit_action | access |
+======+======+===========+=============================================+======+========+===============+========+
| 7377 | v1 | 7371 | create view v1 (i) as select * from sys.t1; | 1 | false | 0 | 0 |
+------+------+-----------+---------------------------------------------+------+--------+---------------+--------+
1 tuple (5.581ms)

Comment 21004

Date: 2015-07-15 16:26:33 +0200
From: @yzchang

This bug is related to Bug #3708, but is slightly different.

Bug #3708 is about creating a view V for schema2 under schema2, and then try to access schema2.V in schema1. This works now, as is also shown in my mclient session.

This bug is about creating a viw V for schema2 under schema1 (!), and then try to access schema2.V in schema1 will result in an error.
If it's not allowed to create views for a different schema than the current schema, then the CREATE VIEW statement should return an error.

Comment 21005

Date: 2015-07-15 16:38:12 +0200
From: MonetDB Mercurial Repository <>

Changeset 0e35d49441c9 made by Jennie Zhang y.zhang@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for Bug #3766

Comment 21009

Date: 2015-07-15 17:20:43 +0200
From: MonetDB Mercurial Repository <>

Changeset 0d892d399f1e made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

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

Changeset description:

Enable test for bug #3766.

Comment 21042

Date: 2015-07-22 15:23:45 +0200
From: @njnes

the create view should have failed, as the t1 table is never reachable from the tst schema. We now properly detect that, ie use the proper schema to execute the sub-(view)-query

Comment 21061

Date: 2015-07-24 14:41:38 +0200
From: @yzchang

Hai Niels,

When processing "create view tst.v1" do you switch current_schema to "tst" underwater? Because, now

create view tst.v1 (i) as select * from t1;

will return "SELECT: no such table 't1'", while

create view tst.v1 (i) as select * from sys.t1;

will succeed (as it should).

Is this expected behaviour? So even if I'm still in schema "sys", I have to explicitly identify t1 with sys.t1?

Jennie

Comment 21225

Date: 2015-08-28 13:42:58 +0200
From: @sjoerdmullender

Jul2015 has been released.