db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sylvain Leroux <sylv...@chicoree.fr>
Subject Re: Updatable cursors and generated columns
Date Sat, 20 Feb 2010 19:26:08 GMT
Reported as DERBY-4559

Knut Anders Hatlen a écrit :
> Sylvain Leroux <sylvain@chicoree.fr> writes:
> 
>> Hi,
>>
>>
>> By reading DERBY-1773 I had a question. Since it is only slightly
>> related with that JIRA issue, I prefer posting here - rather than
>> interfering with the corresponding discution.
>>
>>
>> How /should/ behave an updatable cursor on columns, when there is a
>> generated column on the table which is not marked FOR UPDATE, but
>> which is dependent of an updatable column.
>>
>> Here is an example:
>> CREATE TABLE T(COL1 INT, COL2 INT, COL3 GENERATED ALWAYS AS (COL1+COL2));
>> SELECT COL1 FROM T FOR UPDATE OF COL1;
>>
>> If I update 'COL1' from the above cursor, should COL3 be updated as
>> well? Or should an error be raised?
> 
> Hi Sylvain,
> 
> I would say that it should be allowed, and COL3 should automatically be
> updated, similar to what you'd get if you had an update trigger on COL1
> that updated COL3. But I see that that's not the case:
> 
> ij> insert into t(col1,col2) values (1,2),(3,4);
> 2 rows inserted/updated/deleted
> ij> get cursor c as 'SELECT COL1 FROM T FOR UPDATE OF COL1';
> ij> next c;
> COL1       
> -----------
> 1          
> ij> update t set col1 = 5 where current of c;
> ERROR 42X31: Column 'COL3' is not in the FOR UPDATE list of cursor 'C'.
> 
> Looks like a bug to me...
> 


-- 
sylvain@chicoree.fr
http://www.chicoree.fr



Mime
View raw message