| 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: 'IX2SAMPLETAB' index not created because it 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, isindex as ind, 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, isindex; |
| SCHEMANAME |TABLENAME |IND |DESCR |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| APP |SAMPLETAB |false|NULL |
| TEST |SAMPLETAB |false|NULL |
| TEST |SAMPLETAB |true |BTREE (1) |
| 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 |
| ----- |
| false |
| false |
| true |
| 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> |
