You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Sylvain Leroux <sy...@chicoree.fr> on 2010/02/18 13:02:00 UTC

Updatable cursors and generated columns

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?


Thanks,
Sylvain

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

Re: Updatable cursors and generated columns

Posted by Sylvain Leroux <sy...@chicoree.fr>.
Reported as DERBY-4559

Knut Anders Hatlen a écrit :
> Sylvain Leroux <sy...@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

Re: Updatable cursors and generated columns

Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Sylvain Leroux <sy...@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...

-- 
Knut Anders