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 <[EMAIL PROTECTED]> 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
> --------------------------------------
>
>
>
