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 Thu, 07 Apr 2005 06:06:35 GMT
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 ***
>>  
>>

Mime
View raw message