With the patch , the following cases are taken care :
1) delete from <tablename> <correlationName> where <correlatioName>.<columnName> = <value>
2) delete from <tablename> AS <correlationName> where <correlatioName>.<columnName> = <value>
But when I ran derbyall I had a couple of failures. I am attaching the test failure diffs here as well.
Please comment.
~ Shreyas
Shreyas Kaushik wrote:
As this is a new feature and also involves changes the way SQL commands for delete are parsed ( we need to support both with and without AS keyword ) can someone give some pointers for starting. I have previously worked in solving some correlation name bugs for Derby , this would be a good one to dive in more deep.
~ Shreyas
Shreyas Kaushik wrote:
Yes, this definitely a good feature add.
thanks Shreyas
Jack Klebanoff (JIRA) wrote:
[ http://issues.apache.org/jira/browse/DERBY-156?page=comments#action_60015 ]
Jack Klebanoff commented on DERBY-156:
--------------------------------------
The Derby reference manual does not document the use of correlation names in DELETE statements, so this is a feature request not a bug.
The SQL 2003 spec does allow correlation names in delete statements, so this is a very reasonable request.
SQL allows an optional 'AS' in front of the correlation name:
<delete statement: searched> ::= DELETE FROM <target table> [ [ AS ] <correlation name> ] [ WHERE <search condition> ]
If we do add this to Derby we should allow the optional "AS".
Delete with alias on column fails ---------------------------------
Key: DERBY-156
URL: http://issues.apache.org/jira/browse/DERBY-156
Project: Derby
Type: Bug
Reporter: Bernd Ruehlicke
Priority: Critical
DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value';
fails with ERROR 42X01: Syntax error: Encountered "x" at line 1, column 24
This is the core of the problem. I found it form a more complicated statement but it cooks down to that this should work but dose not.
B-)
Index: java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (revision
159559)
+++ java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (working copy)
@@ -2552,16 +2552,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());
@@ -2569,7 +2575,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>
@@ -2601,7 +2611,7 @@
fromTable = (FromTable) nodeFactory.getNode(
C_NodeTypes.FROM_BASE_TABLE,
tableName,
-
null,
+
correlationName,
ReuseFactory.getInteger(
FromBaseTable.DELETE),
null,
*** Start: floattypes jdk1.5.0 derbyall:derbylang 2005-04-04 15:30:58 *** 152,165d151 < ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(31,0). < ij> select * from tmp; < D < -------------------------------- < 100000000000000000000000000000 < ij> drop table tmp; < 0 rows inserted/updated/deleted < ij> -- try inserting various types into decimal. < -- we expect silent truncation of the fraction < drop table tmp; < ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TMP' because it does not exist. < ij> create table tmp (d decimal(5,2)); < 0 rows inserted/updated/deleted < ij> insert into tmp values (100); 166a153,167 > ij> select * from tmp; > D > -------------------------------- > 100000000000000000000000000000 > 1797690000000000000000000000000& > ij> drop table tmp; > 0 rows inserted/updated/deleted > ij> -- try inserting various types into decimal. > -- we expect silent truncation of the fraction > drop table tmp; > ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TMP' because it does not > exist. > ij> create table tmp (d decimal(5,2)); > 0 rows inserted/updated/deleted > ij> insert into tmp values (100); > 1 row inserted/updated/deleted Test Failed. *** End: floattypes jdk1.5.0 derbyall:derbylang 2005-04-04 15:31:41 ***
*** Start: refActions1 jdk1.5.0 derbyall:derbylang 2005-04-04 15:53:53 *** 6009 del < ERROR 42X01: Syntax error: Encountered "d" at line 1, column 26. 6009a6009 > ERROR 42X04: Column 'DB2TEST.DEPT.DNO' 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 > 'DB2TEST.DEPT.DNO' is not a column in the target table. 7588 del < ERROR 42X01: Syntax error: Encountered "e" at line 1, column 25. 7588a7588 > ERROR 42X04: Column 'DB2TEST.EMP.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 > 'DB2TEST.EMP.NAME' is not a column in the target table. 7756 del < ERROR 42X01: Syntax error: Encountered "e" at line 1, column 25. 7756a7756 > ERROR 42X04: Column 'DB2TEST.EMP.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 > 'DB2TEST.EMP.NAME' is not a column in the target table. Test Failed. *** End: refActions1 jdk1.5.0 derbyall:derbylang 2005-04-04 15:57:14 ***
