ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- -- this test shows the current supported schema functionality, which -- isn't much. Currently, we have no CREATE SCHEMA statement, though -- we do understand schema names in table names -- -- Catalog names are not supported, and result in syntax errors when used. -- create table myschem.t(c int); 0 rows inserted/updated/deleted ij> insert into t values (1); ERROR 42X05: Table/View 'T' does not exist. ij> insert into blah.t values (2); ERROR 42Y07: Schema 'BLAH' does not exist ij> insert into blah.blah.t values (3); ERROR 42X01: Syntax error: Encountered "." at line 1, column 22. ij> insert into blah.blah.blah.t values (3); ERROR 42X01: Syntax error: Encountered "." at line 1, column 22. ij> select "goofy name".t.c from "goofy name".t; ERROR 42Y07: Schema 'goofy name' does not exist ij> -- catalog name not supported: create table mycat.myschem.s(c int); ERROR 42X01: Syntax error: Encountered "." at line 2, column 27. ij> -- name too long: create table myworld.mycat.myschem.s(c int); ERROR 42X01: Syntax error: Encountered "." at line 2, column 27. ij> create table myschem.s(c int); 0 rows inserted/updated/deleted ij> insert into s values (1); ERROR 42X05: Table/View 'S' does not exist. ij> insert into honk.s values (2); ERROR 42Y07: Schema 'HONK' does not exist ij> insert into honk.blat.s values (3); ERROR 42X01: Syntax error: Encountered "." at line 1, column 22. ij> insert into loud.honk.blat.s values (4); ERROR 42X01: Syntax error: Encountered "." at line 1, column 22. ij> -- Catalog names in column expressions cause syntax errors. Rather than -- fix this, I am checking it in this way, considering that no client we -- know of uses catalogs. -- - Jeff -- -- select honk.blat.s.c from honk.blat.s; drop table xyzzy.t; ERROR 42Y07: Schema 'XYZZY' does not exist ij> -- catalog name not supported: drop table goodness.gosh.s; ERROR 42X01: Syntax error: Encountered "." at line 2, column 25. ij> -- finds s, schema name ignored: drop table gosh.s; ERROR 42Y07: Schema 'GOSH' does not exist ij> -- tests for qualified names in select, relative to method invocations create table mytab (i int); 0 rows inserted/updated/deleted ij> create table APP.mytab2 (i int); 0 rows inserted/updated/deleted ij> insert into mytab values 1,2,3; 3 rows inserted/updated/deleted ij> insert into APP.mytab2 values 1,2,3; 3 rows inserted/updated/deleted ij> -- plain and just table names match up fine select i, mytab.i from mytab; I |I ----------------------- 1 |1 2 |2 3 |3 ij> -- schema names on columns select APP.mytab2.i from APP.mytab2; I ----------- 1 2 3 ij> select APP.mytab2.i from mytab2; I ----------- 1 2 3 ij> select mytab2.i from APP.mytab2; I ----------- 1 2 3 ij> -- schema names correlation names: select m.i from APP.mytab2 m; I ----------- 1 2 3 ij> -- syntax errors on catalog names select nocatalogs.APP.mytab.i from mytab2; ERROR 42X04: Column 'NOCATALOGS.APP.MYTAB' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'NOCATALOGS.APP.MYTAB' is not a column in the target table. ij> drop table mytab; 0 rows inserted/updated/deleted ij> drop table APP.mytab2; 0 rows inserted/updated/deleted ij> ------------------------------------------------ -- -- Now, we'll try to create and drop some schemas -- ------------------------------------------------ create schema app; ERROR X0Y68: Schema 'APP' already exists. ij> create schema sys; ERROR 42939: An object cannot be created with the schema name 'SYS'. ij> -- negative drop test drop schema does_not_exist RESTRICT; ERROR 42Y07: Schema 'DOES_NOT_EXIST' does not exist ij> -- negative create test - should not be able to create existing system schemas; create schema app; ERROR X0Y68: Schema 'APP' already exists. ij> create schema APP; ERROR X0Y68: Schema 'APP' already exists. ij> create schema sys; ERROR 42939: An object cannot be created with the schema name 'SYS'. ij> create schema SYS; ERROR 42939: An object cannot be created with the schema name 'SYS'. ij> create schema sysibm; ERROR 42939: An object cannot be created with the schema name 'SYSIBM'. ij> create schema SYSIBM; ERROR 42939: An object cannot be created with the schema name 'SYSIBM'. ij> create schema syscat; ERROR 42939: An object cannot be created with the schema name 'SYSCAT'. ij> create schema SYSCAT; ERROR 42939: An object cannot be created with the schema name 'SYSCAT'. ij> create schema sysfun; ERROR 42939: An object cannot be created with the schema name 'SYSFUN'. ij> create schema SYSFUN; ERROR 42939: An object cannot be created with the schema name 'SYSFUN'. ij> create schema sysproc; ERROR 42939: An object cannot be created with the schema name 'SYSPROC'. ij> create schema SYSPROC; ERROR 42939: An object cannot be created with the schema name 'SYSPROC'. ij> create schema sysstat; ERROR 42939: An object cannot be created with the schema name 'SYSSTAT'. ij> create schema SYSSTAT; ERROR 42939: An object cannot be created with the schema name 'SYSSTAT'. ij> create schema syscs_diag; ERROR 42939: An object cannot be created with the schema name 'SYSCS_DIAG'. ij> create schema SYSCS_DIAG; ERROR 42939: An object cannot be created with the schema name 'SYSCS_DIAG'. ij> create schema syscs_util; ERROR 42939: An object cannot be created with the schema name 'SYSCS_UTIL'. ij> create schema SYSCS_UTIL; ERROR 42939: An object cannot be created with the schema name 'SYSCS_UTIL'. ij> create schema nullid; ERROR X0Y68: Schema 'NULLID' already exists. ij> create schema NULLID; ERROR X0Y68: Schema 'NULLID' already exists. ij> create schema sqlj; ERROR X0Y68: Schema 'SQLJ' already exists. ij> create schema SQLJ; ERROR X0Y68: Schema 'SQLJ' already exists. ij> -- negative create test - should not be able to objects in system schemas create table syscat.foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCAT' schema. ij> create table sysfun.foo2 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSFUN' schema. ij> create table sysproc.foo3 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSPROC' schema. ij> create table sysstat.foo4 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSSTAT' schema. ij> create table syscs_diag.foo6 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCS_DIAG' schema. ij> create table nullid.foo7 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'NULLID' schema. ij> create table sysibm.foo8 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSIBM' schema. ij> create table sqlj.foo8 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SQLJ' schema. ij> create table syscs_util.foo9 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCS_UTIL' schema. ij> create table SYSCAT.foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCAT' schema. ij> create table SYSFUN.foo2 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSFUN' schema. ij> create table SYSPROC.foo3 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSPROC' schema. ij> create table SYSSTAT.foo4 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSSTAT' schema. ij> create table SYSCS_DIAG.foo6 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCS_DIAG' schema. ij> create table SYSIBM.foo8 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSIBM' schema. ij> create table SQLJ.foo8 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SQLJ' schema. ij> create table SYSCS_UTIL.foo9 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCS_UTIL' schema. ij> -- negative drop test - should not be able to drop system schema's drop schema app RESTRICT; 0 rows inserted/updated/deleted ij> drop schema APP RESTRICT; ERROR 42Y07: Schema 'APP' does not exist ij> drop schema sys RESTRICT; ERROR 42Y67: Schema 'SYS' cannot be dropped. ij> drop schema SYS RESTRICT; ERROR 42Y67: Schema 'SYS' cannot be dropped. ij> drop schema sysibm RESTRICT; ERROR 42Y67: Schema 'SYSIBM' cannot be dropped. ij> drop schema SYSIBM RESTRICT; ERROR 42Y67: Schema 'SYSIBM' cannot be dropped. ij> drop schema syscat RESTRICT; ERROR 42Y67: Schema 'SYSCAT' cannot be dropped. ij> drop schema SYSCAT RESTRICT; ERROR 42Y67: Schema 'SYSCAT' cannot be dropped. ij> drop schema sysfun RESTRICT; ERROR 42Y67: Schema 'SYSFUN' cannot be dropped. ij> drop schema SYSFUN RESTRICT; ERROR 42Y67: Schema 'SYSFUN' cannot be dropped. ij> drop schema sysproc RESTRICT; ERROR 42Y67: Schema 'SYSPROC' cannot be dropped. ij> drop schema SYSPROC RESTRICT; ERROR 42Y67: Schema 'SYSPROC' cannot be dropped. ij> drop schema sysstat RESTRICT; ERROR 42Y67: Schema 'SYSSTAT' cannot be dropped. ij> drop schema SYSSTAT RESTRICT; ERROR 42Y67: Schema 'SYSSTAT' cannot be dropped. ij> drop schema syscs_diag RESTRICT; ERROR 42Y67: Schema 'SYSCS_DIAG' cannot be dropped. ij> drop schema SYSCS_DIAG RESTRICT; ERROR 42Y67: Schema 'SYSCS_DIAG' cannot be dropped. ij> drop schema syscs_util RESTRICT; ERROR 42Y67: Schema 'SYSCS_UTIL' cannot be dropped. ij> drop schema SYSCS_UTIL RESTRICT; ERROR 42Y67: Schema 'SYSCS_UTIL' cannot be dropped. ij> drop schema nullid RESTRICT; ERROR 42Y67: Schema 'NULLID' cannot be dropped. ij> drop schema NULLID RESTRICT; ERROR 42Y67: Schema 'NULLID' cannot be dropped. ij> drop schema sqlj RESTRICT; ERROR 42Y67: Schema 'SQLJ' cannot be dropped. ij> drop schema SQLJ RESTRICT; ERROR 42Y67: Schema 'SQLJ' cannot be dropped. ij> create schema app; 0 rows inserted/updated/deleted ij> set schema app; 0 rows inserted/updated/deleted ij> create table test (a int); 0 rows inserted/updated/deleted ij> -- negative create test - should not be able to objects in system schemas set schema syscat; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCAT' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSCAT' schema. ij> set schema sysfun; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSFUN' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSFUN' schema. ij> set schema sysproc; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSPROC' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSPROC' schema. ij> set schema sysstat; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSSTAT' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSSTAT' schema. ij> set schema sysstat; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSSTAT' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSSTAT' schema. ij> set schema syscs_diag; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCS_DIAG' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSCS_DIAG' schema. ij> set schema syscs_util; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCS_UTIL' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSCS_UTIL' schema. ij> set schema nullid; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'NULLID' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'NULLID' schema. ij> set schema sysibm; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSIBM' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSIBM' schema. ij> set schema sqlj; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SQLJ' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SQLJ' schema. ij> set schema SYSCAT; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCAT' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSCAT' schema. ij> set schema SYSFUN; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSFUN' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSFUN' schema. ij> set schema SYSPROC; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSPROC' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSPROC' schema. ij> set schema SYSSTAT; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSSTAT' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSSTAT' schema. ij> set schema SYSSTAT; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSSTAT' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSSTAT' schema. ij> set schema SYSCS_DIAG; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCS_DIAG' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSCS_DIAG' schema. ij> set schema SYSCS_UTIL; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSCS_UTIL' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSCS_UTIL' schema. ij> set schema NULLID; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'NULLID' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'NULLID' schema. ij> set schema SYSIBM; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SYSIBM' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SYSIBM' schema. ij> set schema SQLJ; 0 rows inserted/updated/deleted ij> create table foo1 (a int); ERROR 42X62: 'CREATE TABLE' is not allowed in the 'SQLJ' schema. ij> create view foo1 as select * from app.test; ERROR 42X62: 'CREATE VIEW' is not allowed in the 'SQLJ' schema. ij> -- Negative tests. Disable use of schemas starting with SYS set schema app; 0 rows inserted/updated/deleted ij> create table t1 (c1 int); 0 rows inserted/updated/deleted ij> create trigger sysblah.trig1 after update of c1 on t1 for each row insert into t1 values 1; ERROR 42X62: 'CREATE TRIGGER' is not allowed in the 'SYSBLAH' schema. ij> create procedure sysblah.dummy() language java external name 'NotReallyThere.NoMethod' parameter style java; ERROR 42X62: 'CREATE PROCEDURE' is not allowed in the 'SYSBLAH' schema. ij> drop table t1; 0 rows inserted/updated/deleted ij> set schema app; 0 rows inserted/updated/deleted ij> -- create a schema create schema test; 0 rows inserted/updated/deleted ij> -- create it again, should fail create schema test; ERROR X0Y68: Schema 'TEST' already exists. ij> -- verify it select schemaname, authorizationid from sys.sysschemas where CAST(schemaname AS VARCHAR(128)) = 'TEST'; SCHEMANAME |AUTHORIZATIONID ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- TEST |APP ij> -- create a table in test set schema test; 0 rows inserted/updated/deleted ij> create table sampletab (c1 int constraint st_c1 check (c1 > 1), c2 char(20)); 0 rows inserted/updated/deleted ij> insert into sampletab values (1,'in schema: TEST'); ERROR 23513: The check constraint 'ST_C1' was violated while performing an INSERT or UPDATE on table '"TEST"."SAMPLETAB"'. ij> insert into sampletab values (2,'in schema: TEST'); 1 row inserted/updated/deleted ij> -- verify it select schemaname, tablename, descriptor from sys.sysschemas s, sys.sysconglomerates c , sys.systables t where CAST(t.tablename AS VARCHAR(128)) = 'SAMPLETAB' and s.schemaid = c.schemaid and c.tableid = t.tableid; SCHEMANAME |TABLENAME |DESCRIPTOR --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- TEST |SAMPLETAB |NULL ij> -- do some ddl on said table create index ixsampletab on sampletab(c1); 0 rows inserted/updated/deleted ij> create index ix2sampletab on test.sampletab(c1); 0 rows inserted/updated/deleted WARNING 01504: The new index is a duplicate of an existing index: IXSAMPLETAB. ij> create view vsampletab as select * from sampletab; 0 rows inserted/updated/deleted ij> create view v2sampletab as select * from test.sampletab; 0 rows inserted/updated/deleted ij> alter table sampletab add column c3 int; 0 rows inserted/updated/deleted ij> -- switch schemas set schema APP; 0 rows inserted/updated/deleted ij> -- create table with same name in APP create table sampletab (c1 int constraint st_c1 check(c1 > 1), c2 char(20)); 0 rows inserted/updated/deleted ij> insert into sampletab values (2,'in schema: APP'); 1 row inserted/updated/deleted ij> -- verify it -- select schemaname, tablename, descriptor as descr from sys.sysschemas s, sys.sysconglomerates c , sys.systables t where CAST(t.tablename AS VARCHAR(128)) = 'SAMPLETAB' and s.schemaid = c.schemaid and c.tableid = t.tableid order by schemaname, tablename; SCHEMANAME |TABLENAME |DESCR --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- APP |SAMPLETAB |NULL TEST |SAMPLETAB |BTREE (1) TEST |SAMPLETAB |NULL ij> -- select from both the tables select * from sampletab; C1 |C2 -------------------------------- 2 |in schema: APP ij> select * from test.sampletab; C1 |C2 |C3 -------------------------------------------- 2 |in schema: TEST |NULL ij> -- switch to the test schema set schema test; 0 rows inserted/updated/deleted ij> select * from sampletab; C1 |C2 |C3 -------------------------------------------- 2 |in schema: TEST |NULL ij> select * from app.sampletab; C1 |C2 -------------------------------- 2 |in schema: APP ij> -- try a drop, should fail since we haven't -- cleaned out everything in the schema drop schema test RESTRICT; ERROR X0Y54: Schema 'TEST' cannot be dropped because it is not empty. ij> -- make sure use the correct schema for various ddl drop view vsampletab; 0 rows inserted/updated/deleted ij> drop view v2sampletab; 0 rows inserted/updated/deleted ij> drop index ixsampletab; 0 rows inserted/updated/deleted ij> drop index ix2sampletab; ERROR 42X65: Index 'IX2SAMPLETAB' does not exist. ij> alter table sampletab add column c4 int; 0 rows inserted/updated/deleted ij> select * from sampletab; C1 |C2 |C3 |C4 -------------------------------------------------------- 2 |in schema: TEST |NULL |NULL ij> -- get rid of last object in test drop table sampletab; 0 rows inserted/updated/deleted ij> -- try a drop now, should be ok drop schema test RESTRICT; 0 rows inserted/updated/deleted ij> -- use quoted id create schema "heLLo"; 0 rows inserted/updated/deleted ij> create schema "sys"; 0 rows inserted/updated/deleted ij> -- should fail drop schema "hello" RESTRICT; ERROR 42Y07: Schema 'hello' does not exist ij> -- ok drop schema "heLLo" RESTRICT; 0 rows inserted/updated/deleted ij> drop schema "sys" RESTRICT; 0 rows inserted/updated/deleted ij> -- try prepared statements, should fail prepare createSchema as 'create schema ?'; ERROR 42X01: Syntax error: Encountered "?" at line 1, column 15. ij> prepare dropSchema as 'drop schema ? RESTRICT'; ERROR 42X01: Syntax error: Encountered "?" at line 1, column 13. ij> -- -- specific drop schema tests, all should fail -- create schema x; 0 rows inserted/updated/deleted ij> set schema x; 0 rows inserted/updated/deleted ij> create view vx as select * from sys.sysschemas; 0 rows inserted/updated/deleted ij> drop schema x RESTRICT; ERROR X0Y54: Schema 'X' cannot be dropped because it is not empty. ij> drop view x.vx; 0 rows inserted/updated/deleted ij> create table x (x int); 0 rows inserted/updated/deleted ij> drop schema x restrict; ERROR X0Y54: Schema 'X' cannot be dropped because it is not empty. ij> drop table x.x; 0 rows inserted/updated/deleted ij> -- syntax not supported yet (but is in the parser) drop schema x cascade; ERROR 42X01: Syntax error: Encountered "cascade" at line 2, column 15. ij> set schema app; 0 rows inserted/updated/deleted ij> drop schema x restrict; 0 rows inserted/updated/deleted ij> -- -- test using schema names and correlation names -- first test simple use of schema names create schema test; 0 rows inserted/updated/deleted ij> set schema test; 0 rows inserted/updated/deleted ij> autocommit off; ij> -- create the all type tables create table s (i int, s smallint, c char(30), vc char(30)); 0 rows inserted/updated/deleted ij> create table t (i int, s smallint, c char(30), vc char(30)); 0 rows inserted/updated/deleted ij> create table tt (ii int, ss smallint, cc char(30), vcvc char(30)); 0 rows inserted/updated/deleted ij> create table ttt (iii int, sss smallint, ccc char(30), vcvcvc char(30)); 0 rows inserted/updated/deleted ij> -- populate the tables insert into s values (null, null, null, null); 1 row inserted/updated/deleted ij> insert into s values (0, 0, '0', '0'); 1 row inserted/updated/deleted ij> insert into s values (1, 1, '1', '1'); 1 row inserted/updated/deleted ij> insert into t values (null, null, null, null); 1 row inserted/updated/deleted ij> insert into t values (0, 0, '0', '0'); 1 row inserted/updated/deleted ij> insert into t values (1, 1, '1', '1'); 1 row inserted/updated/deleted ij> insert into t values (1, 1, '1', '1'); 1 row inserted/updated/deleted ij> insert into tt values (null, null, null, null); 1 row inserted/updated/deleted ij> insert into tt values (0, 0, '0', '0'); 1 row inserted/updated/deleted ij> insert into tt values (1, 1, '1', '1'); 1 row inserted/updated/deleted ij> insert into tt values (1, 1, '1', '1'); 1 row inserted/updated/deleted ij> insert into tt values (2, 2, '2', '2'); 1 row inserted/updated/deleted ij> insert into ttt values (null, null, null, null); 1 row inserted/updated/deleted ij> insert into ttt values (11, 11, '11', '11'); 1 row inserted/updated/deleted ij> insert into ttt values (11, 11, '11', '11'); 1 row inserted/updated/deleted ij> insert into ttt values (22, 22, '22', '22'); 1 row inserted/updated/deleted ij> commit; ij> set schema app; 0 rows inserted/updated/deleted ij> -- test simple statements which use schema names insert into test.t values (2, 2, '2', '2'); 1 row inserted/updated/deleted ij> update test.t set s = 2 where i = 2; 1 row inserted/updated/deleted ij> update test.t set s = 2 where test.t.i = 2; 1 row inserted/updated/deleted ij> delete from test.t where i = 1; 2 rows inserted/updated/deleted ij> select * from test.t; I |S |C |VC -------------------------------------------------------------------------------- NULL |NULL |NULL |NULL 0 |0 |0 |0 2 |2 |2 |2 ij> insert into test.t values (1, 1, '1', '1'); 1 row inserted/updated/deleted ij> insert into test.t values (1, 1, '1', '1'); 1 row inserted/updated/deleted ij> -- test correlated names with tables and schema names select * from test.t t1; I |S |C |VC -------------------------------------------------------------------------------- NULL |NULL |NULL |NULL 0 |0 |0 |0 2 |2 |2 |2 1 |1 |1 |1 1 |1 |1 |1 ij> -- test subqueries select * from test.s where exists (select test.s.* from test.t); I |S |C |VC -------------------------------------------------------------------------------- NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> select * from test.s t where exists (select t.* from test.t); I |S |C |VC -------------------------------------------------------------------------------- NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> select * from test.s u where exists (select u.* from test.t); I |S |C |VC -------------------------------------------------------------------------------- NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> -- column reference in select list select * from test.s where exists (select i from test.t); I |S |C |VC -------------------------------------------------------------------------------- NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> select * from test.s where exists (select test.t.i from test.t); I |S |C |VC -------------------------------------------------------------------------------- NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> -- derived table in the from list select 1 from test.s where exists (select * from (select * from test.t) x); 1 ----------- 1 1 1 ij> select 1 from test.s where exists (select * from (select * from test.t) x (i, s, c, vc) ); 1 ----------- 1 1 1 ij> -- subquery in derived table select * from (select * from test.s where exists (select * from test.t) and i = 0) a; I |S |C |VC -------------------------------------------------------------------------------- 0 |0 |0 |0 ij> -- exists under an OR select * from test.s where (1=2) or exists (select * from test.t); I |S |C |VC -------------------------------------------------------------------------------- NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> select * from test.s where (1=1) or exists (select * from test.t where (1=2)); I |S |C |VC -------------------------------------------------------------------------------- NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> -- expression subqueries -- non-correlated select * from test.s where i = (select i from test.t where i = 0); I |S |C |VC -------------------------------------------------------------------------------- 0 |0 |0 |0 ij> -- ? parameter on left hand side of expression subquery prepare subq1 as 'select * from test.s where ? = (select i from test.t where i = 0)'; ij> execute subq1 using 'values (0)'; I |S |C |VC -------------------------------------------------------------------------------- NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> remove subq1; ij> -- subquery = subquery select * from test.s where (select i from test.t where i = 0) = (select s from test.t where s = 0); I |S |C |VC -------------------------------------------------------------------------------- NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> select * from test.s t1 where (select i from test.t t2 where i = 0) = (select s from test.t t3 where s = 0); I |S |C |VC -------------------------------------------------------------------------------- NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> -- multiple subqueries at the same level select * from test.s where i = (select s from test.t where s = 0) and s = (select i from test.t where i = 2); I |S |C |VC -------------------------------------------------------------------------------- ij> -- nested subqueries select * from test.s where i = (select i from test.t where s = (select i from test.t where s = 2)); I |S |C |VC -------------------------------------------------------------------------------- ij> select * from test.s t1 where i = (select i from test.t t2 where s = (select i from test.t t3 where s = 2)); I |S |C |VC -------------------------------------------------------------------------------- ij> -- correlated subqueries -- exists disallowed in select clause select (exists (select * from test.ttt where iii = (select 11 from test.tt where ii = i and ii <> 1)) ) a from test.s order by a; ERROR 42X01: Syntax error: Encountered "exists" at line 4, column 9. ij> -- negative tests -- multiple matches at parent level select * from test.s, test.t where exists (select i from test.tt); ERROR 42X03: Column name 'I' is in more than one table in the FROM list. ij> -- match is against base table, but not derived column list select * from test.s ss (c1, c2, c3, c4) where exists (select i from test.tt); ERROR 42X04: Column 'I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'I' is not a column in the target table. ij> select * from test.s ss (c1, c2, c3, c4) where exists (select ss.i from test.tt); ERROR 42X04: Column 'SS.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'SS.I' is not a column in the target table. ij> -- correlation name exists at both levels, but only column match is at -- parent level select * from test.s where exists (select s.i from test.tt s); ERROR 42X04: Column 'S.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'S.I' is not a column in the target table. ij> -- only match is at peer level select * from test.s where exists (select * from test.tt) and exists (select ii from test.t); ERROR 42X04: Column 'II' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'II' is not a column in the target table. ij> -- correlated column in a derived table select * from test.s, (select * from test.tt where test.s.i = ii) a; ERROR 42X04: Column 'TEST.S.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'TEST.S.I' is not a column in the target table. ij> -- positive tests -- skip levels to find match select * from test.s where exists (select * from test.ttt where iii = (select 11 from test.tt where ii = i and ii <> 1)); I |S |C |VC -------------------------------------------------------------------------------- 0 |0 |0 |0 ij> -- join in subquery select * from test.s where i in (select i from test.t, test.tt where test.s.i <> i and i = ii); I |S |C |VC -------------------------------------------------------------------------------- ij> select * from test.s t1 where i in (select i from test.t t2, test.tt t3 where t1.i <> i and i = ii); I |S |C |VC -------------------------------------------------------------------------------- ij> -- joins in both query blocks select test.s.i, test.t.i from test.s, test.t where test.t.i = (select ii from test.ttt, test.tt where test.s.i = test.t.i and test.t.i = test.tt.ii and iii = 22 and ii <> 1); I |I ----------------------- 0 |0 ij> select t1.i, t2.i from test.s t1, test.t t2 where t2.i = (select ii from test.ttt t3, test.tt t4 where t1.i = t2.i and t2.i = t4.ii and iii = 22 and ii <> 1); I |I ----------------------- 0 |0 ij> ---------------------------------- -- update create table test.u (i int, s smallint, c char(30), vc char(30)); 0 rows inserted/updated/deleted ij> insert into test.u select * from test.s; 3 rows inserted/updated/deleted ij> select * from test.u; I |S |C |VC -------------------------------------------------------------------------------- NULL |NULL |NULL |NULL 0 |0 |0 |0 1 |1 |1 |1 ij> update test.u set i = 2 where vc <> (select vc from test.s where vc = '1'); 1 row inserted/updated/deleted ij> select * from test.u; I |S |C |VC -------------------------------------------------------------------------------- NULL |NULL |NULL |NULL 2 |0 |0 |0 1 |1 |1 |1 ij> delete from test.u; 3 rows inserted/updated/deleted ij> insert into test.u select * from test.s; 3 rows inserted/updated/deleted ij> -- delete delete from test.u where c < (select c from test.t where c = '2'); 2 rows inserted/updated/deleted ij> select * from test.u; I |S |C |VC -------------------------------------------------------------------------------- NULL |NULL |NULL |NULL ij> -- reset autocommit autocommit on; ij> -- bug 5146 - drop schema did not invalidate plan for create table. -- now schemas are implictly created. create schema B5146; 0 rows inserted/updated/deleted ij> create table B5146.DT(i int); 0 rows inserted/updated/deleted ij> insert into B5146.DT values 5146, 6415; 2 rows inserted/updated/deleted ij> create schema A5146; 0 rows inserted/updated/deleted ij> prepare PS5146_TABLE as 'create table A5146.I(i int)'; ij> drop schema A5146 restrict; 0 rows inserted/updated/deleted ij> execute PS5146_TABLE; 0 rows inserted/updated/deleted ij> insert into A5146.I values 3; 1 row inserted/updated/deleted ij> select * from A5146.I; I ----------- 3 ij> drop table A5146.I; 0 rows inserted/updated/deleted ij> prepare PS5146_VIEW as 'create view A5146.V AS SELECT * FROM B5146.DT'; ij> drop schema A5146 restrict; 0 rows inserted/updated/deleted ij> execute PS5146_VIEW; 0 rows inserted/updated/deleted ij> select * from A5146.V; I ----------- 5146 6415 ij> drop view A5146.V; 0 rows inserted/updated/deleted ij> prepare PS5146_TRIGGER as 'create trigger A5146.DT_TRIG AFTER INSERT ON B5146.DT FOR EACH STATEMENT UPDATE B5146.DT SET I = I + 1'; ij> drop schema A5146 restrict; 0 rows inserted/updated/deleted ij> execute PS5146_TRIGGER; 0 rows inserted/updated/deleted ij> drop trigger A5146.DT_TRIG; 0 rows inserted/updated/deleted ij> prepare PS5146_PROCEDURE as 'create procedure A5146.DUMMY() language java external name ''asdf.asdf'' parameter style java'; ij> drop schema A5146 restrict; 0 rows inserted/updated/deleted ij> execute PS5146_PROCEDURE; 0 rows inserted/updated/deleted ij> drop procedure A5146.DUMMY; 0 rows inserted/updated/deleted ij> -- check implicit schema creation for all CREATE statements that create schema objects. -- TABLE,VIEW,PROCEDURE TRIGGER, STATEMENT -- Cloudscape requires that the INDEX schema matches the (existing) table schema so -- there is no implict schema creation for CREATE INDEX. prepare ISC_TABLE as 'create table ISC.I(i int)'; ij> execute ISC_TABLE; 0 rows inserted/updated/deleted ij> insert into ISC.I values 3; 1 row inserted/updated/deleted ij> select * from ISC.I; I ----------- 3 ij> drop table ISC.I; 0 rows inserted/updated/deleted ij> drop schema ISC restrict; 0 rows inserted/updated/deleted ij> prepare ISC_VIEW as 'create view ISC.V AS SELECT * FROM B5146.DT'; ij> execute ISC_VIEW; 0 rows inserted/updated/deleted ij> select * from ISC.V; I ----------- 5146 6415 ij> drop view ISC.V; 0 rows inserted/updated/deleted ij> drop schema ISC restrict; 0 rows inserted/updated/deleted ij> prepare ISC_TRIGGER as 'create trigger ISC.DT_TRIG AFTER INSERT ON B5146.DT FOR EACH STATEMENT UPDATE B5146.DT SET I = I + 1'; ij> execute ISC_TRIGGER; 0 rows inserted/updated/deleted ij> insert into B5146.DT values 999; 1 row inserted/updated/deleted ij> drop trigger ISC.DT_TRIG; 0 rows inserted/updated/deleted ij> drop schema ISC restrict; 0 rows inserted/updated/deleted ij> select * from B5146.DT; I ----------- 5147 6416 1000 ij> prepare ISC_PROCEDURE as 'create procedure ISC.DUMMY() language java external name ''asdf.asdf'' parameter style java'; ij> execute ISC_PROCEDURE; 0 rows inserted/updated/deleted ij> CALL ISC.DUMMY(); ERROR 42X51: The class 'asdf' does not exist or is inaccessible. This can happen if the class is not public. ERROR XJ001: Java exception: 'asdf: java.lang.ClassNotFoundException'. ij> drop procedure ISC.DUMMY; 0 rows inserted/updated/deleted ij> drop schema ISC restrict; 0 rows inserted/updated/deleted ij> -- check declare of a temp table does not create a SESSION schema. DECLARE GLOBAL TEMPORARY TABLE SESSION.ISCT(c21 int) on commit delete rows not logged; 0 rows inserted/updated/deleted ij> select count(*) from SYS.SYSSCHEMAS WHERE CAST(SCHEMANAME AS VARCHAR(128)) = 'SESSION'; 1 ----------- 0 ij> drop table SESSION.ISCT; 0 rows inserted/updated/deleted ij> drop table B5146.DT; 0 rows inserted/updated/deleted ij> drop schema B5146 restrict; 0 rows inserted/updated/deleted ij> create schema SYSDJD; ERROR 42939: An object cannot be created with the schema name 'SYSDJD'. ij> drop schema SYSDJD restrict; ERROR 42Y07: Schema 'SYSDJD' does not exist ij> create schema "sys"; 0 rows inserted/updated/deleted ij> drop schema "sys" restrict; 0 rows inserted/updated/deleted ij> set schema test; 0 rows inserted/updated/deleted ij> -- drop the tables drop table s; 0 rows inserted/updated/deleted ij> drop table t; 0 rows inserted/updated/deleted ij> drop table tt; 0 rows inserted/updated/deleted ij> drop table ttt; 0 rows inserted/updated/deleted ij> drop table u; 0 rows inserted/updated/deleted ij> set schema app; 0 rows inserted/updated/deleted ij> drop schema test restrict; 0 rows inserted/updated/deleted ij>