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. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> insert into blah.blah.blah.t values (3); ERROR 42X01: Syntax error: Encountered "." at line 1, column 22. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. 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. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- name too long: create table myworld.mycat.myschem.s(c int); ERROR 42X01: Syntax error: Encountered "." at line 2, column 27. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. 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. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> insert into loud.honk.blat.s values (4); ERROR 42X01: Syntax error: Encountered "." at line 1, column 22. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. 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. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. 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. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> prepare dropSchema as 'drop schema ? RESTRICT'; ERROR 42X01: Syntax error: Encountered "?" at line 1, column 13. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. 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. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. 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 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; A ----- true NULL NULL 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>