db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: [PATCH] Derby-156
Date Mon, 18 Apr 2005 05:04:06 GMT
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