[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12658654#action_12658654 ] Rick Hillegas commented on DERBY-481: - Hi Dag. I think your interpretation is reasonable. > 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, derby-481-17-aa-currentSchema.diff, > derby-481-18-aa-updatableResultSets.diff, derby-481-19-aa-cleanup.diff, > derby-481-20-aa-cleanup.diff, GeneratedColumns.html, GeneratedColumns.html, > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12658604#action_12658604 ] Dag H. Wanvik commented on DERBY-481: - A possible answer may be that a column level constraints is defined as *equivalent* to table level constraints in section 11.4 SR 12. Since this is a syntactic rule, I guess we could argue that the standard sees column level constraints as a syntactic variation of a table level constraint, in which case the RESTRICT behavior discussed above would apply to it as well. What do you think? > 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, derby-481-17-aa-currentSchema.diff, > derby-481-18-aa-updatableResultSets.diff, derby-481-19-aa-cleanup.diff, > derby-481-20-aa-cleanup.diff, GeneratedColumns.html, GeneratedColumns.html, > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12658603#action_12658603 ] Dag H. Wanvik commented on DERBY-481: - Hmm indeed puzzling that distinction about the table vs column level constraint. So, if it is a table level constraint, we do not restrict in the single column case? > 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, derby-481-17-aa-currentSchema.diff, > derby-481-18-aa-updatableResultSets.diff, derby-481-19-aa-cleanup.diff, > derby-481-20-aa-cleanup.diff, GeneratedColumns.html, GeneratedColumns.html, > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12652147#action_12652147 ] Rick Hillegas commented on DERBY-481: - Hi Dag, Thanks for your second batch of comments on November 26. I agree that it seems silly for RESTRICT to prevent you from dropping a column which is constrained by a CHECK constraint that only mentions that column. I'm not sure that the language of section 11.18 addresses this silliness, though. In the example from our test code, the CHECK constraint seems to me to be a column level CHECK constraint, not a table level constraint--so the exception in syntax rule (5b) doesn't apply. This is puzzling though. I don't know why the CHECK constraint would block a RESTRICTed DROP when phrased at the column level but would not block the DROP when phrased at the table level. > 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, derby-481-17-aa-currentSchema.diff, > derby-481-18-aa-updatableResultSets.diff, derby-481-19-aa-cleanup.diff, > derby-481-20-aa-cleanup.diff, GeneratedColumns.html, GeneratedColumns.html, > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[
https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12651231#action_12651231
]
Dag H. Wanvik commented on DERBY-481:
-
Reviewed derby-481-14-ab-dropColumn. Thanks again Rick! The patch is
good to commit. My notes:
* AlterTableConstantAction.java:
- The following fragment threw me off at first:
> int sizeAfterCascadedDrops = td.getColumnDescriptorList().size() -
> cascadedDrops;
> // can NOT drop a column if it is the only one in the table
> if (sizeAfterCascadedDrops == 1)
Surely,, since we are talkign about size after cascaded drops, th test
should be against 0? But no, the quantity sizeAfterCascadedDrops does
not include the original dropped column, so the code works, but is a
bit misleading. Maybe do:
int sizeAfterCascadedDrops = td.getColumnDescriptorList().size() - 1 -
cascadedDrops;
// Can not drop a column if it there would be no columns left:
if (sizeAfterCascadedDrops == 0)
- Maybe it would be informative to add a comment on the recursive call
that it never exceeds 2 in depth since generated columns don't
reference other generated columns.
- The new error message LANG_CASCADED_GEN_COL_DROP doesn't appear to be
used in the patch?
Is the intention that it be used in this exception?
throw StandardException.newException(
SQLState.LANG_PROVIDER_HAS_DEPENDENT_OBJECT,
dm.getActionString(DependencyManager.DROP_COLUMN),
"THE *LAST* COLUMN " + columnName,
"TABLE",
td.getQualifiedName() );
The new tests use LANG_PROVIDER_HAS_DEPENDENT_OBJECT (X0Y25), so
perhaps LANG_CASCADED_GEN_COL_DROP is cruft?
The wording of X0Y25 doesn't really precisely describe the problem,
though, so a new error message would be nice, I think...
- The string "THE *LAST* COLUMN " is not i18n safe.
- If generated column were to be dropped indirectly, columnName could
not *appear* to the user to be THE *LAST* COLUMN; I think the error
should point out in such cases that, after cascade action, we would
have 0 columns left. Perhaps the wording can be word-smithed to
handle both cases well.
- The error message wording of LANG_CASCADED_GEN_COL_DROP is also
confusing to me:
"Dropping column '{0}' would orphan the generated column '{1}' which
references it. You must drop the generated column first.
I interpret this to mean that if 0 is dropped, 1 would be the only
column left and a computed column can obviously not be alone in a
table. But the issue would be clearer explained by saying that after
1 is removed by cascade, there would be *no* columns left. I also
don't understand the point about dropping the generated column
first? Dropping 0 after having dropped 1 wouldn't improve things
unless another column were added in the meantime? Or maybe I am
misunderstanding here.. getting late ;-)
* GeneratedColumnsTest.java
- The verification that an index on a generated column is actually
dropped could be better, I think. Presently, you only verify that
an insert previosly blocked by the index is legal after the dropping
of the column. It would be good to inspect the dictionary to see
that it is actually gone physically as well.
- Under verification that constraints are dropped, I started
wondering.. I think this expected error is not according to the
standard:
:
create table t_dc_10( a int generated always as ( -b ) check ( a is not null
), b int, c int )"
alter table t_dc_10 drop column a restrict // gives X0Y25
:
According to section 11.19, we have:
"5) If RESTRICT is specified, then C shall not be referenced in any of the
following:
a) The of any view descriptor.
b) The of any constraint descriptor other than a
table constraint descriptor that contains references to no other column
and
**
that is included in the table descriptor of T.
c) The SQL routine body of any routine descriptor.
d) Either an explicit trigger column list or a triggered action
column set of any trigger descriptor.
e) The generation expression of any column descriptor."
It would seem to be that in this case we are covered under item 5b),
the check constraint only references the dropped column "a," so
RESTRICT should not block this dropping. This is not
behavior particular to generated columns, so if you agree with my
interpretation, we should
file a JIRA for it.
> 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
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12651068#action_12651068 ] Dag H. Wanvik commented on DERBY-481: - Looked at derby-481-13-aa-alterDatatype.diff, looks good! Thanks. > 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, derby-481-17-aa-currentSchema.diff, > derby-481-18-aa-updatableResultSets.diff, derby-481-19-aa-cleanup.diff, > GeneratedColumns.html, GeneratedColumns.html, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12651062#action_12651062 ] Dag H. Wanvik commented on DERBY-481: - Looking at derby-481-12-aa-padding, thanks for the new tests. - Shouldn't the type of column a in table t_cc_varchar be a varchar? It is a char now. > 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, derby-481-17-aa-currentSchema.diff, > derby-481-18-aa-updatableResultSets.diff, derby-481-19-aa-cleanup.diff, > GeneratedColumns.html, GeneratedColumns.html, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[
https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12651059#action_12651059
]
Dag H. Wanvik commented on DERBY-481:
-
Reviewing derby-481-11-aa-notNull. Thanks for the good explanations,
Rick, really helped! Good patch.
Notes:
Index: java/engine/org/apache/derby/impl/sql/execute/NormalizeResultSet.java
Good that you refactored this class a bit; was a bit messy before..
* Javadoc for normalizeColumn starts with the text "Normalize a
row". It should probably be "Normalize a column". What about the
rest of that comment? It speaks of not yet having data type
conversions.. Is this still relevant? We do have casts, but maybe
this is something else. Asking since you touched it by moving it
around ;-)
* I think the normalizeColumn method should take sourceCol as an
argument, rather than sourceRow (less scope; only one column is ever
used inside normalizeColumn).
* normalizeColumn javadoc lacks @params and @return.
* private member numColumns is no longer needed.
* computeStartColumn lacks @return and @params.
Index: java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java
* would be nice to comment usage of new members firstColumn,
generatedColumnPositions, normalizedGeneratedValues
* maybe I am missing something here, but couldn't the two conditional
actions below be put inside one 'if' in that they always happen together?
if ( firstColumn < 0 ) { firstColumn =
NormalizeResultSet.computeStartColumn( isUpdate,
activation.getResultDescription() ); }
if ( generatedColumnPositions == null ) {
setupGeneratedColumns( activation, (ValueRow) newRow ); }
* Javadoc for setupGeneratedColumns could use some more explanation, a
bit on the concise side now, I think. It has side-effects.
* The javadoc for ResultColumn#hasGenerationClause can now refer to
the javadoc in the interface ResultColumnDescriptor (to avoid
redundancy in description).
> 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, derby-481-17-aa-currentSchema.diff,
> derby-481-18-aa-updatableResultSets.diff, derby-481-19-aa-cleanup.diff,
> GeneratedColumns.html, GeneratedColumns.html, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[
https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12650666#action_12650666
]
Dag H. Wanvik commented on DERBY-481:
-
Reviewed patch derby-481-10-aa-foreignKeyActions. Looks correct to
me. Thanks, Rick!
My notes:
* error messages
'{0}' is a generated column. It cannot be part of a foreign key whose
referential action is SET NULL or SET DEFAULT or whose update rule is
ON UPDATE CASCADE
Would perhaps be better to say "referential action for delete is SET
NULL or SET DEFAULT or whose referential action for update is
CASCADE" to make the the two cases more symmetrically explained. As
it stands, the first part doesn't explicitly say this concerns ON
DELETE.
* AlterTableNode.java
This code fragment:
:
if (numGenerationClauses > 0)
{ tableElementList.bindAndValidateGenerationClauses(fromList,
generatedColumns ); }
if ( numReferenceConstraints > 0) {
tableElementList.validateForeignKeysOnGenerationClauses( fromList,
generatedColumns ); }
The latter if statement could be conditional on numGenerationClauses > 0, too,
I think
if (numGenerationClauses > 0) {
tableElementList.bindAndValidateGenerationClauses(
fromList, generatedColumns );
if (numReferenceConstraints > 0) {
tableElementList.validateForeignKeysOnGenerationClauses(
fromList, generatedColumns ); }
}
Even if validateForeignKeysOnGenerationClauses tests for the presence
of generation clauses, it seems more logical to me to just not call
it.
Ditto in CreateTableNode.java.
* TableElementList.java
- typo: "rulse" -> rule
- This logic
if (
( deleteRule != StatementType.RA_SETNULL ) &&
( deleteRule != StatementType.RA_SETDEFAULT )
)
{ continue; }
I think is more readable as:
if( ! (deleteRule == StatementType.RA_SETNULL ||
deleteRule == StatementType.RA_SETDEFAULT ) )
{ continue }
Ideally, though, I would prefer
if( deleteRule == StatementType.RA_SETNULL ||
deleteRule == StatementType.RA_SETDEFAULT ) {
// do our thing
} else {
continue
}
in spite of the extra indentation, but that's me... ;)
* GeneratedColumnsTest.java
Thanks for the extra tests! Cf. DERBY-3964 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: 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, derby-481-17-aa-currentSchema.diff,
> derby-481-18-aa-updatableResultSets.diff, derby-481-19-aa-cleanup.diff,
> GeneratedColumns.html, GeneratedColumns.html, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12650250#action_12650250 ] Dag H. Wanvik commented on DERBY-481: - Reviewed derby-481-09-aa-dummyDefaults. Patch looks fine. - InsertNode.java Comment ca line 301 "Replace any DEFAULTs with the associated tree" is now only conditionally true; for generated columns this happens later. It would be nice to improve this comment to explain where it happens for such column. The javadoc for the ResultSetNode#replaceDefaults would also benefit from this caveat. Similarly for RowResultSet, SingleChildResultSetNode and TableOperatorNode, I guess.. > 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, derby-481-17-aa-currentSchema.diff, > derby-481-18-aa-updatableResultSets.diff, derby-481-19-aa-cleanup.diff, > GeneratedColumns.html, GeneratedColumns.html, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12650238#action_12650238 ] Dag H. Wanvik commented on DERBY-481: - Thanks for your incorporating the comments, Rick! Reviewed derby-481-08-aa-castToDeclaredType.diff Patch looks good! Some small notes on the test: I compared with the table found in the refman under the CAST function (http://db.apache.org/derby/docs/dev/ref/rrefsqlj33562.html) to check that all conversions are included. - smallint, int, bigint and decimal lacks a testcase for char - char lacks testcases for smallint, int, bigint and decimal - the varchar test seems wrong (identical to the char case; copy/paste error?) - the three bitdata cases lack cases for clob and blob - timestamp lacks cases for date and time - xml is omitted Some of the omissions may be intentional (xml), if so it would be good to indicate why they are omitted in this test. > 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, derby-481-17-aa-currentSchema.diff, > derby-481-18-aa-updatableResultSets.diff, derby-481-19-aa-cleanup.diff, > GeneratedColumns.html, GeneratedColumns.html, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12649864#action_12649864 ] Rick Hillegas commented on DERBY-481: - Tests ran cleanly for me except for the heisenbugs in the stressmulti tests. Committed derby-481-19-aa-cleanup.diff at subversion revision 719760. > 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, derby-481-17-aa-currentSchema.diff, > derby-481-18-aa-updatableResultSets.diff, derby-481-19-aa-cleanup.diff, > GeneratedColumns.html, GeneratedColumns.html, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12648832#action_12648832 ] Dag H. Wanvik commented on DERBY-481: - Reviewed derby-481-07-ab-noSQLinRoutines. Good patch, verified that the extended test fails without the rest of the patch. Notes: M java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java - throwReliabilityException: new argument fragmentBitMask lacks @param M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java - You have used the reliability constant SQL_IN_ROUTINES_ILLEGAL. Isn't the name "routines" that overly wide here, since only functions are ever allowed in generation clauses? > 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, derby-481-17-aa-currentSchema.diff, > GeneratedColumns.html, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12648798#action_12648798 ] Dag H. Wanvik commented on DERBY-481: - Reviewed derby-481-06-aa-genreferences. Looks good! I verified that without the rest of this patch, the extended GeneratedColumnsTest fails on the first case of referencing other generated columns. Notes: - The javadoc for TableDescriptor#makeColumnMap lacks @param and @return tags. - The javadoc for ResultColumnList#getPosition has @param, but lacks @return tag. > 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, derby-481-17-aa-currentSchema.diff, > GeneratedColumns.html, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[
https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12648689#action_12648689
]
Dag H. Wanvik commented on DERBY-481:
-
Reviewed derby-481-05-aa-update. Thanks for all this work, Rick!
Thorough work, I think, and the approach is well integrated with the rest of the
machinery. Explanations again helped me through this one; some
unclear points ("why is this needed?") from the the preceding "insert" patch
became
clear now.
Minor notes:
M java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java
- import of C_NodeTypes occurs twice now. New imports of
java.util.ArrayList and java.util.Array is also unneeded.
- comment ca line 578 starting "Get and bind all check constraints.."
should mention generated columns too.
- addGeneratedColumns's dummy declaration can omitted by using
getNullNode(gc.getType()) instead.
- At declaration time of bindStatement's addedGeneratedColumns, it
would be nice to mention what this variable is used for (grant
"forgiveness" in forbidGenerationOverrides).
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java
- pedantic note: assertTriggerStatus makes a select which does no
ORDER BY before comparison with canon; it's OK here of course, since
it's a VTI for which we have control over the order of the rows
handed out..
> 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, derby-481-17-aa-currentSchema.diff,
> GeneratedColumns.html, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[
https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12648477#action_12648477
]
Dag H. Wanvik commented on DERBY-481:
-
Reviewed derby-481-04-aa-insert. Quite a mouthful, this one :) Looks
good, though. Thanks for the detailed explanation of the code; very
helpful. I wasn't able to locate the "similar poking" being done for
the CHECK codepaths; could you point me to that?
Minor notes:
M java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
- I think createGeneratedColumn can be simplified by using arg
getNullNode(colDesc.getType()) instead of the dummy declaration?
M java/engine/org/apache/derby/impl/sql/compile/DMLModStatementNode.java
- javadocs for forbidGenerationOverrides, parseAndBindGenerationClauses
and generateGenerationClauses lack @params
- The "continue" statement after hasGenerationClauses has been
established as true can be replaced by a break; no need to see more.
The initial comment in this loop ("generate statements of the
form..") seem misplaced too, since this loop is only used to detect
is there exists a generation clause. It should probably be moved to
after the loop?
- I see you have just followed the pattern from check constraints, but
I am not sure I like the fact that there are two overloaded public
methods called generateGenerationClauses which seem to be on
different levels of abstraction. The innermost can be private,
btw. I would have preferred different names for them.
M java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
- has lots of spurious diffs; only one significant change.
M java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java
- The method evaluateGenerationClauses could be made protected and
moved down to DMLWriteResultSet. It lacks @params for its javadoc.
> 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, derby-481-17-aa-currentSchema.diff,
> GeneratedColumns.html, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12647782#action_12647782 ] Dag H. Wanvik commented on DERBY-481: - > I think that if views are supposed to be dropped because an > underlying, role-based permission has vanished, then tables should > also be dropped if they require a role-based permission that has > vanished. I am not sure; I haven't yet been able to find any mention of this scneario in the standard. It seems a bit harsh; dropping a view, constraint or trigger doesn't actually delete any data, whereas deleting a table would.. Maybe it's ok to just let inserts and updates (if the generated column would need regeneration) to that table fail as long as execute privileges are not in place for any function referenced in the generation expression.. I'll see if I can find anything in the std. > 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, derby-481-17-aa-currentSchema.diff, > GeneratedColumns.html, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12647780#action_12647780 ] Dag H. Wanvik commented on DERBY-481: - Reviewed derby-481-03-aa-grammar.diff, looks good! Interesting to see more of the compile-time mechinery involved here. Minor notes: * DDLStatementNode.java makeFromList javadoc misses @param for 2 parameters * TableElementList.java - This comment in bindAndValidateGenerationClauses is a cut/paste palimpsest: // bind the check condition // verify that it evaluates to a boolean - Shouldn't finally clause in bindAndValidateGenerationClauses also reset the current auxiliary provider list? * StaticMethodCallNode.java - two spurious blank line diffs * GenerationClauseNode.java toString should include "AS" keyword after ALWAYS. * GeneratedColumnsTest.java - It would be nice to add a comment that the test can not be run in client server mode due to the casting to internal Derby objects of some result set columns for dictionary tables. * DB_Table.java Will an unqualified function name necessarily be output correctly by dblook? Do we need to expand the generation text in SYSCOLUMN to include the fully qualified name for functions? > 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, derby-481-17-aa-currentSchema.diff, > GeneratedColumns.html, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12647717#action_12647717 ] Rick Hillegas commented on DERBY-481: - Hi Dag, Thanks for your continued attention to this issue. I am catching up with some of your comments now. This is reference to the role-related question you asked on 12/Nov/08 09:49 AM. I think that if views are supposed to be dropped because an underlying, role-based permission has vanished, then tables should also be dropped if they require a role-based permission that has vanished. I am going to create an issue to track this. If you think this is not the correct behavior, please elaborate. Thanks. > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12647578#action_12647578 ] Knut Anders Hatlen commented on DERBY-481: -- All callers of StringUtil.stringify() were removed in derby-481-14-ab-dropColumn.diff, so we might just as well remove the method now. > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[
https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12647571#action_12647571
]
Knut Anders Hatlen commented on DERBY-481:
--
> - javadoc for StringUtil#stringify: would be nice to specify the format of
> the output: Some such:
>
> 'null' | '[ ' [ { ', ' }* ] ' ]'
Except for the space after the opening bracket and before the closing
bracket, the format is identical to what you get from
java.util.Arrays.toString(int[]), which is not available prior to Java
1.5. Perhaps it's better just to document that it should return the
same as Arrays.toString() so that we can replace it with a call to the
standard library once we move to Java 1.5. Currently, the the method
is only used by test code, so maybe it should be moved out of the
production jars?
> - GeneratedColumnsTest#expectExecutionError: closing of ps is missing
Or even better: use BaseJDBCTestCase.prepareStatement() instead of
Connection.prepareStatement(). Then you get automatic cleanup in
tearDown().
> 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[
https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12647470#action_12647470
]
Dag H. Wanvik commented on DERBY-481:
-
Reviewing patch derby-481-02-aa-utilities.diff.
Patch looks good to me. Some small notes:
- javadoc for StringUtil#stringify: would be nice to specify the format of
the output: Some such:
'null' | '[ ' [ { ', ' }* ] ' ]'
- GeneratedColumnsTest#expectExecutionError: closing of ps is missing
- GeneratedColumnsTest#assertResults
:
assertEquals( (expectedValue == null), rs.wasNull() );
if ( expectedValue == null ){ assertNull( actualValue ); }
Isn't the latter assert redundant after the first assert?
- GeneratedColumnsTest: lines > 80 long
- SQLState.java: it would look better to align the values for the new
errors with the existing declarations (in column 72).
I didn't check the commented out new code much, since its not live yet.
> 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12647466#action_12647466 ] Dag H. Wanvik commented on DERBY-481: - I read the specification again, clear and easy to read! Some notes: - Overview 2nd sentence. Maybe add ALTER TABLE as well. 3rd sentence. "The user declares" -> "The user can declare" - SQL standard: Add sections 11.8, 11.39 Reference to section 14.18 should be to 11.18, I think - syntax: generation-clause ::= GENERATED ALWAYS ( value-expression ) * "AS" keyword required after ALWAYS but missing - SYSCOLUMNS: no new column was added, so I guess this section should be modified to reflect the implementation now? - Documentation If we decide REVOKE execute privilege from will impact functions references in generated columns (as in dropping them), this should probably be documented along with the new doc for REVOKE from and DROP . Add note in INSERT on the use of "default", perhaps? Appendix A: Possibly Non-deterministic System Functions - add CURRENT SCHEMA - add CURRENT SQLID > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12647291#action_12647291 ] Rick Hillegas commented on DERBY-481: - Thanks for your comments, Dag and Knut. Knut is right, the following statements should fail because we are trying to override a generation clause: insert into a select * from b; insert into a select i,0 from b; Thanks for finding these problems. > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12647290#action_12647290 ] Dag H. Wanvik commented on DERBY-481: - Knut> I would have expected that attempts to set a column which is Knut> GENERATED ALWAYS AS failed. Yes. Note, though, that the generated column gets the correct (generated) value, Derby presently just requires the number of columns to be 2, the last (2nd) column in the RS is discarded... Knut> insert into a(i) select i from b Yes, I was just wondering if the column specification could be dropped legally in this case or not. If yes, the required result set should have 1, not 2 columns. But it might be column indication is always required here. > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12647275#action_12647275 ] Knut Anders Hatlen commented on DERBY-481: -- > Is this the right behavior? I would have expected that attempts to set a column which is GENERATED ALWAYS AS failed. Similarly to what happens if you try to specify the value of an identity column (GENERATED ALWAYS AS IDENTITY): ij> insert into tt select * from b; ERROR 42Z23: Attempt to modify an identity column 'B'. > It seems a bit non-intuitive to require the > result set inserted to have two columns when only one is used anyway... Note that you could specify that you only set some of the columns in the insert (again similarly to how you'd do it if you had an identity column): insert into a(i) select i from b > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12647156#action_12647156 ] Dag H. Wanvik commented on DERBY-481: - Looked at derby-481-01-aa-catalog.diff, looks good. Only small notes: - weird indentation for variable "count" in getGeneratedColumns - In DefaultInfoImpl, specify BITS_MASK_IS_GENERATED_COLUMN as = 0x1 << 1 to follow pattern ? - New DefaultInfoImpl constructor has weird indentation for referencedColumnIDs arg. - Lines > 80 chars > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12646973#action_12646973 ] Dag H. Wanvik commented on DERBY-481: - I agree. Another thing: What is the correct behavior for this case? Here the generation expression needs a privilege which gets revoked... ij(CONNECTION0)> create table t (i int, j int, k int generated always as (dag.myfunc(j))); ERROR 42504: User 'KNUT' does not have execute permission on FUNCTION 'DAG'.'MYFUNC'. ij(CONNECTION0)> set role bar; -- this role has the privilege and is granted to KNUT earlier 0 rows inserted/updated/deleted ij(CONNECTION0)> create table t (i int, j int, k int generated always as (dag.myfunc(j))); 0 rows inserted/updated/deleted ij(CONNECTION0)> set connection connection1; ij(CONNECTION1)> revoke bar from knut; 0 rows inserted/updated/deleted ij(CONNECTION1)> set connection connection0; ij(CONNECTION0)> insert into t values (1,2,default); ERROR 42504: User 'KNUT' does not have execute permission on FUNCTION 'DAG'.'MYFUNC'. Triggers, view and constraints in similar circumstances get dropped; should the generated column get dropped too? > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12646929#action_12646929 ] Rick Hillegas commented on DERBY-481: - Thanks for finding this issue with BEFORE triggers, Dag. I don't understand why the standard forbids these references in BEFORE triggers. This would be a good question to ask the SQL committee. I think we should follow the standard here. We can relax this restriction later if we come to understand that it really is harmless. > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ 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 shall not contain a 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: 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12646369#action_12646369 ] Rick Hillegas commented on DERBY-481: - Tests ran cleanly for me on derby-481-16-aa-dropFunction.diff. Committed at subversion revision 712840. > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12645988#action_12645988 ] Rick Hillegas commented on DERBY-481: - Committed derby-481-14-ab-dropColumn.diff at subversion revision 712400. > 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, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12645891#action_12645891 ] Rick Hillegas commented on DERBY-481: - Tests ran cleanly for me on derby-481-14-ab-dropColumn.diff except for 4 errors. These errors were in ClobUpdatableReaderTest, NetworkServerMBeanTest, and StressMultiTest. I think these are either errors introduced by recent submissions or outstanding heisenbugs: There were 4 errors: 1) testUpdateableStoreReader(org.apache.derbyTesting.functionTests.tests.jdbcapi.ClobUpdatableReaderTest)java.sql.SQLException: Java exception: 'Bad file descriptor: java.io.IOException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:87) at org.apache.derby.impl.jdbc.Util.javaException(Util.java:244) at org.apache.derby.impl.jdbc.EmbedConnection.clearLOBMapping(EmbedConnection.java:3097) at org.apache.derby.impl.jdbc.EmbedConnection.rollback(EmbedConnection.java:1765) at org.apache.derbyTesting.junit.BaseJDBCTestCase.rollback(BaseJDBCTestCase.java:378) at org.apache.derbyTesting.functionTests.tests.jdbcapi.ClobUpdatableReaderTest.tearDown(ClobUpdatableReaderTest.java:304) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:105) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) Caused by: java.io.IOException: Bad file descriptor at java.io.RandomAccessFile.close0(Native Method) at java.io.RandomAccessFile.close(RandomAccessFile.java:532) at org.apache.derby.impl.jdbc.LOBFile.close(LOBFile.java:148) at org.apache.derby.impl.jdbc.EmbedConnection.clearLOBMapping(EmbedConnection.java:3095) ... 29 more 2) testUpdateableReader(org.apache.derbyTesting.functionTests.tests.jdbcapi.ClobUpdatableReaderTest)java.sql.SQLException: Table/View 'UPDATECLOB' already exists in Schema 'APP'. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:201) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1323) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:555) at org.apache.derbyTesting.functionTests.tests.jdbcapi.ClobUpdatableReaderTest.setUp(ClobUpdatableReaderTest.java:284) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:105) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) Caused by: ERROR X0Y32: Table/View 'UPDATECLOB' already exists in Schema 'APP'. at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:373) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.duplicateDescriptorException(DataDictionaryImpl.java:1817) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.addDescriptor(DataDictionaryImpl.java:1808) at org.apache.derby.impl.sql.execute.CreateTableConstantAction.executeConstantAction(CreateTableConstantAction.java:238) at org.apache.derby.impl.sql.execute.MiscResultSet.open(MiscResultSet.java:64) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235) ... 29 more 3) testMultiplexedOperationProblem(org.apache.derbyTesting.functionTests.tests.jdbcapi.ClobUpdatableReaderTest)java.sql.SQLException: Table/View 'UPDATECLOB' already exists in Schema 'APP'. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45) at org.apache.derby.impl.jdbc.Util.ge
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12645225#action_12645225 ] Rick Hillegas commented on DERBY-481: - Tests against derby-481-11-aa-notNull.diff ran cleanly for me except for the heisenbugs in StressMultiTest. Committed at subversion revision 711571. > 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, > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12644859#action_12644859 ] Rick Hillegas commented on DERBY-481: - Committed derby-481-10-aa-foreignKeyActions.diff at subversion revision 711135. > 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, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12644807#action_12644807 ] Rick Hillegas commented on DERBY-481: - Tests ran cleanly for me on deby-481-10-aa-foreignKeyActions.diff. > 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, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12644732#action_12644732 ] Rick Hillegas commented on DERBY-481: - Tests ran cleanly for me on derby-481-09-aa-dummyDefaults.diff. Committed at subversion revision 710071. > 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, > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12644436#action_12644436 ] Rick Hillegas commented on DERBY-481: - Tests ran cleanly for me on derby-481-aa-castToDeclaredType.diff. Committed at subversion revision 709577. > 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, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12644374#action_12644374 ] Rick Hillegas commented on DERBY-481: - Hi Dag, Thanks for digging into this! 1) "insert into bar values (2, default); -- fails trying to bind "i" " - This looks like a bug to me. 2) I didn't notice section 11.8, SR 12a) when I wrote the functional spec. I should add that to the functional spec and make sure that those cases are forbidden. Thanks! > 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, > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12644368#action_12644368 ] Dag H. Wanvik commented on DERBY-481: - I tried this fragment which seems to be forbidden by section 11.8, SR 12a), p.549: create table bar2 (i int, j int generated always as (i*2) references foo(i) on delete set null); The "set null" part here is not flagged, but probably should be (conflict with generated clause)? Sorry if this is pending work, I am just working my way through the standard ;-) > 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, > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12644366#action_12644366 ] Dag H. Wanvik commented on DERBY-481: - Started playing a bit with generated columns, this behavior seems wrong? create table bar (i int, j int generated always as (-i)); insert into bar(i,j) values (1, default); -- works insert into bar values (2, default); -- fails trying to bind "i" ERROR 42X04: Column 'I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'I' is not a column in the target table. > 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, > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12644058#action_12644058 ] Rick Hillegas commented on DERBY-481: - Tests passed cleanly for me on derby-481-06-aa-genReferences.diff. Committed at subversion revision 709219. > 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, > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12643479#action_12643479 ] Rick Hillegas commented on DERBY-481: - Committed derby-481-05-aa-update.diff at subversion revision 708900. > 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, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12643254#action_12643254 ] Rick Hillegas commented on DERBY-481: - Tests ran cleanly for me on the derby-481-05-aa-update.diff patch. > 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, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12643195#action_12643195 ] Rick Hillegas commented on DERBY-481: - Thanks for puzzling through the patch summary, Bryan. I appreciate the sanity-check. I committed derby-481-04-aa-insert.diff at subversion revision 708561. > 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, > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12643116#action_12643116 ] Bryan Pendleton commented on DERBY-481: --- Modeling the overall execution behaviors on CHECK constraints seems like a reasonable approach to me. Thanks for taking the time to explain your thinking. It seems like you've found a couple of thorny implementation details, your strategy of addressing them seems fine to me. > 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, > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12643073#action_12643073 ] Rick Hillegas commented on DERBY-481: - Tests pass cleanly for me on derby-481-04-aa-insert.diff. > 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, > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12642953#action_12642953 ] Rick Hillegas commented on DERBY-481: - Commited derby-03-aa-grammar.diff at subversion revision 708169. > 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, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12642912#action_12642912 ] Rick Hillegas commented on DERBY-481: - Hi Bryan, Thanks for reading the patches. I appreciate having another set of eyes on this! I think that your questions will be addressed in the next couple of patches, which should introduce INSERT-time and UPDATE-time processing. In coding INSERT/UPDATE support, I took CHECK constraints as my model. This is what's going on in the prototype: When you INSERT into a table which has a generated column: 1) During compilation, at generate() time, we build a function to evaluate the generation clause. 2) At execution time, we run that function on every row that will be inserted into the table. The function runs AFTER the non-generated columns are populated but BEFORE we run triggers, enforce constraints, and build index rows. All of this happens before the base and index rows are handed to the Store. When you UPDATE a table which has a generated column: 3) During compilation, at bind() time we determine whether we need to re-evaluate any of the generation clauses. We decide to re-generate a column if we see that one of the columns that it references is being changed. So in the example you gave, we would decide to re-generate c given the following update statement: update bry set b = b + 3 but we would decide NOT to re-generate c given the following update statement update bry set a = a + 3 4) At execution time, we run that function on every row that is being updated. As with INSERT, the function runs AFTER the non-generated columns are populated but BEFORE we run triggers, enforce constraints, and build index rows. That, at least, is how the prototype is supposed to work! So to answer your questions directly: A) Generation clauses are evaluated when you INSERT and UPDATE rows. B) For UPDATEs, we re-evaluate a generation clause only if we are changing one of the columns that it references. Thanks, -Rick > 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, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12642572#action_12642572 ] Bryan Pendleton commented on DERBY-481: --- Hi Rick, I read through the first 4 patches and they look very good to me. Thanks for the effort in writing the comments and the tests. I was wondering if you could briefly outline how you expect the execution-time logic to go: will we generate code to implement the generation expression? Or will there be some sort of interpreter that runs as part of update processing and interprets the generation expression? Also, when are the generated columns evaluated? Is it only when their value is retrieved by a SELECT? Or is the generated column evaluated whenever anything that it references is updated? So for example, if I have: create table bry (a int, b int, c int generated always as (2 * b)) then is the value of column c generated: - only when c is selected? - when the value of b changes? - when the value of either a or b changes? thanks, bryan > 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, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12642559#action_12642559 ] Rick Hillegas commented on DERBY-481: - Tests ran cleanly for me on the derby-481-03-aa-grammar.diff patch. > 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, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12642454#action_12642454 ] Rick Hillegas commented on DERBY-481: - Committed derby-481-02-aa-utilities.diff at subversion revision 707619. > 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, > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12642200#action_12642200 ] Rick Hillegas commented on DERBY-481: - Committed derby-481-01-aa-catalog.diff at subversion revision 707414. > 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, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12641969#action_12641969 ] Bryan Pendleton commented on DERBY-481: --- That makes sense. Thanks for the clarification, and thanks for working on this feature! > 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, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12641967#action_12641967 ] Rick Hillegas commented on DERBY-481: - Hi Bryan, Thanks for your interest in this patch! In general, I do want to provide tests with patches. For the catalog changes, however, I need grammar changes before I can really test anything meaningful. I considered introducing the catalog changes along with the grammar patch but I thought it would be better to chunk the catalog piece in first in order to reduce the size of the grammar patch. > 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, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12641959#action_12641959 ] Bryan Pendleton commented on DERBY-481: --- I think the incremental approach is great, but why not do the tests incrementally, at the same time? > 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, 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12582272#action_12582272 ] Rick Hillegas commented on DERBY-481: - Sounds like a good idea. We'll need to divide this work into subtasks as we go along. I've created a separate subtask (DERBY-3570) to track the DETERMINISTIC work. > 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 > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12582121#action_12582121 ] Daniel John Debrunner commented on DERBY-481: - Since DeterministicCharacteristic is an independent feature to generated columns should it be its own Jira issue. If not we may land in a similar situation to DERBY-2109 where independent functionality is implemented but no record exists in Jira. > 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 > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ 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: 10.0.2.1 >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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12580443#action_12580443 ] Rick Hillegas commented on DERBY-481: - Thanks, Dan. That would be great. > 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 > 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.
[jira] Commented: (DERBY-481) implement SQL generated columns
[ https://issues.apache.org/jira/browse/DERBY-481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12580439#action_12580439 ] Daniel John Debrunner commented on DERBY-481: - In the spec: > We rename the sqlAllowed field of RoutineAliasInfo and borrow one of its bits > to encode whether a routine is DETERMINISTIC. RoutineAliasInfo already has a field set up for expansion, I think we can use this in a smart way instead of making a single field handle multiple meanings (less clear). I've always been planning to add all the possible future options for routines into RoutineAliasInfo, I'd be willing to handle that portion for this improvement. > 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 > 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.
