db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-481) implement SQL generated columns
Date Wed, 12 Nov 2008 14:51:44 GMT

    [ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12646915#action_12646915

Dag H. Wanvik commented on DERBY-481:

I am trying to understand if the following is allowed by the standard:
Assume this ij fragment:

> create table t (i int, j int, k int generated always as (-j));
> create function myfunc (i int) returns int language java external name 
>        'M.myfunc' parameter style java;
> insert into t values (1,2,default), (3,4,default);
> create trigger tr no cascade before update on t 
>        referencing new as new  for each row values myfunc(new.k);
> update t set j=10 where i=1;
> // myfunc prints -10 here

In the standard, section 11.39, SR 12 c) I see this provision:

   12) If BEFORE is specified, then:
     c) The <triggered action> shall not contain a <field reference> that
     references a field in the new transition variable corresponding to a
     generated column of T.

It would seem that the reference to k is illegal here. What do you
think? Not sure I understand why this should be prohibited, though....

> implement SQL generated columns
> -------------------------------
>                 Key: DERBY-481
>                 URL: https://issues.apache.org/jira/browse/DERBY-481
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>    Affects Versions:
>            Reporter: Rick Hillegas
>            Assignee: Rick Hillegas
>         Attachments: derby-481-00-aa-prototype.diff, derby-481-01-aa-catalog.diff, derby-481-02-aa-utilities.diff,
derby-481-03-aa-grammar.diff, derby-481-04-aa-insert.diff, derby-481-05-aa-update.diff, derby-481-06-aa-genreferences.diff,
derby-481-07-aa-noSQLinRoutines.diff, derby-481-07-ab-noSQLinRoutines.diff, derby-481-08-aa-castToDeclaredType.diff,
derby-481-09-aa-dummyDefaults.diff, derby-481-10-aa-foreignKeyActions.diff, derby-481-11-aa-notNull.diff,
derby-481-12-aa-padding.diff, derby-481-13-aa-alterDatatype.diff, derby-481-14-ab-dropColumn.diff,
derby-481-15-aa-renameAndAddDefault.diff, derby-481-16-aa-dropFunction.diff, GeneratedColumns.html
> Satheesh has pointed out that generated columns, a SQL 2003 feature, would satisfy the
performance requirements of Expression Indexes (bug 455). Generated columns may not be as
elegant as Expression Indexes, but they are easier to implement. We would allow the following
new kind of column definition in CREATE TABLE and ALTER TABLE statements:
>     columnName GENERATED ALWAYS AS ( expression )
> If expression were an indexableExpression (as defined in bug 455), then we could create
indexes on it. There is no work for the optimizer to do here. The Language merely has to compute
the generated column at INSERT/UPDATE time.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message