Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <616025925.1226537984369.JavaMail.jira@brutus>
Date: Wed, 12 Nov 2008 16:59:44 -0800 (PST)
From: "Dag H. Wanvik (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-481) implement SQL generated columns
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Dag H. Wanvik commented on DERBY-481:
-------------------------------------

Is this the right behavior? It seems a bit non-intuitive to require the
result set inserted to have two columns when only one is used anyway...

ij> create table a(i int, j int generated always as (-i));
0 rows inserted/updated/deleted
ij> create table b(i int, j int);
0 rows inserted/updated/deleted
ij> insert into b values  (1,1);
1 row inserted/updated/deleted
ij> insert into a select * from b;
1 row inserted/updated/deleted
ij> select * from a;
I          |J          
-----------------------
1          |-1         

1 row selected
ij> insert into a select i from b;
ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns.
ij> insert into a select i,0 from b;
1 row inserted/updated/deleted
ij> select * from a;
I          |J          
-----------------------
1          |-1         
1          |-1         

2 rows selected

> 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: 10.0.2.1
>            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.