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. -- -- tests for synonym support -- When we decide to convert this test to junit test, the converted tests can -- go in existing SynonymTest.java set schema APP; 0 rows inserted/updated/deleted ij> -- negative tests ----- Create a synonym to itself. Error. create synonym syn for syn; ERROR 42916: Synonym 'SYN' cannot be created for 'APP.SYN' as it would result in a circular synonym chain. ij> create synonym syn for APP.syn; ERROR 42916: Synonym 'SYN' cannot be created for 'APP.SYN' as it would result in a circular synonym chain. ij> create synonym APP.syn for syn; ERROR 42916: Synonym 'APP.SYN' cannot be created for 'APP.SYN' as it would result in a circular synonym chain. ij> create synonym APP.syn for APP.syn; ERROR 42916: Synonym 'APP.SYN' cannot be created for 'APP.SYN' as it would result in a circular synonym chain. ij> -- Create a simple synonym loop. Error. create synonym synonym1 for synonym; 0 rows inserted/updated/deleted WARNING 01522: The newly defined synonym 'SYNONYM1' resolved to the object 'APP.SYNONYM' which is currently undefined. : ij> create synonym synonym for synonym1; ERROR 42916: Synonym 'SYNONYM' cannot be created for 'SYNONYM1' as it would result in a circular synonym chain. ij> drop synonym synonym1; 0 rows inserted/updated/deleted ij> -- Create a larger synonym loop. create synonym ts1 for ts; 0 rows inserted/updated/deleted WARNING 01522: The newly defined synonym 'TS1' resolved to the object 'APP.TS' which is currently undefined. : ij> create synonym ts2 for ts1; 0 rows inserted/updated/deleted WARNING 01522: The newly defined synonym 'TS2' resolved to the object 'APP.TS' which is currently undefined. : ij> create synonym ts3 for ts2; 0 rows inserted/updated/deleted WARNING 01522: The newly defined synonym 'TS3' resolved to the object 'APP.TS' which is currently undefined. : ij> create synonym ts4 for ts3; 0 rows inserted/updated/deleted WARNING 01522: The newly defined synonym 'TS4' resolved to the object 'APP.TS' which is currently undefined. : ij> create synonym ts5 for ts4; 0 rows inserted/updated/deleted WARNING 01522: The newly defined synonym 'TS5' resolved to the object 'APP.TS' which is currently undefined. : ij> create synonym ts6 for ts5; 0 rows inserted/updated/deleted WARNING 01522: The newly defined synonym 'TS6' resolved to the object 'APP.TS' which is currently undefined. : ij> create synonym ts for ts6; ERROR 42916: Synonym 'TS' cannot be created for 'TS6' as it would result in a circular synonym chain. ij> drop synonym App.ts1; 0 rows inserted/updated/deleted ij> drop synonym "APP".ts2; 0 rows inserted/updated/deleted ij> drop synonym TS3; 0 rows inserted/updated/deleted ij> drop synonym ts4; 0 rows inserted/updated/deleted ij> drop synonym ts5; 0 rows inserted/updated/deleted ij> drop synonym app.ts6; 0 rows inserted/updated/deleted ij> -- Synonyms and table/view share same namespace. Negative tests for this. create table table1 (i int, j int); 0 rows inserted/updated/deleted ij> insert into table1 values (1,1), (2,2); 2 rows inserted/updated/deleted ij> create view view1 as select i, j from table1; 0 rows inserted/updated/deleted ij> create synonym table1 for t1; ERROR X0Y68: Table/View 'TABLE1' already exists. ij> create synonym APP.Table1 for t1; ERROR X0Y68: Table/View 'TABLE1' already exists. ij> create synonym app.TABLE1 for "APP"."T"; ERROR X0Y68: Table/View 'TABLE1' already exists. ij> create synonym APP.VIEW1 for v1; ERROR X0Y68: Table/View 'VIEW1' already exists. ij> create synonym "APP"."VIEW1" for app.v; ERROR X0Y68: Table/View 'VIEW1' already exists. ij> -- Synonyms can't be created on temporary tables declare global temporary table session.t1 (c1 int) not logged; 0 rows inserted/updated/deleted ij> create synonym synForTemp for session.t1; ERROR XCL51: The requested function can not reference tables in SESSION schema. ij> create synonym synForTemp for session."T1"; ERROR XCL51: The requested function can not reference tables in SESSION schema. ij> -- Synonyms can't be created in session schemas create synonym session.table1 for APP.table1; ERROR XCL51: The requested function can not reference tables in SESSION schema. ij> -- Creating a table or a view when a synonym of that name is present. Error. create synonym myTable for table1; 0 rows inserted/updated/deleted ij> create table myTable(i int, j int); ERROR X0Y32: Table/View 'MYTABLE' already exists in Schema 'APP'. ij> create view myTable as select * from table1; ERROR X0Y32: Table/View 'MYTABLE' already exists in Schema 'APP'. ij> -- Positive test cases ----- Using synonym in DML select * from myTable; ERROR X0Y79: Statement.executeUpdate() cannot be called with a statement that returns a ResultSet. ij> select * from table1; I |J ----- 1 |1 2 |2 ij> insert into myTable values (3,3), (4,4); 2 rows inserted/updated/deleted ij> select * from mytable; I |J ----- 1 |1 2 |2 3 |3 4 |4 ij> update myTable set i=3 where j=4; 1 row inserted/updated/deleted ij> select * from mytable; I |J ----- 1 |1 2 |2 3 |3 3 |4 ij> select * from table1; I |J ----- 1 |1 2 |2 3 |3 3 |4 ij> delete from myTable where i> 2; 2 rows inserted/updated/deleted ij> select * from "APP"."MYTABLE"; I |J ----- 1 |1 2 |2 ij> select * from APP.table1; I |J ----- 1 |1 2 |2 ij> -- Try some cursors get cursor c1 as 'select * from myTable'; ij> next c1; I |J ----- 1 |1 ij> next c1; I |J ----- 2 |2 ij> close c1; ij> -- Try updatable cursors autocommit off; ij> get cursor c2 as 'select * from myTable for update'; ij> next c2; I |J ----- 1 |1 ij> update myTable set i=5 where current of c2; 1 row inserted/updated/deleted ij> close c2; ij> autocommit on; ij> select * from table1; I |J ----- 5 |1 2 |2 ij> -- Try updatable cursors, with synonym at the top, base table inside. autocommit off; ij> get cursor c2 as 'select * from app.table1 for update'; ij> next c2; I |J ----- 5 |1 ij> update myTable set i=6 where current of c2; 1 row inserted/updated/deleted ij> close c2; ij> autocommit on; ij> select * from table1; I |J ----- 6 |1 2 |2 ij> -- trigger tests create table table2 (i int, j int); 0 rows inserted/updated/deleted ij> -- Should fail create trigger tins after insert on myTable referencing new_table as new for each statement insert into table2 select i,j from table1; ERROR 42Y55: 'CREATE TRIGGER' cannot be performed on 'MYTABLE' because it does not exist. ij> -- Should pass create trigger tins after insert on table1 referencing new_table as new for each statement insert into table2 select i,j from table1; 0 rows inserted/updated/deleted ij> drop trigger tins; 0 rows inserted/updated/deleted ij> create trigger triggerins after insert on table2 referencing new_table as new for each statement insert into myTable select i,j from new; 0 rows inserted/updated/deleted ij> select * from myTable; I |J ----- 6 |1 2 |2 ij> insert into table2 values (5, 5); 1 row inserted/updated/deleted ij> select * from myTable; I |J ----- 6 |1 2 |2 5 |5 ij> drop table table2; 0 rows inserted/updated/deleted ij> -- Try referential constraints. Synonyms should not be allowed there. create table primaryTab (i int not null primary key, j int, c char(10)); 0 rows inserted/updated/deleted ij> create synonym synPrimary for primaryTab; 0 rows inserted/updated/deleted ij> -- Should fail create table foreignTab(i int, j int references synPrimary(i)); ERROR X0Y46: Constraint 'xxxxGENERATED-IDxxxx' is invalid: referenced table SYNPRIMARY does not exist. ij> create table foreignTab(i int, j int references primaryTab(i)); 0 rows inserted/updated/deleted ij> drop table foreignTab; 0 rows inserted/updated/deleted ij> drop table primaryTab; 0 rows inserted/updated/deleted ij> drop synonym synPrimary; 0 rows inserted/updated/deleted ij> -- Tests with non existant schemas ----- Implicitly creates junkSchema create synonym junkSchema.syn1 for table2; 0 rows inserted/updated/deleted WARNING 01522: The newly defined synonym 'SYN1' resolved to the object 'JUNKSCHEMA.TABLE2' which is currently undefined. : ij> select * from junkSchema.syn1; ERROR 42X05: Table/View 'JUNKSCHEMA.TABLE2' does not exist. ij> set schema junkSchema; 0 rows inserted/updated/deleted ij> create table table2(c char(10)); 0 rows inserted/updated/deleted ij> select * from syn1; C ----- ij> set schema APP; 0 rows inserted/updated/deleted ij> -- Should resolve to junkSchema.table2 select * from junkSchema.syn1; C ----- ij> drop table junkSchema.table2; 0 rows inserted/updated/deleted ij> -- Should fail. Need to drop synonym first drop schema junkSchema restrict; ERROR X0Y54: Schema 'JUNKSCHEMA' cannot be dropped because it is not empty. ij> drop synonym junkSchema.syn1; 0 rows inserted/updated/deleted ij> drop schema junkSchema restrict; 0 rows inserted/updated/deleted ij> -- Test with target schema missing create synonym mySynonym for notPresent.t1; 0 rows inserted/updated/deleted WARNING 01522: The newly defined synonym 'MYSYNONYM' resolved to the object 'NOTPRESENT.T1' which is currently undefined. : ij> select * from mySynonym; ERROR 42Y07: Schema 'NOTPRESENT' does not exist ij> create table notPresent.t1(j int, c char(10)); 0 rows inserted/updated/deleted ij> insert into notPresent.t1 values (100, 'satheesh'); 1 row inserted/updated/deleted ij> -- Should resolve now select * from mySynonym; J |C ----- 100 |satheesh ij> drop table notPresent.t1; 0 rows inserted/updated/deleted ij> drop synonym mySynonym; 0 rows inserted/updated/deleted ij> -- Positive test case with three levels of synonym chaining create schema synonymSchema; 0 rows inserted/updated/deleted ij> create synonym synonymSchema.mySynonym1 for APP.table1; 0 rows inserted/updated/deleted ij> create synonym APP.mySynonym2 for "SYNONYMSCHEMA"."MYSYNONYM1"; 0 rows inserted/updated/deleted ij> create synonym mySynonym for mySynonym2; 0 rows inserted/updated/deleted ij> select * from table1; I |J ----- 6 |1 2 |2 5 |5 ij> select * from mySynonym; I |J ----- 6 |1 2 |2 5 |5 ij> insert into mySynonym values (6,6); 1 row inserted/updated/deleted ij> insert into mySynonym select * from mySynonym where i<2; 0 rows inserted/updated/deleted ij> select * from mySynonym; I |J ----- 6 |1 2 |2 5 |5 6 |6 ij> update mySynonym set j=5; 4 rows inserted/updated/deleted ij> update mySynonym set j=4 where i=5; 1 row inserted/updated/deleted ij> delete from mySynonym where j=6; 0 rows inserted/updated/deleted ij> select * from mySynonym; I |J ----- 6 |5 2 |5 5 |4 6 |5 ij> select * from table1; I |J ----- 6 |5 2 |5 5 |4 6 |5 ij> -- cursor on mySynonym get cursor c1 as 'select * from mySynonym'; ij> next c1; I |J ----- 6 |5 ij> next c1; I |J ----- 2 |5 ij> next c1; I |J ----- 5 |4 ij> close c1; ij> -- More negative tests to check dependencies select * from mySynonym; I |J ----- 6 |5 2 |5 5 |4 6 |5 ij> drop synonym mySynonym; 0 rows inserted/updated/deleted ij> -- Previously compiled cached statement should get invalidated select * from mySynonym; ERROR 42X05: Table/View 'MYSYNONYM' does not exist. ij> -- drop and recreate schema test create schema testSchema; 0 rows inserted/updated/deleted ij> create synonym multiSchema for testSchema.testtab; 0 rows inserted/updated/deleted WARNING 01522: The newly defined synonym 'MULTISCHEMA' resolved to the object 'TESTSCHEMA.TESTTAB' which is currently undefined. : ij> select * from multiSchema; ERROR 42X05: Table/View 'TESTSCHEMA.TESTTAB' does not exist. ij> create table testSchema.testtab(i int, c char(10)); 0 rows inserted/updated/deleted ij> insert into testSchema.testtab values (1, 'synonym'); 1 row inserted/updated/deleted ij> select * from multiSchema; I |C ----- 1 |synonym ij> drop table testSchema.testtab; 0 rows inserted/updated/deleted ij> drop schema testSchema restrict; 0 rows inserted/updated/deleted ij> create schema testSchema; 0 rows inserted/updated/deleted ij> create table testSchema.testtab(j int, c1 char(10), c2 char(20)); 0 rows inserted/updated/deleted ij> insert into testSchema.testtab values (1, 'synonym', 'test'); 1 row inserted/updated/deleted ij> select * from multiSchema; J |C1 |C2 ----- 1 |synonym |test ij> drop synonym multiSchema; 0 rows inserted/updated/deleted ij> drop table testSchema.testtab; 0 rows inserted/updated/deleted ij> drop view view1; 0 rows inserted/updated/deleted ij> drop table table1; 0 rows inserted/updated/deleted ij>