Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 38051 invoked from network); 22 Jan 2007 17:24:55 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 22 Jan 2007 17:24:55 -0000 Received: (qmail 16561 invoked by uid 500); 22 Jan 2007 17:25:01 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 16534 invoked by uid 500); 22 Jan 2007 17:25:01 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 16523 invoked by uid 99); 22 Jan 2007 17:25:01 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 22 Jan 2007 09:25:01 -0800 X-ASF-Spam-Status: No, hits=-9.4 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 22 Jan 2007 09:24:52 -0800 Received: by eris.apache.org (Postfix, from userid 65534) id 6E39A1A9820; Mon, 22 Jan 2007 09:23:45 -0800 (PST) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r498716 [2/2] - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/ suites/ tests/lang/ Date: Mon, 22 Jan 2007 17:23:44 -0000 To: derby-commits@db.apache.org From: djd@apache.org X-Mailer: svnmailer-1.1.0 Message-Id: <20070122172345.6E39A1A9820@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out?view=diff&rev=498716&r1=498715&r2=498716 ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out Mon Jan 22 09:23:42 2007 @@ -39,6 +39,7 @@ -- # : 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/deleted ij> set schema db2test; @@ -119,6 +120,7 @@ ij> insert into db2test.origemp select * from db2test.emp; 20 rows inserted/updated/deleted ij> -- "END OF TESTUNIT: 01"; + -- ************************************************************************* -- TESTUNIT : 02 -- DESCRIPTION : delete where SQ on same table on RI cascade col @@ -148,6 +150,8 @@ 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 @@ -193,6 +197,7 @@ 14 |SAM1 |ROGER |K52 15 |SAM2 |ROGER |K52 ij> -- "END OF TESTUNIT: 03"; + -- ************************************************************************* -- TESTUNIT : 04 -- DESCRIPTION : delete with SQ GB Having SQ on @@ -258,6 +263,7 @@ C0 |NAME |MGRNAME |DNO -------------------------------------- ij> -- "END OF TESTUNIT: 04"; + -- ************************************************************************* -- TESTUNIT : 05 -- DESCRIPTION : delete with SQ GB Having SQ on @@ -268,6 +274,7 @@ -- reset to original rows delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.emp select * from db2test.origemp; 20 rows inserted/updated/deleted ij> select * from db2test.emp where exists ( select max(mgrname) from @@ -325,6 +332,7 @@ C0 |NAME |MGRNAME |DNO -------------------------------------- ij> -- "END OF TESTUNIT: 05"; + -- ************************************************************************* -- TESTUNIT : 06 -- DESCRIPTION : delete on view with SQ GB Having SQ on @@ -335,6 +343,7 @@ -- reset to original rows delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.emp select * from db2test.origemp; 20 rows inserted/updated/deleted ij> create view db2test.vemp (vc0, vname, vmgrname, vdno) as @@ -412,6 +421,7 @@ 15 |SAM2 |ROGER |K52 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 06"; + -- "cleanup"; drop view db2test.vemp; 0 rows inserted/updated/deleted @@ -441,6 +451,7 @@ -- # : shape of the RI tree is a 1 level star fanout. -- ######################################################################### -- "START OF TESTCASE: cself302.sql"; + autocommit off; ij> -- ************************************************************************* -- TESTUNIT : 01 @@ -523,6 +534,7 @@ 20 rows inserted/updated/deleted ij> commit; ij> -- "END OF TESTUNIT: 01"; + -- ************************************************************************* -- TESTUNIT : 02 -- DESCRIPTION : delete where SQ on same table on RI cascade col @@ -571,6 +583,8 @@ 16 |GUY |JOHN |K55 6 |HAMID |JOHN |K55 ij> -- "END OF TESTUNIT: 02"; + + -- ************************************************************************* -- TESTUNIT : 03 -- DESCRIPTION : delete with 2 levels of SQ and self-ref in 2nd @@ -637,6 +651,7 @@ 16 |GUY |JOHN |K55 6 |HAMID |JOHN |K55 ij> -- "END OF TESTUNIT: 03"; + -- ************************************************************************* -- TESTUNIT : 04 -- DESCRIPTION : delete with SQ GB Having SQ on @@ -683,6 +698,7 @@ -------------------------------------- 1 |ASHOK |NULL |K51 ij> -- "END OF TESTUNIT: 04"; + -- ************************************************************************* -- TESTUNIT : 05 -- DESCRIPTION : delete with SQ on child table correlated to SQ @@ -693,8 +709,10 @@ -- reset to original rows delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.dept; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.secondemp; 1 row inserted/updated/deleted ij> insert into db2test.dept select * from db2test.origdept; @@ -744,6 +762,7 @@ -------------------------------------- 1 |ASHOK |NULL |K51 ij> -- "END OF TESTUNIT: 05"; + -- ************************************************************************* -- TESTUNIT : 06 -- DESCRIPTION : delete with SQ on child table correlated to SQ @@ -754,8 +773,10 @@ -- reset to original rows delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.dept; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.secondemp; 1 row inserted/updated/deleted ij> insert into db2test.dept select * from db2test.origdept; @@ -800,6 +821,8 @@ C0 |NAME |MGRNAME |DNO -------------------------------------- ij> -- "END OF TESTUNIT: 06"; + + -- ************************************************************************* -- TESTUNIT : 07 -- DESCRIPTION : delete with SQ on child table correlated to SQ @@ -810,10 +833,13 @@ -- reset to original rows delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.dept; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.secondemp; 1 row inserted/updated/deleted ij> insert into db2test.dept select * from db2test.origdept; @@ -854,6 +880,7 @@ C0 |NAME |MGRNAME |DNO -------------------------------------- ij> -- "END OF TESTUNIT: 07"; + -- ************************************************************************* -- TESTUNIT : 08 -- DESCRIPTION : delete with SQ on child table correlated to SQ @@ -864,12 +891,15 @@ -- reset to original rows delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.secondemp; 1 row inserted/updated/deleted ij> delete from db2test.dept; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.dept select * from db2test.origdept; 3 rows inserted/updated/deleted ij> insert into db2test.emp select * from db2test.origemp; @@ -907,6 +937,7 @@ C0 |NAME |MGRNAME |DNO -------------------------------------- ij> -- "END OF TESTUNIT: 08"; + -- ************************************************************************* -- TESTUNIT : 09 -- DESCRIPTION : delete on view with SQ GB Having SQ on @@ -917,10 +948,13 @@ -- reset to original rows delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.dept; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.secondemp; 1 row inserted/updated/deleted ij> insert into db2test.dept select * from db2test.origdept; @@ -980,6 +1014,7 @@ 2 |JOHN |ASHOK |K51 3 |ROBIN |ASHOK |K51 ij> -- "END OF TESTUNIT: 09"; + -- ************************************************************************* -- TESTUNIT : 10 -- DESCRIPTION : delete on iudt where SQ on @@ -1138,6 +1173,7 @@ -- select * from db2test.dept where dno in (select vdno from -- db2test.vempjoin12) -- and dno in ('K55', 'K52') order by 2, 3; + --delete from db2test.dept where dno in (select vdno from -- db2test.vempjoin12) -- and dno in ('K55', 'K52'); @@ -1516,6 +1552,8 @@ 18 |MONICA |GUY |K55 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 10"; + + -- ************************************************************************* -- TESTUNIT : 11 -- DESCRIPTION : delete with many SQ levels correl'd on child tables @@ -1527,36 +1565,51 @@ 3 rows inserted/updated/deleted ij> delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.secondemp; 1 row inserted/updated/deleted ij> delete from db2test.emp2; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp3; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp4; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp5; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp6; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp7; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp8; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp9; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp10; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp11; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp12; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp13; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp14; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp15; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.dept select * from db2test.origdept; 3 rows inserted/updated/deleted ij> insert into db2test.emp select * from db2test.origemp; @@ -1750,6 +1803,8 @@ 2 |JOHN |ASHOK |K51 3 |ROBIN |ASHOK |K51 ij> -- "END OF TESTUNIT: 11"; + + -- ************************************************************************* -- TESTUNIT : 12 -- DESCRIPTION : delete on view with union of 15 child tables @@ -1763,34 +1818,49 @@ 1 row inserted/updated/deleted ij> delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp3; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp4; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp5; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp6; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp7; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp8; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp9; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp10; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp11; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp12; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp13; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp14; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp15; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.dept select * from db2test.origdept; 3 rows inserted/updated/deleted ij> insert into db2test.emp select * from db2test.origemp; @@ -2021,6 +2091,7 @@ 3 |ROBIN |ASHOK |K51 ij> -- "END OF TESTUNIT: 12"; -- "cleanup"; + drop view VEMPUNION15; 0 rows inserted/updated/deleted ij> drop view VEMPJOIN12; @@ -2067,6 +2138,7 @@ 0 rows inserted/updated/deleted ij> commit; ij> -- "cself302.sql ENDED"; + -- ######################################################################### -- # TESTCASE NAME : cself303.sql -- # LINE ITEM : Self-referencing subqueries @@ -2082,7 +2154,9 @@ -- # : tables in the query by cascade on delete. -- # : shape of the RI tree is a 6 level chain. -- ######################################################################### + -- "START OF TESTCASE: cself303.sql"; + autocommit off; ij> -- ************************************************************************* -- TESTUNIT : 01 @@ -2202,6 +2276,7 @@ ij> insert into db2test.emp6 select * from db2test.emp; 20 rows inserted/updated/deleted ij> -- "END OF TESTUNIT: 01"; + -- ************************************************************************* -- TESTUNIT : 02 -- DESCRIPTION : delete where SQ on child table on RI cascade col @@ -2293,6 +2368,7 @@ 14 |SAM1 |ROGER |K52 15 |SAM2 |ROGER |K52 ij> -- "END OF TESTUNIT: 02"; + -- ************************************************************************* -- TESTUNIT : 03 -- DESCRIPTION : delete where SQ on child table on RI cascade col @@ -2304,14 +2380,19 @@ 3 rows inserted/updated/deleted ij> delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp3; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp4; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp5; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.dept select * from db2test.origdept; 3 rows inserted/updated/deleted ij> insert into db2test.emp select * from db2test.origemp; @@ -2409,6 +2490,7 @@ 14 |SAM1 |ROGER |K52 15 |SAM2 |ROGER |K52 ij> -- "END OF TESTUNIT: 03"; + -- ************************************************************************* -- TESTUNIT : 04 -- DESCRIPTION : delete where SQ chain reversing RI child chain @@ -2420,14 +2502,19 @@ 3 rows inserted/updated/deleted ij> delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp3; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp4; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp5; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.dept select * from db2test.origdept; 3 rows inserted/updated/deleted ij> insert into db2test.emp select * from db2test.origemp; @@ -2463,6 +2550,7 @@ where e1.name = db2test.emp.mgrname and e1.mgrname = 'JOHN'))))); 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> select * from db2test.emp order by dno, name, mgrname; C0 |NAME |MGRNAME |DNO -------------------------------------- @@ -2579,6 +2667,7 @@ 18 |MONICA |GUY |K55 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 04"; + -- ************************************************************************* -- TESTUNIT : 05 -- DESCRIPTION : delete where SQ chain reversing RI child chain @@ -2591,14 +2680,19 @@ 3 rows inserted/updated/deleted ij> delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp3; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp4; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp5; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.dept select * from db2test.origdept; 3 rows inserted/updated/deleted ij> insert into db2test.emp select * from db2test.origemp; @@ -2748,6 +2842,8 @@ 18 |MONICA |GUY |K55 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 05"; + + -- ************************************************************************* -- TESTUNIT : 06 -- DESCRIPTION : delete where SQ chain reversing RI child chain @@ -2760,14 +2856,19 @@ 3 rows inserted/updated/deleted ij> delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp3; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp4; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp5; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.dept select * from db2test.origdept; 3 rows inserted/updated/deleted ij> insert into db2test.emp select * from db2test.origemp; @@ -2916,6 +3017,7 @@ 18 |MONICA |GUY |K55 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 06"; + -- ************************************************************************* -- TESTUNIT : 07 -- DESCRIPTION : delete where SQ chain reversing RI child chain @@ -2928,14 +3030,19 @@ 3 rows inserted/updated/deleted ij> delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp3; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp4; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp5; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.dept select * from db2test.origdept; 3 rows inserted/updated/deleted ij> insert into db2test.emp select * from db2test.origemp; @@ -3077,6 +3184,7 @@ 18 |MONICA |GUY |K55 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 07"; + -- ************************************************************************* -- TESTUNIT : 08 -- DESCRIPTION : delete where SQ chain reversing RI child chain @@ -3089,14 +3197,19 @@ 3 rows inserted/updated/deleted ij> delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp3; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp4; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp5; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.dept select * from db2test.origdept; 3 rows inserted/updated/deleted ij> insert into db2test.emp select * from db2test.origemp; @@ -3246,6 +3359,7 @@ 18 |MONICA |GUY |K55 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 08"; + -- ************************************************************************* -- TESTUNIT : 09 -- DESCRIPTION : delete where SQ chain reversing RI child chain @@ -3258,14 +3372,19 @@ 3 rows inserted/updated/deleted ij> delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp3; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp4; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp5; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.dept select * from db2test.origdept; 3 rows inserted/updated/deleted ij> insert into db2test.emp select * from db2test.origemp; @@ -3438,6 +3557,7 @@ 0 rows inserted/updated/deleted ij> rollback; ij> -- "cself303.clp ENDED"; + -- ######################################################################### -- # TESTCASE NAME : cself304.sql -- # LINE ITEM : Self-referencing subqueries @@ -3540,12 +3660,14 @@ 0 rows inserted/updated/deleted ij> commit; ij> -- "END OF TESTUNIT: 01"; + -- ************************************************************************ -- TESTUNIT : 02 -- DESCRIPTION : delete where SQ on child table on RI cascade col -- EXPECTED RESULTS : SQL commands should complete successfully -- ************************************************************************* -- "START OF TESTUNIT: 02"; + select * from db2test.emp e where dno in (select dno from db2test.emp2 e2 where e2.dno in (select dno from db2test.dept d where dmgrname = 'JOHN')); @@ -3603,6 +3725,7 @@ 0 rows inserted/updated/deleted ij> commit; ij> -- "END OF TESTUNIT: 02"; + -- ************************************************************************ -- TESTUNIT : 03 -- DESCRIPTION : same as 02, but with correlation to top table @@ -3614,6 +3737,7 @@ 3 rows inserted/updated/deleted ij> delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 1 row inserted/updated/deleted ij> insert into db2test.dept select * from db2test.origdept; @@ -3635,6 +3759,7 @@ where e2.dno in (select dno from db2test.dept d where mgrname = 'john' and e2.name = d.dmgrname)); 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> select * from db2test.dept order by dno, dname, dmgrname; C0 |DNO|DNAME |DMGRNAME ------------------------------------- @@ -3691,6 +3816,7 @@ 0 rows inserted/updated/deleted ij> commit; ij> -- "END OF TESTUNIT: 03"; + -- ************************************************************************ -- TESTUNIT : 04 -- DESCRIPTION : same as 02 but SQ's reversed @@ -3702,6 +3828,7 @@ 3 rows inserted/updated/deleted ij> delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 1 row inserted/updated/deleted ij> insert into db2test.dept select * from db2test.origdept; @@ -3775,6 +3902,7 @@ 0 rows inserted/updated/deleted ij> commit; ij> -- "END OF TESTUNIT: 04"; + -- ************************************************************************ -- TESTUNIT : 05 -- DESCRIPTION : same as 03 but SQ's reversed @@ -3786,6 +3914,7 @@ 3 rows inserted/updated/deleted ij> delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 1 row inserted/updated/deleted ij> insert into db2test.dept select * from db2test.origdept; @@ -3859,6 +3988,7 @@ 0 rows inserted/updated/deleted ij> commit; ij> -- "END OF TESTUNIT: 05"; + -- ************************************************************************ -- TESTUNIT : 06 -- DESCRIPTION : same as 05 but extra table @@ -3870,6 +4000,7 @@ 3 rows inserted/updated/deleted ij> delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 1 row inserted/updated/deleted ij> insert into db2test.dept select * from db2test.origdept; @@ -3945,6 +4076,7 @@ 0 rows inserted/updated/deleted ij> commit; ij> -- "END OF TESTUNIT: 06"; + -- ************************************************************************* -- TESTUNIT : 07 -- DESCRIPTION : delete on parent with SQ on join view of children @@ -4011,6 +4143,7 @@ 0 rows inserted/updated/deleted ij> commit; ij> -- "END OF TESTUNIT: 07"; + -- ************************************************************************* -- TESTUNIT : 08 -- DESCRIPTION : delete on parent with SQ on union view of children @@ -4097,6 +4230,7 @@ --drop view db2test.vempunion; commit; ij> -- "cself304.sql ENDED"; + -- ######################################################################### -- # TESTCASE NAME : cself311.sql -- # LINE ITEM : Self-referencing subqueries @@ -4113,7 +4247,9 @@ -- # : and the deleted table has a self-ref'g RI const. -- # EXPECTED RESULTS : File should run successfully with no errors. -- ######################################################################### + -- "START OF TESTCASE: cself311.sql"; + autocommit off; ij> -- ************************************************************************* -- TESTUNIT : 01 @@ -4189,6 +4325,7 @@ ij> insert into db2test.origemp select * from db2test.emp; 20 rows inserted/updated/deleted ij> -- "END OF TESTUNIT: 01"; + -- ************************************************************************* -- TESTUNIT : 02 -- DESCRIPTION : delete where SQ on same table on RI set null col @@ -4226,6 +4363,8 @@ 15 |SAM2 |ROGER |K52 7 |TRUONG |NULL |K55 ij> -- "END OF TESTUNIT: 02"; + + -- ************************************************************************* -- TESTUNIT : 03 -- DESCRIPTION : delete with 2 levels of SQ and self-ref in 2nd @@ -4271,6 +4410,7 @@ 14 |SAM1 |ROGER |K52 15 |SAM2 |ROGER |K52 ij> -- "END OF TESTUNIT: 03"; + -- ************************************************************************* -- TESTUNIT : 04 -- DESCRIPTION : delete with SQ GB Having SQ on @@ -4336,6 +4476,7 @@ C0 |NAME |MGRNAME |DNO -------------------------------------- ij> -- "END OF TESTUNIT: 04"; + -- ************************************************************************* -- TESTUNIT : 05 -- DESCRIPTION : delete with SQ GB Having SQ on @@ -4346,6 +4487,8 @@ -- reset to original rows delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.emp select * from db2test.origemp; 20 rows inserted/updated/deleted ij> select * from db2test.emp where exists ( select max(mgrname) from @@ -4403,6 +4546,7 @@ C0 |NAME |MGRNAME |DNO -------------------------------------- ij> -- "END OF TESTUNIT: 05"; + -- ************************************************************************* -- TESTUNIT : 06 -- DESCRIPTION : delete on view with SQ GB Having SQ on @@ -4413,6 +4557,8 @@ -- reset to original rows delete from db2test.emp; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.emp select * from db2test.origemp; 20 rows inserted/updated/deleted ij> create view db2test.vemp (vc0, vname, vmgrname, vdno) as @@ -4490,6 +4636,7 @@ 15 |SAM2 |ROGER |K52 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 06"; + -- "cleanup"; drop view db2test.vemp; 0 rows inserted/updated/deleted @@ -4503,6 +4650,7 @@ 0 rows inserted/updated/deleted ij> rollback; ij> -- "cself311.clp ENDED"; + -- ######################################################################### -- # TESTCASE NAME : cself312.sql -- # LINE ITEM : Self-referencing subqueries @@ -4518,7 +4666,9 @@ -- # : tables in the query by set null on delete. -- # : shape of the RI tree is a 1 level star fanout. -- ######################################################################### + -- "START OF TESTCASE: cself312.sql"; + autocommit off; ij> -- ************************************************************************* -- TESTUNIT : 01 @@ -4601,6 +4751,7 @@ 20 rows inserted/updated/deleted ij> commit; ij> -- "END OF TESTUNIT: 01"; + -- ************************************************************************* -- TESTUNIT : 02 -- DESCRIPTION : delete where SQ on same table on RI set null col @@ -4667,6 +4818,8 @@ 18 |MONICA |GUY |K55 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 02"; + + -- ************************************************************************* -- TESTUNIT : 03 -- DESCRIPTION : delete with 2 levels of SQ and self-ref in 2nd @@ -4679,6 +4832,7 @@ 20 rows inserted/updated/deleted ij> delete from db2test.dept; 2 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.secondemp; 20 rows inserted/updated/deleted ij> insert into db2test.dept select * from db2test.origdept; @@ -4751,6 +4905,7 @@ 18 |MONICA |GUY |K55 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 03"; + -- ************************************************************************* -- TESTUNIT : 04 -- DESCRIPTION : delete with SQ GB Having SQ on @@ -4763,6 +4918,7 @@ 20 rows inserted/updated/deleted ij> delete from db2test.dept; 2 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.secondemp; 20 rows inserted/updated/deleted ij> insert into db2test.dept select * from db2test.origdept; @@ -4836,6 +4992,7 @@ 18 |MONICA |GUY |K55 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 04"; + -- ************************************************************************* -- TESTUNIT : 05 -- DESCRIPTION : delete with SQ on child table correlated to SQ @@ -4848,6 +5005,8 @@ 20 rows inserted/updated/deleted ij> delete from db2test.dept; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.secondemp; 20 rows inserted/updated/deleted ij> insert into db2test.dept select * from db2test.origdept; @@ -4932,6 +5091,7 @@ 18 |MONICA |GUY |K55 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 05"; + -- ************************************************************************* -- TESTUNIT : 06 -- DESCRIPTION : delete with SQ on child table correlated to SQ @@ -4944,6 +5104,8 @@ 20 rows inserted/updated/deleted ij> delete from db2test.dept; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.secondemp; 20 rows inserted/updated/deleted ij> insert into db2test.dept select * from db2test.origdept; @@ -5047,6 +5209,8 @@ 15 |SAM2 |ROGER |NULL 7 |TRUONG |HAMID |NULL ij> -- "END OF TESTUNIT: 06"; + + -- ************************************************************************* -- TESTUNIT : 07 -- DESCRIPTION : delete with SQ on child table correlated to SQ @@ -5061,6 +5225,9 @@ 20 rows inserted/updated/deleted ij> delete from db2test.dept; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.secondemp; 20 rows inserted/updated/deleted ij> insert into db2test.dept select * from db2test.origdept; @@ -5159,6 +5326,7 @@ 15 |SAM2 |ROGER |NULL 7 |TRUONG |HAMID |NULL ij> -- "END OF TESTUNIT: 07"; + -- ************************************************************************* -- TESTUNIT : 08 -- DESCRIPTION : delete with SQ on child table correlated to SQ @@ -5175,6 +5343,9 @@ 20 rows inserted/updated/deleted ij> delete from db2test.dept; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.dept select * from db2test.origdept; 3 rows inserted/updated/deleted ij> insert into db2test.emp select * from db2test.origemp; @@ -5271,6 +5442,7 @@ 15 |SAM2 |ROGER |NULL 7 |TRUONG |HAMID |NULL ij> -- "END OF TESTUNIT: 08"; + -- ************************************************************************* -- TESTUNIT : 09 -- DESCRIPTION : delete on view with SQ GB Having SQ on @@ -5285,6 +5457,9 @@ 20 rows inserted/updated/deleted ij> delete from db2test.dept; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.secondemp; 20 rows inserted/updated/deleted ij> insert into db2test.dept select * from db2test.origdept; @@ -5385,6 +5560,7 @@ 15 |SAM2 |ROGER |NULL 7 |TRUONG |HAMID |NULL ij> -- "END OF TESTUNIT: 09"; + -- ************************************************************************* -- TESTUNIT : 10 -- DESCRIPTION : delete on iudt where SQ on @@ -5399,6 +5575,8 @@ 20 rows inserted/updated/deleted ij> delete from db2test.dept; 1 row inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.secondemp; 20 rows inserted/updated/deleted ij> insert into db2test.dept select * from db2test.origdept; @@ -5921,6 +6099,8 @@ 18 |MONICA |GUY |K55 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 10"; + + -- ************************************************************************* -- TESTUNIT : 11 -- DESCRIPTION : delete with many SQ levels correl'd on child tables @@ -6392,6 +6572,8 @@ 15 |SAM2 |ROGER |NULL 7 |TRUONG |HAMID |NULL ij> -- "END OF TESTUNIT: 11"; + + -- ************************************************************************* -- TESTUNIT : 12 -- DESCRIPTION : delete on view with union of 15 child tables @@ -6900,6 +7082,8 @@ 7 |TRUONG |HAMID |NULL ij> -- "END OF TESTUNIT: 12"; -- "cleanup"; + + drop view db2test.vempunion15; 0 rows inserted/updated/deleted ij> drop view db2test.vempjoin12; @@ -6966,7 +7150,9 @@ -- # : tables in the query by set null on delete. -- # : shape of the RI tree is a 6way star. -- ######################################################################### + -- "START OF TESTCASE: cself313.sql"; + autocommit off; ij> -- ************************************************************************* -- TESTUNIT : 01 @@ -7086,6 +7272,7 @@ ij> insert into db2test.emp6 select * from db2test.emp; 20 rows inserted/updated/deleted ij> -- "END OF TESTUNIT: 01"; + -- ************************************************************************* -- TESTUNIT : 02 -- DESCRIPTION : delete where SQ on child table on RI set null col @@ -7101,6 +7288,12 @@ where e3.dno in (select dno from db2test.emp2 e2 where mgrname = 'JOHN' and e3.name = e2.mgrname)); 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> select * from db2test.emp order by dno, name, mgrname; C0 |NAME |MGRNAME |DNO -------------------------------------- @@ -7217,6 +7410,7 @@ 18 |MONICA |GUY |K55 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 02"; + -- ************************************************************************* -- TESTUNIT : 03 -- DESCRIPTION : delete where SQ on child table on RI set null col @@ -7267,6 +7461,7 @@ where e3.dno in (select dno from db2test.emp2 e2 where mgrname = 'JOHN')); 10 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> select * from db2test.emp order by dno, name, mgrname; C0 |NAME |MGRNAME |DNO -------------------------------------- @@ -7373,6 +7568,7 @@ 18 |MONICA |NULL |K55 7 |TRUONG |NULL |K55 ij> -- "END OF TESTUNIT: 03"; + -- ************************************************************************* -- TESTUNIT : 04 -- DESCRIPTION : delete where SQ chain reversing RI child chain @@ -7382,8 +7578,10 @@ -- reset to original rows delete from db2test.dept; 3 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp; 10 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 20 rows inserted/updated/deleted ij> delete from db2test.emp3; @@ -7427,6 +7625,12 @@ where e1.name = db2test.emp.mgrname and e1.mgrname = 'JOHN'))))); 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> select * from db2test.emp order by dno, name, mgrname; C0 |NAME |MGRNAME |DNO -------------------------------------- @@ -7543,6 +7747,7 @@ 18 |MONICA |GUY |K55 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 04"; + -- ************************************************************************* -- TESTUNIT : 05 -- DESCRIPTION : delete where SQ chain reversing RI child chain @@ -7553,8 +7758,10 @@ -- reset to original rows delete from db2test.dept; 3 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp; 20 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 20 rows inserted/updated/deleted ij> delete from db2test.emp3; @@ -7605,6 +7812,11 @@ e2.dno in (select e1.dno from db2test.emp e1 where e1.name = e.mgrname and e1.mgrname = 'JOHN'))))); 8 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> select * from db2test.emp order by dno, name, mgrname; C0 |NAME |MGRNAME |DNO -------------------------------------- @@ -7713,6 +7925,8 @@ 18 |MONICA |GUY |K55 7 |TRUONG |HAMID |K55 ij> -- "END OF TESTUNIT: 05"; + + -- ************************************************************************* -- TESTUNIT : 06 -- DESCRIPTION : delete where SQ chain reversing RI child chain @@ -7723,8 +7937,10 @@ -- reset to original rows delete from db2test.dept; 3 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp; 12 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 20 rows inserted/updated/deleted ij> delete from db2test.emp3; @@ -7768,6 +7984,7 @@ e2.dno in (select e1.dno from db2test.emp e1 where e.mgrname = 'JOHN'))))); 2 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> select * from db2test.emp order by dno, name, mgrname; C0 |NAME |MGRNAME |DNO -------------------------------------- @@ -7882,6 +8099,7 @@ 18 |MONICA |NULL |K55 7 |TRUONG |NULL |K55 ij> -- "END OF TESTUNIT: 06"; + -- ************************************************************************* -- TESTUNIT : 07 -- DESCRIPTION : delete where SQ chain reversing RI child chain @@ -7892,8 +8110,10 @@ -- reset to original rows delete from db2test.dept; 3 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp; 18 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> delete from db2test.emp2; 20 rows inserted/updated/deleted ij> delete from db2test.emp3; @@ -7937,6 +8157,7 @@ e2.dno in (select dno from db2test.emp e1 where db2test.emp.mgrname = 'JOHN'))))); 2 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> select * from db2test.emp order by dno, name, mgrname; C0 |NAME |MGRNAME |DNO -------------------------------------- @@ -8052,6 +8273,7 @@ 7 |TRUONG |NULL |K55 ij> -- "END OF TESTUNIT: 07"; -- "cleanup"; + drop table db2test.emp6; 0 rows inserted/updated/deleted ij> drop table db2test.emp5; @@ -8072,6 +8294,7 @@ 0 rows inserted/updated/deleted ij> rollback; ij> -- "cself313.clp ENDED"; + -- ######################################################################### -- # TESTCASE NAME : cself314.sql -- # LINE ITEM : Self-referencing subqueries @@ -8087,7 +8310,9 @@ -- # : where the deleted table is connected to other -- # : tables in the query by set null on delete. -- # : shape of the RI tree is a 3-way cycle to parent + -- "START OF TESTCASE: cself314.sql"; + autocommit off; ij> -- ************************************************************************* -- TESTUNIT : 01 @@ -8173,6 +8398,7 @@ 0 rows inserted/updated/deleted ij> commit; ij> -- "END OF TESTUNIT: 01"; + -- ************************************************************************ -- TESTUNIT : 02 -- DESCRIPTION : delete where SQ on child table on RI set null col @@ -8245,6 +8471,7 @@ 0 rows inserted/updated/deleted ij> commit; ij> -- "END OF TESTUNIT: 02"; + -- ************************************************************************ -- TESTUNIT : 03 -- DESCRIPTION : same as 02, but with correlation to top table @@ -8277,6 +8504,8 @@ where e2.dno in (select dno from db2test.dept d where mgrname = 'john' and e2.name = d.dmgrname)); 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> select * from db2test.dept order by dno, dname, dmgrname; C0 |DNO|DNAME |DMGRNAME ------------------------------------- @@ -8333,6 +8562,7 @@ 0 rows inserted/updated/deleted ij> commit; ij> -- "END OF TESTUNIT: 03"; + -- ************************************************************************ -- TESTUNIT : 04 -- DESCRIPTION : same as 02 but SQ's reversed @@ -8369,6 +8599,8 @@ (select dno from db2test.emp2 e2 where e2.mgrname = 'john')); 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> select * from db2test.dept order by dno, dname, dmgrname; C0 |DNO|DNAME |DMGRNAME ------------------------------------- @@ -8425,6 +8657,7 @@ 0 rows inserted/updated/deleted ij> commit; ij> -- "END OF TESTUNIT: 04"; + -- ************************************************************************ -- TESTUNIT : 05 -- DESCRIPTION : same as 03 but SQ's reversed @@ -8461,6 +8694,8 @@ (select dno from db2test.emp2 e2 where e2.dno = d.dno and e2.mgrname = 'john')); 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> select * from db2test.dept order by dno, dname, dmgrname; C0 |DNO|DNAME |DMGRNAME ------------------------------------- @@ -8517,6 +8752,7 @@ 0 rows inserted/updated/deleted ij> commit; ij> -- "END OF TESTUNIT: 05"; + -- ************************************************************************ -- TESTUNIT : 06 -- DESCRIPTION : same as 05 but extra table @@ -8555,6 +8791,8 @@ (select dno from db2test.emp2 e2 where e2.dno = d.dno and e2.mgrname = 'john'))); 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> select * from db2test.dept order by dno, dname, dmgrname; C0 |DNO|DNAME |DMGRNAME ------------------------------------- @@ -8611,6 +8849,7 @@ 0 rows inserted/updated/deleted ij> commit; ij> -- "END OF TESTUNIT: 06"; + -- ************************************************************************* -- TESTUNIT : 07 -- DESCRIPTION : delete on parent with SQ on join view of children @@ -8624,6 +8863,7 @@ 20 rows inserted/updated/deleted ij> delete from db2test.dept; 3 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.dept select * from db2test.origdept; 3 rows inserted/updated/deleted ij> insert into db2test.emp select * from db2test.origemp; @@ -8705,6 +8945,7 @@ 0 rows inserted/updated/deleted ij> commit; ij> -- "END OF TESTUNIT: 07"; + -- ************************************************************************* -- TESTUNIT : 08 -- DESCRIPTION : delete on parent with SQ on union view of children @@ -8718,6 +8959,7 @@ 20 rows inserted/updated/deleted ij> delete from db2test.dept; 1 row inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> insert into db2test.dept select * from db2test.origdept; 3 rows inserted/updated/deleted ij> insert into db2test.emp select * from db2test.origemp; @@ -8743,6 +8985,7 @@ 2 |K52|OFC |ROBIN 1 |K55|DB |JOHN ij> -- #END; + delete from db2test.dept where dno in (select vdno from db2test.vempunion) and dno in ('K55', 'K52'); @@ -8820,5 +9063,6 @@ drop schema db2test restrict; 0 rows inserted/updated/deleted ij> -- END OF REFACTIONS1.sql -- + ; -ij> +ij> \ No newline at end of file Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out?view=diff&rev=498716&r1=498715&r2=498716 ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out Mon Jan 22 09:23:42 2007 @@ -15,6 +15,7 @@ -- limitations under the License. -- -- tests for synonym support + set schema APP; 0 rows inserted/updated/deleted ij> -- negative tests @@ -103,6 +104,7 @@ 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; I |J @@ -163,6 +165,7 @@ 2 |2 ij> close c1; ij> -- Try updatable cursors + autocommit off; ij> get cursor c2 as 'select * from myTable for update'; ij> next c2; @@ -223,13 +226,14 @@ 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. +create table foreignTab(i int, j int CONSTRAINT SYNPK_F references synPrimary(i)); +ERROR X0Y46: Constraint 'SYNPK_F' 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; @@ -287,6 +291,7 @@ 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; @@ -324,6 +329,7 @@ 1 row inserted/updated/deleted ij> delete from mySynonym where j=6; 0 rows inserted/updated/deleted +WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. ij> select * from mySynonym; I |J ----------------------- @@ -919,4 +925,4 @@ 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted -ij> +ij> \ No newline at end of file Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/tempRestrictions.out URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/tempRestrictions.out?view=diff&rev=498716&r1=498715&r2=498716 ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/tempRestrictions.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/tempRestrictions.out Mon Jan 22 09:23:42 2007 @@ -15,6 +15,7 @@ -- limitations under the License. -- -- Tests for temporary restrictions in the language + -- create a table create table t1(c1 int); 0 rows inserted/updated/deleted @@ -25,4 +26,4 @@ ij> -- drop the table drop table t1; 0 rows inserted/updated/deleted -ij> +ij> \ No newline at end of file Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?view=diff&rev=498716&r1=498715&r2=498716 ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall Mon Jan 22 09:23:42 2007 @@ -14,7 +14,6 @@ lang/bug4356.java lang/bug5052rts.java lang/bug5054.java -lang/cast.sql lang/casting.java lang/checkConstraint.sql lang/closed.java @@ -41,13 +40,10 @@ lang/distinctFiltering.sql lang/dropTable.sql lang/dynamicLikeOptimization.sql -lang/ejbql.sql -lang/ejbql_nonSPS.sql lang/emptyStatistics.sql lang/errorCode.sql lang/errorStream.java lang/fk_nonSPS.sql -lang/floattypes.sql lang/forbitdata.java lang/forupdate.sql lang/functions.sql @@ -94,7 +90,6 @@ lang/procedure.java lang/procedureInTrigger.sql lang/refActions.sql -lang/refActions1.sql lang/refActions2.sql lang/refActions3.sql lang/releaseCompileLocks.sql @@ -115,9 +110,7 @@ lang/subquery2.sql lang/subqueryFlattening.sql lang/supersimple.sql -lang/synonym.sql lang/syscat.sql -lang/tempRestrictions.sql lang/timestampArith.java lang/triggerBeforeTrig.sql lang/triggerGeneral.sql @@ -126,7 +119,6 @@ lang/triggerStream.java lang/unaryArithmeticDynamicParameter.java lang/ungroupedAggregatesNegative.sql -lang/unlimited.sql lang/updateCursor.java lang/wisconsin.java lang/ShutdownDatabase.java Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangScripts.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangScripts.java?view=diff&rev=498716&r1=498715&r2=498716 ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangScripts.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LangScripts.java Mon Jan 22 09:23:42 2007 @@ -24,6 +24,7 @@ import org.apache.derbyTesting.functionTests.util.ScriptTestCase; import org.apache.derbyTesting.junit.CleanDatabaseTestSetup; +import org.apache.derbyTesting.junit.JDBC; import org.apache.derbyTesting.junit.TestConfiguration; /** @@ -39,22 +40,20 @@ * */ public final class LangScripts extends ScriptTestCase { - - /** - * Language SQL scripts (.sql files) that run under all configurations. - */ - private static final String[] SQL_LANGUAGE_TESTS = { - "case", - "constantExpression", - }; /** * Language SQL scripts (.sql files) that run under Derby's client - * and emebdded configurations. + * and emebedded configurations. SQL tests are put in this category + * if they are likely to have some testing of the protocol, + * typically tests related to data types. + * */ - private static final String[] DERBY_TESTS = { + private static final String[] CLIENT_AND_EMBEDDED_TESTS = { + "LOB", + "bit", "bit2", - "derived", + "nulls", + "stringtypes", }; /** @@ -66,21 +65,33 @@ "aggbuiltin", "aggregate", "arithmetic", - "bit", + "case", "comparisons", + "constantExpression", "delete", + "derived", "depend", + "ejbql", + "floattypes", "insert", - "LOB", - "nulls", + "refActions1", "schemas", "select", - "stringtypes", + "synonym", + "tempRestrictions", "union", "update", "valuesclause", "views", - }; + }; + + /** + * Tests that run in embedded and require JDBC3_TESTS + * (ie. can not run on JSR169). + */ + private static final String[] JDBC3_TESTS = { + "cast", // DERBY-2228 (also never ran with J2ME with old harness) + }; /** * Run a set of language SQL scripts (.sql files) passed in on the @@ -102,14 +113,14 @@ public static Test suite() { TestSuite suite = new TestSuite("LangScripts"); - suite.addTest(getSuite(SQL_LANGUAGE_TESTS)); - suite.addTest(getSuite(DERBY_TESTS)); + suite.addTest(getSuite(CLIENT_AND_EMBEDDED_TESTS)); suite.addTest(getSuite(EMBEDDED_TESTS)); + if (JDBC.vmSupportsJDBC3()) + suite.addTest(getSuite(JDBC3_TESTS)); // Set up the scripts run with the network client TestSuite clientTests = new TestSuite("LangScripts:client"); - clientTests.addTest(getSuite(SQL_LANGUAGE_TESTS)); - clientTests.addTest(getSuite(DERBY_TESTS)); + clientTests.addTest(getSuite(CLIENT_AND_EMBEDDED_TESTS)); Test client = TestConfiguration.clientServerDecorator(clientTests); // add those client tests into the top-level suite. Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/floattypes.sql URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/floattypes.sql?view=diff&rev=498716&r1=498715&r2=498716 ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/floattypes.sql (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/floattypes.sql Mon Jan 22 09:23:42 2007 @@ -511,7 +511,10 @@ select -(d * 100 / 100e0 ), +(d * 100e0 / 100 ) from t; -- test null/null, constant/null, null/constant -select i, d, i / d, 10 / d, d / 10e0 from t; +-- Cast to decimal to remove a minor diff among +-- vms, where 0.005 converted to a String would be +-- either 0.005 or 0.0050 (both valid formats for Double.toString(double) +select i, d, CAST (i / d AS DECIMAL(6,5)), 10 / d, d / 10e0 from t; -- test for divide by 0 select d / i from t; @@ -799,8 +802,10 @@ select -(r * 100 / 100e0 ), +(r * 100e0 / 100 ) from t; -- test null/null, constant/null, null/constant -select i, d, r, d / r, i / r, 10 / r, r / d, r / 10e0 from t; - +-- Cast to decimal to remove a minor diff among +-- vms, where 0.005 converted to a String would be +-- either 0.005 or 0.0050 (both valid formats for Double.toString(double) +select i, d, r, d / r, CAST (i / r AS DECIMAL(6,5)), 10 / r, r / d, r / 10e0 from t; -- test for divide by 0 select r / i from t; Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql?view=diff&rev=498716&r1=498715&r2=498716 ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/synonym.sql Mon Jan 22 09:23:42 2007 @@ -150,7 +150,7 @@ create synonym synPrimary for primaryTab; -- Should fail -create table foreignTab(i int, j int references synPrimary(i)); +create table foreignTab(i int, j int CONSTRAINT SYNPK_F references synPrimary(i)); create table foreignTab(i int, j int references primaryTab(i));