db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Shreyas Kaushik <Shreyas.Kaus...@Sun.COM>
Subject Re: [PATCH] Derby-156
Date Mon, 18 Apr 2005 05:22:35 GMT
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 <Shreyas.Kaushik@sun.com> 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() } )
>>       <FROM> javaToSQLNode = newInvocation()
>>+        {
>>+           objArr = optionalTableClauses();
>>+           correlationName = (String)objArr[OPTIONAL_TABLE_CLAUSES_CORRELATION_NAME];
>>+        }
>>       [ whereToken = <WHERE> 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);
>>       }
>>|
>>-       <FROM> tableName = qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128)
>>+       <FROM> tableName = qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128)
>>+        {
>>+           objArr = optionalTableClauses();
>>+           correlationName = (String)objArr[OPTIONAL_TABLE_CLAUSES_CORRELATION_NAME];
>>+        }
>>           [targetProperties = propertyList() ]
>>               [
>>                       whereToken = <WHERE>
>>@@ -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
>>--------------------------------------
>>
>>
>>
>>    
>>

Mime
View raw message