Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 24937 invoked from network); 20 Apr 2005 03:43:38 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 20 Apr 2005 03:43:38 -0000 Received: (qmail 83810 invoked by uid 500); 20 Apr 2005 03:43:37 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 83774 invoked by uid 500); 20 Apr 2005 03:43:37 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Development" Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 83760 invoked by uid 99); 20 Apr 2005 03:43:37 -0000 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests= X-Spam-Check-By: apache.org Received-SPF: pass (hermes.apache.org: local policy) Received: from brmea-mail-3.Sun.COM (HELO brmea-mail-3.sun.com) (192.18.98.34) by apache.org (qpsmtpd/0.28) with ESMTP; Tue, 19 Apr 2005 20:43:36 -0700 Received: from phys-biff-2 ([129.158.227.37]) by brmea-mail-3.sun.com (8.12.10/8.12.9) with ESMTP id j3K3hVjS009811 for ; Tue, 19 Apr 2005 21:43:34 -0600 (MDT) Received: from conversion-daemon.biff-mail1.india.sun.com by biff-mail1.india.sun.com (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) id <0IF800I016YWP9@biff-mail1.india.sun.com> (original mail from Shreyas.Kaushik@Sun.COM) for derby-dev@db.apache.org; Wed, 20 Apr 2005 09:13:31 +0530 (IST) Received: from [129.158.229.246] (lilly.India.Sun.COM [129.158.229.246]) by biff-mail1.india.sun.com (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) with ESMTP id <0IF800D0F7OI64@biff-mail1.india.sun.com> for derby-dev@db.apache.org; Wed, 20 Apr 2005 09:13:31 +0530 (IST) Date: Wed, 20 Apr 2005 09:10:35 +0530 From: Shreyas Kaushik Subject: Re: [PATCH] Derby-156 In-reply-to: <4263441B.9020908@sun.com> To: Derby Development Message-id: <4265CF33.4070700@Sun.com> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7BIT X-Accept-Language: en-us, en User-Agent: Mozilla Thunderbird 1.0 (X11/20041208) References: <42632E66.3020403@sun.com> <4263441B.9020908@sun.com> X-Virus-Checked: Checked X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Did anyone gfo through this? ~ Shreyas Shreyas Kaushik wrote: > Hi Mamta, > > Done, test case added in the latest patch attached. > > ~ Shreyas > > Mamta Satoor wrote: > >> Hi Shreyas, >> >> Unless I missed it, I don't see a test like the one in the Jira entry >> for this bug >> DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value'; This test case >> attempts to use the correlation name in the where clause. >> >> Mamta >> >> On 4/17/05, Shreyas Kaushik wrote: >> >> >>> I have attched the patch for this. I have also added test cases that >>> test this feature. Also I have made some changes to the refActions1 >>> test >>> case. >>> I ran the derbyall test suite without any failures. >>> >>> thanks >>> Shreyas >>> >>> >>> Index: java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java >>> =================================================================== >>> --- >>> java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java >>> (revision 161449) >>> +++ >>> java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java >>> (working copy) >>> @@ -241,6 +241,13 @@ >>> resultColumnList = new ResultColumnList(); >>> >>> FromBaseTable fbt = >>> getResultColumnList(resultColumnList); >>> + >>> + if(targetTable instanceof FromBaseTable) { >>> + String correlationName; >>> + correlationName = >>> ((FromBaseTable)targetTable).correlationName; >>> + if(correlationName != null) >>> + fbt.correlationName = correlationName; >>> + } >>> >>> readColsBitSet = getReadMap(dataDictionary, >>> >>> targetTableDescriptor); >>> Index: java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj >>> =================================================================== >>> --- java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj >>> (revision 161449) >>> +++ java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj >>> (working copy) >>> @@ -2565,16 +2565,22 @@ >>> QueryTreeNode retval; >>> Properties targetProperties = null; >>> Token whereToken = null; >>> + String correlationName = null; >>> + Object []objArr = null; >>> } >>> { >>> LOOKAHEAD( { fromNewInvocationFollows() } ) >>> javaToSQLNode = newInvocation() >>> + { >>> + objArr = optionalTableClauses(); >>> + correlationName = >>> (String)objArr[OPTIONAL_TABLE_CLAUSES_CORRELATION_NAME]; >>> + } >>> [ whereToken = whereClause = whereClause(whereToken) ] >>> { >>> fromTable = (FromTable) nodeFactory.getNode( >>> >>> C_NodeTypes.FROM_VTI, >>> >>> javaToSQLNode.getJavaValueNode(), >>> - >>> (String) null, >>> + >>> correlationName, >>> >>> null, >>> >>> (Properties) null, >>> >>> getContextManager()); >>> @@ -2582,7 +2588,11 @@ >>> return getDeleteNode(fromTable, tableName, whereClause); >>> } >>> | >>> - tableName = >>> qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128) >>> + tableName = >>> qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128) >>> + { >>> + objArr = optionalTableClauses(); >>> + correlationName = >>> (String)objArr[OPTIONAL_TABLE_CLAUSES_CORRELATION_NAME]; >>> + } >>> [targetProperties = propertyList() ] >>> [ >>> whereToken = >>> @@ -2614,7 +2624,7 @@ >>> fromTable = (FromTable) nodeFactory.getNode( >>> >>> C_NodeTypes.FROM_BASE_TABLE, >>> >>> tableName, >>> - >>> null, >>> + >>> correlationName, >>> >>> ReuseFactory.getInteger( >>> >>> FromBaseTable.DELETE), >>> >>> null, >>> Index: >>> java/testing/org/apache/derbyTesting/functionTests/tests/lang/corrDelete.sql >>> >>> =================================================================== >>> --- >>> java/testing/org/apache/derbyTesting/functionTests/tests/lang/corrDelete.sql >>> (revision 0) >>> +++ >>> java/testing/org/apache/derbyTesting/functionTests/tests/lang/corrDelete.sql >>> (revision 0) >>> @@ -0,0 +1,23 @@ >>> +-- This tests the delete functionality with correlation name >>> + >>> +create table corrDelete(ival int, cval varchar(10)); >>> +insert into corrDelete values(1,'test1'); >>> +insert into corrDelete values(2,'test2'); >>> +insert into corrDelete values(3,'test3'); >>> +insert into corrDelete values(4,'test4'); >>> +insert into corrDelete values(5,'test5'); >>> +insert into corrDelete values(6,'test6'); >>> + >>> +select * from corrDelete; >>> + >>> +delete from corrDelete d where ival=3; >>> + >>> +select * from corrDelete; >>> + >>> +delete from corrDelete as d where ival=5; >>> + >>> +select * from corrDelete; >>> + >>> +delete from corrDelete d; >>> + >>> +select * from corrDelete; >>> Index: >>> java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql >>> >>> =================================================================== >>> --- >>> java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql >>> (revision 161449) >>> +++ >>> java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql >>> (working copy) >>> @@ -719,6 +719,14 @@ >>> select * from db2test.emp13 order by dno, name, mgrname; >>> select * from db2test.emp14 order by dno, name, mgrname; >>> select * from db2test.emp15 order by dno, name, mgrname; >>> +delete from db2test.dept d where >>> + dno in (select dno from db2test.emp e where >>> + e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where >>> + e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where >>> + e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where >>> + e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where >>> + e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where >>> + e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))); >>> -- "END OF TESTUNIT: 11"; >>> >>> @@ -2306,14 +2314,6 @@ >>> e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where >>> e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))) >>> order by 2, 3; >>> -delete from db2test.dept d where >>> - dno in (select dno from db2test.emp e where >>> - e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where >>> - e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where >>> - e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where >>> - e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where >>> - e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where >>> - e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))); >>> select * from db2test.dept order by dno, dname; >>> select * from db2test.emp order by dno, name, mgrname; >>> select * from db2test.secondemp order by dno, name, mgrname; >>> Index: >>> java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant >>> >>> =================================================================== >>> --- >>> java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant >>> (revision 161449) >>> +++ >>> java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant >>> (working copy) >>> @@ -209,3 +209,4 @@ >>> wisconsin_app.properties >>> wisconsin_derby.properties >>> wisconsin_sed.properties >>> +corrDelete.sql >>> Index: >>> java/testing/org/apache/derbyTesting/functionTests/master/corrDelete.out >>> >>> =================================================================== >>> --- >>> java/testing/org/apache/derbyTesting/functionTests/master/corrDelete.out >>> (revision 0) >>> +++ >>> java/testing/org/apache/derbyTesting/functionTests/master/corrDelete.out >>> (revision 0) >>> @@ -0,0 +1,49 @@ >>> +ij> -- This tests the delete functionality with correlation name >>> +create table corrDelete(ival int, cval varchar(10)); >>> +0 rows inserted/updated/deleted >>> +ij> insert into corrDelete values(1,'test1'); >>> +1 row inserted/updated/deleted >>> +ij> insert into corrDelete values(2,'test2'); >>> +1 row inserted/updated/deleted >>> +ij> insert into corrDelete values(3,'test3'); >>> +1 row inserted/updated/deleted >>> +ij> insert into corrDelete values(4,'test4'); >>> +1 row inserted/updated/deleted >>> +ij> insert into corrDelete values(5,'test5'); >>> +1 row inserted/updated/deleted >>> +ij> insert into corrDelete values(6,'test6'); >>> +1 row inserted/updated/deleted >>> +ij> select * from corrDelete; >>> +IVAL |CVAL >>> +---------------------- >>> +1 |test1 >>> +2 |test2 >>> +3 |test3 >>> +4 |test4 >>> +5 |test5 >>> +6 |test6 >>> +ij> delete from corrDelete d where ival=3; >>> +1 row inserted/updated/deleted >>> +ij> select * from corrDelete; >>> +IVAL |CVAL >>> +---------------------- >>> +1 |test1 >>> +2 |test2 >>> +4 |test4 >>> +5 |test5 >>> +6 |test6 >>> +ij> delete from corrDelete as d where ival=5; >>> +1 row inserted/updated/deleted >>> +ij> select * from corrDelete; >>> +IVAL |CVAL >>> +---------------------- >>> +1 |test1 >>> +2 |test2 >>> +4 |test4 >>> +6 |test6 >>> +ij> delete from corrDelete d; >>> +4 rows inserted/updated/deleted >>> +ij> select * from corrDelete; >>> +IVAL |CVAL >>> +---------------------- >>> +ij> >>> Index: >>> java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out >>> >>> =================================================================== >>> --- >>> java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out >>> (revision 161449) >>> +++ >>> java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out >>> (working copy) >>> @@ -1733,6 +1733,15 @@ >>> 5 |JOE2 |ASHOK |K51 >>> 2 |JOHN |ASHOK |K51 >>> 3 |ROBIN |ASHOK |K51 >>> +ij> delete from db2test.dept d where >>> + dno in (select dno from db2test.emp e where >>> + e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where >>> + e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where >>> + e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where >>> + e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where >>> + e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where >>> + e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))); >>> +0 rows inserted/updated/deleted >>> ij> -- "END OF TESTUNIT: 11"; >>> -- >>> ************************************************************************* >>> >>> -- TESTUNIT : 12 >>> @@ -5998,15 +6007,6 @@ >>> -------------------------- >>> 2 |K52|OFC >>> 1 |K55|DB >>> -ij> delete from db2test.dept d where >>> - dno in (select dno from db2test.emp e where >>> - e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where >>> - e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where >>> - e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where >>> - e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where >>> - e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where >>> - e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))); >>> -ERROR 42X01: Syntax error: Encountered "d" at line 1, column 26. >>> ij> select * from db2test.dept order by dno, dname; >>> C0 |DNO|DNAME >>> -------------------------- >>> @@ -7585,7 +7585,7 @@ >>> where e3.name = e2.mgrname group by dno having >>> e2.dno in (select dno from db2test.emp e1 >>> where e1.name = e.mgrname and e1.mgrname = >>> 'JOHN'))))); >>> -ERROR 42X01: Syntax error: Encountered "e" at line 1, column 25. >>> +ERROR 42X04: Column 'E5.NAME' is not in any table in the FROM list >>> or it appears within a join specification and is outside the scope >>> of the join specification or it appears in a HAVING clause and is >>> not in the GROUP BY list. If this is a CREATE or ALTER TABLE >>> statement then 'E5.NAME' is not a column in the target table. >>> ij> select * from db2test.emp order by dno, name, mgrname; >>> C0 |NAME |MGRNAME |DNO >>> -------------------------------------- >>> @@ -7753,7 +7753,7 @@ >>> where e.name = e2.mgrname group by dno having >>> e2.dno in (select dno from db2test.emp e1 >>> where e.mgrname = 'JOHN'))))); >>> -ERROR 42X01: Syntax error: Encountered "e" at line 1, column 25. >>> +ERROR 42X04: Column 'E.NAME' is not in any table in the FROM list >>> or it appears within a join specification and is outside the scope >>> of the join specification or it appears in a HAVING clause and is >>> not in the GROUP BY list. If this is a CREATE or ALTER TABLE >>> statement then 'E.NAME' is not a column in the target table. >>> ij> select * from db2test.emp order by dno, name, mgrname; >>> C0 |NAME |MGRNAME |DNO >>> -------------------------------------- >>> >>> >>> >>> >> >------------------------------------------------------------------------ > >Index: java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java >=================================================================== >--- java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java (revision 161449) >+++ java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java (working copy) >@@ -241,6 +241,13 @@ > resultColumnList = new ResultColumnList(); > > FromBaseTable fbt = getResultColumnList(resultColumnList); >+ >+ if(targetTable instanceof FromBaseTable) { >+ String correlationName; >+ correlationName = ((FromBaseTable)targetTable).correlationName; >+ if(correlationName != null) >+ fbt.correlationName = correlationName; >+ } > > readColsBitSet = getReadMap(dataDictionary, > targetTableDescriptor); >Index: java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj >=================================================================== >--- java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (revision 161449) >+++ java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (working copy) >@@ -2565,16 +2565,22 @@ > QueryTreeNode retval; > Properties targetProperties = null; > Token whereToken = null; >+ String correlationName = null; >+ Object []objArr = null; > } > { > LOOKAHEAD( { fromNewInvocationFollows() } ) > javaToSQLNode = newInvocation() >+ { >+ objArr = optionalTableClauses(); >+ correlationName = (String)objArr[OPTIONAL_TABLE_CLAUSES_CORRELATION_NAME]; >+ } > [ whereToken = whereClause = whereClause(whereToken) ] > { > fromTable = (FromTable) nodeFactory.getNode( > C_NodeTypes.FROM_VTI, > javaToSQLNode.getJavaValueNode(), >- (String) null, >+ correlationName, > null, > (Properties) null, > getContextManager()); >@@ -2582,7 +2588,11 @@ > return getDeleteNode(fromTable, tableName, whereClause); > } > | >- tableName = qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128) >+ tableName = qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128) >+ { >+ objArr = optionalTableClauses(); >+ correlationName = (String)objArr[OPTIONAL_TABLE_CLAUSES_CORRELATION_NAME]; >+ } > [targetProperties = propertyList() ] > [ > whereToken = >@@ -2614,7 +2624,7 @@ > fromTable = (FromTable) nodeFactory.getNode( > C_NodeTypes.FROM_BASE_TABLE, > tableName, >- null, >+ correlationName, > ReuseFactory.getInteger( > FromBaseTable.DELETE), > null, >Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/corrDelete.sql >=================================================================== >--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/corrDelete.sql (revision 0) >+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/corrDelete.sql (revision 0) >@@ -0,0 +1,23 @@ >+-- This tests the delete functionality with correlation name >+ >+create table corrDelete(ival int, cval varchar(10)); >+insert into corrDelete values(1,'test1'); >+insert into corrDelete values(2,'test2'); >+insert into corrDelete values(3,'test3'); >+insert into corrDelete values(4,'test4'); >+insert into corrDelete values(5,'test5'); >+insert into corrDelete values(6,'test6'); >+ >+select * from corrDelete; >+ >+delete from corrDelete d where ival=3; >+ >+select * from corrDelete; >+ >+delete from corrDelete as d where d.ival=5; >+ >+select * from corrDelete; >+ >+delete from corrDelete d; >+ >+select * from corrDelete; >Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql >=================================================================== >--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql (revision 161449) >+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql (working copy) >@@ -719,6 +719,14 @@ > select * from db2test.emp13 order by dno, name, mgrname; > select * from db2test.emp14 order by dno, name, mgrname; > select * from db2test.emp15 order by dno, name, mgrname; >+delete from db2test.dept d where >+ dno in (select dno from db2test.emp e where >+ e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where >+ e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where >+ e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where >+ e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where >+ e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where >+ e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))); > -- "END OF TESTUNIT: 11"; > > >@@ -2306,14 +2314,6 @@ > e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where > e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))) > order by 2, 3; >-delete from db2test.dept d where >- dno in (select dno from db2test.emp e where >- e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where >- e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where >- e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where >- e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where >- e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where >- e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))); > select * from db2test.dept order by dno, dname; > select * from db2test.emp order by dno, name, mgrname; > select * from db2test.secondemp order by dno, name, mgrname; >Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant >=================================================================== >--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant (revision 161449) >+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant (working copy) >@@ -209,3 +209,4 @@ > wisconsin_app.properties > wisconsin_derby.properties > wisconsin_sed.properties >+corrDelete.sql >Index: java/testing/org/apache/derbyTesting/functionTests/master/corrDelete.out >=================================================================== >--- java/testing/org/apache/derbyTesting/functionTests/master/corrDelete.out (revision 0) >+++ java/testing/org/apache/derbyTesting/functionTests/master/corrDelete.out (revision 0) >@@ -0,0 +1,49 @@ >+ij> -- This tests the delete functionality with correlation name >+create table corrDelete(ival int, cval varchar(10)); >+0 rows inserted/updated/deleted >+ij> insert into corrDelete values(1,'test1'); >+1 row inserted/updated/deleted >+ij> insert into corrDelete values(2,'test2'); >+1 row inserted/updated/deleted >+ij> insert into corrDelete values(3,'test3'); >+1 row inserted/updated/deleted >+ij> insert into corrDelete values(4,'test4'); >+1 row inserted/updated/deleted >+ij> insert into corrDelete values(5,'test5'); >+1 row inserted/updated/deleted >+ij> insert into corrDelete values(6,'test6'); >+1 row inserted/updated/deleted >+ij> select * from corrDelete; >+IVAL |CVAL >+---------------------- >+1 |test1 >+2 |test2 >+3 |test3 >+4 |test4 >+5 |test5 >+6 |test6 >+ij> delete from corrDelete d where ival=3; >+1 row inserted/updated/deleted >+ij> select * from corrDelete; >+IVAL |CVAL >+---------------------- >+1 |test1 >+2 |test2 >+4 |test4 >+5 |test5 >+6 |test6 >+ij> delete from corrDelete as d where d.ival=5; >+1 row inserted/updated/deleted >+ij> select * from corrDelete; >+IVAL |CVAL >+---------------------- >+1 |test1 >+2 |test2 >+4 |test4 >+6 |test6 >+ij> delete from corrDelete d; >+4 rows inserted/updated/deleted >+ij> select * from corrDelete; >+IVAL |CVAL >+---------------------- >+ij> >Index: java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out >=================================================================== >--- java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out (revision 161449) >+++ java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out (working copy) >@@ -1733,6 +1733,15 @@ > 5 |JOE2 |ASHOK |K51 > 2 |JOHN |ASHOK |K51 > 3 |ROBIN |ASHOK |K51 >+ij> delete from db2test.dept d where >+ dno in (select dno from db2test.emp e where >+ e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where >+ e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where >+ e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where >+ e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where >+ e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where >+ e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))); >+0 rows inserted/updated/deleted > ij> -- "END OF TESTUNIT: 11"; > -- ************************************************************************* > -- TESTUNIT : 12 >@@ -5998,15 +6007,6 @@ > -------------------------- > 2 |K52|OFC > 1 |K55|DB >-ij> delete from db2test.dept d where >- dno in (select dno from db2test.emp e where >- e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where >- e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where >- e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where >- e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where >- e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where >- e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))); >-ERROR 42X01: Syntax error: Encountered "d" at line 1, column 26. > ij> select * from db2test.dept order by dno, dname; > C0 |DNO|DNAME > -------------------------- >@@ -7585,7 +7585,7 @@ > where e3.name = e2.mgrname group by dno having > e2.dno in (select dno from db2test.emp e1 > where e1.name = e.mgrname and e1.mgrname = 'JOHN'))))); >-ERROR 42X01: Syntax error: Encountered "e" at line 1, column 25. >+ERROR 42X04: Column 'E5.NAME' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'E5.NAME' is not a column in the target table. > ij> select * from db2test.emp order by dno, name, mgrname; > C0 |NAME |MGRNAME |DNO > -------------------------------------- >@@ -7753,7 +7753,7 @@ > where e.name = e2.mgrname group by dno having > e2.dno in (select dno from db2test.emp e1 > where e.mgrname = 'JOHN'))))); >-ERROR 42X01: Syntax error: Encountered "e" at line 1, column 25. >+ERROR 42X04: Column 'E.NAME' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'E.NAME' is not a column in the target table. > ij> select * from db2test.emp order by dno, name, mgrname; > C0 |NAME |MGRNAME |DNO > -------------------------------------- > >