db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-481) implement SQL generated columns
Date Wed, 19 Mar 2008 17:22:25 GMT

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

Rick Hillegas commented on DERBY-481:

This feature was introduced to the SQL Standard in 2003. There it is called T175. Here is
some information on what other databases do:

DB2 - Appears to implement the full language for T175.

Oracle - Does not appear to implement T175. However, some similar functionality is available.
"Although Oracle does not support generated columns, a function-based index can be used to
index on the result of an expression" according to http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_standard_sql004.htm

Postgres - Does not appear to implement T175. However, according to section 5.2 of the Postgres
8.3 reference manual, it appears that you can declare the DEFAULT value of a column to be
computed by an expression which can contain functions.

MySQL - According to section 12.1.10 of the MySQL 6.0 reference manual, MySQL only allows
constants and CURRENT_TIMESTAMP as default values for columns.

> 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
>         Attachments: 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