wrong scoping for cross-schema view references

[monetdb-team]

Date: 2015-04-19 15:10:20 +0200
From: sorear
To: SQL devs <>
Version: 11.19.7 (Oct2014-SP1)
CC: @njnes

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

Comment 20816

Date: 2015-04-19 15:10:20 +0200
From: sorear

Just noticed this bit of odd behavior:

sql>create schema s1;
operation successful (26.216ms)
sql>create schema s2;
operation successful (2.487ms)
sql>set current_schema='s1';
auto commit mode: on
sql>create table s3 (x int);
operation successful (5.756ms)
sql>create view s4 as select * from s3;
operation successful (2.070ms)
sql>select * from s1.s4;
+---+
| x |
+===+
+---+
0 tuples (3.550ms)
sql>set current_schema='s2';
auto commit mode: on
sql>select * from s1.s4;
SELECT: no such table 's3'

Apparently the view expansion is not taking into account the view's own schema and using the connection current schema; this can also be shown by creating another version of the table and using PLAN:

sql>create schema s1;
operation successful (2.167ms)
sql>create schema s2;
operation successful (1.642ms)
sql>create table s1.s3 (x int);
operation successful (2.331ms)
sql>create table s2.s3 (x int);
operation successful (2.316ms)
sql>set current_schema='s1';
auto commit mode: on
sql>create view s4 as select * from s3;
operation successful (2.749ms)
sql>plan select * from s1.s4;
+--------------------------------+
| rel |
+================================+
| project ( |
| | table(s1.s3) [ s3.x ] COUNT |
| ) [ s3.x as s4.x ] |
+--------------------------------+
3 tuples (0.398ms)
sql>set current_schema = 's2';
auto commit mode: on
sql>plan select * from s1.s4;
+--------------------------------+
| rel |
+================================+
| project ( |
| | table(s2.s3) [ s3.x ] COUNT |
| ) [ s3.x as s4.x ] |
+--------------------------------+
3 tuples (0.264ms)

I'd expect the plan to not depend on the current_schema. Note that this is not an issue for us in any way; just odd behavior noticed while testing something else.

Incidentally while trying to find duplicates of this I got a bizarre error from bugzilla (here reproduced with curl -v -o- https://www.monetdb.org/bugzilla/buglist.cgi?content=view%20schema)

GET /bugzilla/buglist.cgi?content=view%20schema HTTP/1.1
User-Agent: curl/7.19.7 (x86_64-redhat-linux-gnu) libcurl/7.19.7 NSS/3.13.1.0 zlib/1.2.3 libidn/1.18 libssh2/1.2.2
Host: www.monetdb.org
Accept: /

< HTTP/1.1 500 Internal Server Error
< Date: Sun, 19 Apr 2015 13:08:23 GMT
< Server: Apache/2.4.10 (Fedora) OpenSSL/1.0.1e-fips mod_fcgid/2.3.9 PHP/5.5.23 mod_perl/2.0.9-dev Perl/v5.18.4
< Strict-Transport-Security: max-age=15768000
< Connection: close
< Transfer-Encoding: chunked
< Content-Type: text/html; charset=UTF-8
<

Software error:

Invalid local time for date in time zone: Europe/Amsterdam
 

For help, please send mail to the webmaster (root@localhost), giving this error message and the time and date of the error.

Comment 20873

Date: 2015-05-20 21:02:26 +0200
From: @njnes

Fixed. We now check views schema

Comment 20874

Date: 2015-05-20 21:03:46 +0200
From: MonetDB Mercurial Repository <>

Changeset 87af5a5903a8 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=87af5a5903a8

Changeset description:

fixed bug in handling view's and schema's (fixed bug #3708)

Comment 21205

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

Jul2015 has been released.