db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Updatable cursors and generated columns
Date Fri, 19 Feb 2010 11:20:39 GMT
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...

-- 
Knut Anders

Mime
View raw message