📄 refactions1.out
字号:
ij> -- #########################################################################--- some Test cases from IBM DB2. Reusing them to test Cloudscape-- # MODIFIED BY : WHO WHEN WHY-- # : ----------- ------- --------------------------------- # : P. Selinger 950128 new testcase-- # : M. Snowbell 970716 beef up for better MPP coverage -- # : - add first column for partition-- # : - add rows-- # : Suresh T 020616 - Modified for Cloudscape-- # : Mark C 040309 - Modified for DB2 Cloudscape-- #########################################################################-- # TEST CASE : cself301.sql-- # LINE ITEM : Self-referencing subqueries-- # DESCRIPTION : Allow use of subqueries on the same table being-- # : inserted, deleted, or updated. Cursors updated-- # : or delete where current of are now similarly-- # : unrestricted. Also allowed are subqueries-- # : on tables related to the modified table by-- # : referential relationships, either directly or-- # : indirectly.-- # : This file covers cases where of delete statements-- # : where the deleted table also appears in a-- # : subquery that qualifies which rows are changed-- # : and the deleted table has a self-ref'g RI const.-- *************************************************************************create schema db2test;0 rows inserted/updated/deletedij> set schema db2test;0 rows inserted/updated/deletedij> -- "START OF TESTCASE: cself301.sql";autocommit off;ij> -- *************************************************************************-- TESTUNIT : 01-- DESCRIPTION : Create tables db2test.emp-- : insert some data into it-- EXPECTED RESULTS : SQL commands should complete successfully-- *************************************************************************-- "START OF TESTUNIT: 01";create table db2test.dept (c0 int, dno char(3) not null primary key, dname char(10));0 rows inserted/updated/deletedij> create table db2test.origdept (c0 int, dno char(3) not null primary key, dname char(10));0 rows inserted/updated/deletedij> insert into db2test.dept values (1, 'K55', 'DB');1 row inserted/updated/deletedij> insert into db2test.dept values (2, 'K52', 'OFC');1 row inserted/updated/deletedij> insert into db2test.dept values (3, 'K51', 'CS');1 row inserted/updated/deletedij> insert into db2test.origdept select * from db2test.dept;3 rows inserted/updated/deletedij> create table db2test.emp (c0 int, name char(10) not null primary key, mgrname char(10) references db2test.emp on delete cascade, dno char(3) references db2test.dept on delete set null);0 rows inserted/updated/deletedij> insert into db2test.emp (c0, name, dno) values (1, 'ASHOK', 'K51');1 row inserted/updated/deletedij> insert into db2test.emp values (2, 'JOHN', 'ASHOK', 'K51');1 row inserted/updated/deletedij> insert into db2test.emp values (3, 'ROBIN', 'ASHOK', 'K51');1 row inserted/updated/deletedij> insert into db2test.emp values (4, 'JOE1', 'ASHOK', 'K51');1 row inserted/updated/deletedij> insert into db2test.emp values (5, 'JOE2', 'ASHOK', 'K51');1 row inserted/updated/deletedij> insert into db2test.emp values (6, 'HAMID', 'JOHN', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (7, 'TRUONG', 'HAMID', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (8, 'LARRY1', 'HAMID', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (9, 'LARRY2', 'HAMID', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (10, 'BOBBIE', 'HAMID', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (11, 'ROGER', 'ROBIN', 'K52');1 row inserted/updated/deletedij> insert into db2test.emp values (12, 'JIM', 'ROGER', 'K52');1 row inserted/updated/deletedij> insert into db2test.emp values (13, 'DAN', 'ROGER', 'K52');1 row inserted/updated/deletedij> insert into db2test.emp values (14, 'SAM1', 'ROGER', 'K52');1 row inserted/updated/deletedij> insert into db2test.emp values (15, 'SAM2', 'ROGER', 'K52');1 row inserted/updated/deletedij> insert into db2test.emp values (16, 'GUY', 'JOHN', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (17, 'DON', 'GUY', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (18, 'MONICA', 'GUY', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (19, 'LILY1', 'GUY', 'K55');1 row inserted/updated/deletedij> insert into db2test.emp values (20, 'LILY2', 'GUY', 'K55');1 row inserted/updated/deletedij> create table db2test.origemp (c0 int, name char(10) not null primary key, mgrname char(10) references db2test.origemp on delete cascade, dno char(3) references db2test.origdept on delete set null );0 rows inserted/updated/deletedij> insert into db2test.origemp select * from db2test.emp;20 rows inserted/updated/deletedij> -- "END OF TESTUNIT: 01";-- *************************************************************************-- TESTUNIT : 02-- DESCRIPTION : delete where SQ on same table on RI cascade col-- EXPECTED RESULTS : SQL commands should complete successfully-- *************************************************************************-- "START OF TESTUNIT: 02";select * from db2test.emp where name in (select name from db2test.emp where mgrname = 'JOHN') order by 2, 3, 4;C0 |NAME |MGRNAME |DNO --------------------------------------16 |GUY |JOHN |K55 6 |HAMID |JOHN |K55 ij> delete from db2test.emp where name in (select name from db2test.emp where mgrname = 'JOHN');2 rows inserted/updated/deletedij> select * from db2test.emp order by name, mgrname, dno;C0 |NAME |MGRNAME |DNO --------------------------------------1 |ASHOK |NULL |K51 13 |DAN |ROGER |K52 12 |JIM |ROGER |K52 4 |JOE1 |ASHOK |K51 5 |JOE2 |ASHOK |K51 2 |JOHN |ASHOK |K51 3 |ROBIN |ASHOK |K51 11 |ROGER |ROBIN |K52 14 |SAM1 |ROGER |K52 15 |SAM2 |ROGER |K52 ij> -- "END OF TESTUNIT: 02";-- *************************************************************************-- TESTUNIT : 03-- DESCRIPTION : delete with 2 levels of SQ and self-ref in 2nd-- : correlated to 1st SQ on foreign key-- EXPECTED RESULTS : SQL command should complete successfully-- *************************************************************************-- "START OF TESTUNIT: 03";-- reset to original rowsdelete from db2test.emp;10 rows inserted/updated/deletedij> insert into db2test.emp select * from db2test.origemp;20 rows inserted/updated/deletedij> select * from db2test.emp where dno in (select dno from db2test.dept D where D.dno in (select dno from db2test.emp E where E.dno = D.dno and e.mgrname = 'JOHN')) order by 2, 3, 4;C0 |NAME |MGRNAME |DNO --------------------------------------10 |BOBBIE |HAMID |K55 17 |DON |GUY |K55 16 |GUY |JOHN |K55 6 |HAMID |JOHN |K55 8 |LARRY1 |HAMID |K55 9 |LARRY2 |HAMID |K55 19 |LILY1 |GUY |K55 20 |LILY2 |GUY |K55 18 |MONICA |GUY |K55 7 |TRUONG |HAMID |K55 ij> delete from db2test.emp where dno in (select dno from db2test.dept D where D.dno in (select dno from db2test.emp E where E.dno = D.dno and e.mgrname = 'JOHN'));10 rows inserted/updated/deletedij> select * from db2test.emp order by name, mgrname, dno;C0 |NAME |MGRNAME |DNO --------------------------------------1 |ASHOK |NULL |K51 13 |DAN |ROGER |K52 12 |JIM |ROGER |K52 4 |JOE1 |ASHOK |K51 5 |JOE2 |ASHOK |K51 2 |JOHN |ASHOK |K51 3 |ROBIN |ASHOK |K51 11 |ROGER |ROBIN |K52 14 |SAM1 |ROGER |K52 15 |SAM2 |ROGER |K52 ij> -- "END OF TESTUNIT: 03";-- *************************************************************************-- TESTUNIT : 04-- DESCRIPTION : delete with SQ GB Having SQ on-- : modified table-- EXPECTED RESULTS : SQL command should complete successfully-- *************************************************************************-- "START OF TESTUNIT: 04";-- reset to original rowsdelete from db2test.emp;10 rows inserted/updated/deletedij> insert into db2test.emp select * from db2test.origemp;20 rows inserted/updated/deletedij> select * from db2test.emp where exists ( select max(mgrname) from db2test.origemp group by dno having dno in (select dno from db2test.emp where mgrname = 'ASHOK')) order by 2, 3, 4;C0 |NAME |MGRNAME |DNO --------------------------------------1 |ASHOK |NULL |K51 WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.10 |BOBBIE |HAMID |K55 13 |DAN |ROGER |K52 17 |DON |GUY |K55 16 |GUY |JOHN |K55 6 |HAMID |JOHN |K55 12 |JIM |ROGER |K52 4 |JOE1 |ASHOK |K51 5 |JOE2 |ASHOK |K51 2 |JOHN |ASHOK |K51 8 |LARRY1 |HAMID |K55 9 |LARRY2 |HAMID |K55 19 |LILY1 |GUY |K55 20 |LILY2 |GUY |K55 18 |MONICA |GUY |K55 3 |ROBIN |ASHOK |K51 11 |ROGER |ROBIN |K52 14 |SAM1 |ROGER |K52 15 |SAM2 |ROGER |K52 7 |TRUONG |HAMID |K55 ij> delete from db2test.emp where exists ( select max(mgrname) from db2test.origemp group by dno having dno in (select dno from db2test.emp where mgrname = 'ASHOK'));20 rows inserted/updated/deletedij> select * from db2test.emp order by name, mgrname, dno;C0 |NAME |MGRNAME |DNO --------------------------------------ij> -- "END OF TESTUNIT: 04";-- *************************************************************************-- TESTUNIT : 05-- DESCRIPTION : delete with SQ GB Having SQ on-- : modified table -- 3 levels-- EXPECTED RESULTS : SQL command should complete successfully-- *************************************************************************-- "START OF TESTUNIT: 05";-- reset to original rowsdelete from db2test.emp;0 rows inserted/updated/deletedij> insert into db2test.emp select * from db2test.origemp;20 rows inserted/updated/deletedij> select * from db2test.emp where exists ( select max(mgrname) from db2test.origemp group by dno having dno in (select dno from db2test.dept D where dno in (select dno from db2test.emp E2 where D. dno = E2.dno))) order by 2, 3, 4;C0 |NAME |MGRNAME |DNO --------------------------------------1 |ASHOK |NULL |K51 WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.10 |BOBBIE |HAMID |K55 13 |DAN |ROGER |K52 17 |DON |GUY |K55 16 |GUY |JOHN |K55 6 |HAMID |JOHN |K55 12 |JIM |ROGER |K52 4 |JOE1 |ASHOK |K51 5 |JOE2 |ASHOK |K51 2 |JOHN |ASHOK |K51 8 |LARRY1 |HAMID |K55 9 |LARRY2 |HAMID |K55 19 |LILY1 |GUY |K55 ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -