[DERBY-481] implement SQL generated columns

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.0.2.1
• Fix Version/s: 10.5.1.1
• Component/s: SQL
• Issue & fix info: Patch Available
• Assignee: Richard N. Hillegas
• Reporter: Richard N. Hillegas
• Created: 30/Jul/05 10:01
• Updated: 02/May/13 02:29
• Resolved: 20/Jan/09 21:17

Description

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.

Issue Links

• incorporates: DERBY-3945 Generation clauses which mention user-coded functions may produce different resuls depending on who performs the triggering INSERT/UPDATE
• incorporates: DERBY-3948 Don't allow references to generated columns in the NEW transition variables of triggered actions of BEFORE triggers
• incorporates: DERBY-3949 If a table owner loses a permission needed to run a function in a generation clause of one of her tables, then her table should be dropped.
• incorporates: DERBY-3950 You should get an error if you try to override a generated column via an INSERT driven by an inner ResultSet
• incorporates: DERBY-3957 Generated columns are not recalculated when you update a column through an updatable ResultSet.
• incorporates: DERBY-3964 NullPointerException when re-evaluating generated column during ON DELETE SET NULL referential action
• incorporates: DERBY-3969 NPE if you declare a constraint on a generated column and omit the datatype
• incorporates: DERBY-3570 Implement DETERMINISTIC keyword for procedures and functions
• incorporates: DERBY-3932 Add tests of generated columns when sql authorization is turned on
• incorporates: DERBY-3922 Enable the adding of generated columns via ALTER TABLE
• incorporates: DERBY-3923 Make the datatype optional when declaring columns with generation clauses
• is depended upon by: DERBY-3959 Reference Manual needs information on generated columns
• is related to: DERBY-3940 Dropping a column does not drop triggers which mention that column
• is related to: DERBY-455 Add support for creating index on expressions
• is related to: DERBY-4831 Support for auto-calculated (virtual) columns
• is related to: DERBY-1257 Enhance Derby optimizer and execution model to support effective use of multiple indexes on one table. Derby currently can use one index on a table and there are many situations where using multiple indexes on a table could improve performance significant
• relates to: DERBY-4142 java.lang.VerifyError causing java.sql.SQLException: Cannot create an instance of generated class ... in lang.GeneratedColumnsTest and GeneratedColumnsPermsTest on IBM iseries
• relates to: DERBY-1748 Global case insensitive setting
• relates to: DERBY-3959 Reference Manual needs information on generated columns