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>