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 ***
