db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Shreyas Kaushik <Shreyas.Kaus...@Sun.COM>
Subject Re: [jira] Commented: (DERBY-156) Delete with alias on column fails
Date Tue, 05 Apr 2005 15:25:05 GMT
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 ***
>  
>

Mime
View raw message