Can somebody reply on this?
~ Shreyas
Shreyas Kaushik wrote:
Did any1 have a chance to look at this?
~ Shreyas
Shreyas Kaushik wrote:
I did some changes to sqlgrammar.jj file to make a fix for this.
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 ***
