[
https://issues.apache.org/jira/browse/DERBY-118?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13397409#comment-13397409
]
Knut Anders Hatlen commented on DERBY-118:
------------------------------------------
It is not generally the case that we fail at CREATE TABLE time when the string
literal is longer than what the data type accepts, as can be seen by executing
these statements (without the patch):
ij> create table t(x varchar(2) default 'abc');
0 rows inserted/updated/deleted
ij> insert into t values default;
ERROR 22001: A truncation error was encountered trying to shrink VARCHAR 'abc'
to length 2. (errorCode = 30000)
It is only when the literal is longer than 254 characters that we currently
fail at table creation time, and in that case we fail because the literal is
longer than 254 characters, not because the data type cannot hold the value.
So whereas I agree with Mamta that failing at CREATE TABLE time is correct
according to the standard, moving the failure to CREATE TABLE time will
actually be an additional restriction on CREATE TABLE, not lifting of a
restriction, and could potentially lead to compatibility issues. It's not that
I think the compatibility impact would be severe (after all, it most likely
indicates a bug in the table definition), but I thought it was worth mentioning
that it actually is making it more restrictive.
To me it sounds reasonable that if we lift the restriction on the number of
characters for a string literal in the DEFAULT clause, those literals that are
longer than the old limit should be treated the exact same way as the shorter
literals. Whether or not we should add more restrictions on the shorter
literals (and, transitively, on the longer literals once the 254-character
limitation is lifted) sounds like an orthogonal question.
> Lift some DB2 restrictions on DEFAULT values [was: Allow any build-in
> function as default values in table create for columns]
> -----------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-118
> URL: https://issues.apache.org/jira/browse/DERBY-118
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Bernd Ruehlicke
> Assignee: Dag H. Wanvik
> Priority: Minor
> Attachments: derby-118-all-defaults.diff,
> derby-118-limited-even-more.diff, derby-118-limited-even-more.status,
> derby-118-limited.diff, derby-118-limited.status, derby-118-longvarchar.diff,
> derby-118.diff, derby-118.stat, derby-118b.diff, derby-118b.stat,
> derby-118c.diff, derby-118c.stat
>
>
> It is ok in ij to do a values char(current_date) but is is not allowed to
> use char(current_date) as default value for clolumns; like for example
> CREATE TABLE DOSENOTWORK (num int, created_by varchar(40) default user,
> create_date_string varchar(40) default char(current_date))
> Request: It should be allowed to use any build-in function which return a
> valid type as part of the default value spec.
> There was a e-mail thread for this and the core content/answer was:
> Bernd Ruehlicke wrote:
> >
> > CREATE TABLE DOSENOTWORK (num int, created_by varchar(40) default
> > user, create_date_string varchar(40) default char(current_date))
> >
> > give an error as below - any idea why ?!??!
> >
> The rules for what is acceptable as a column default in Derby say that the
> only valid functions are datetime functions.
> The logic that enforces this can be seen in the "defaultTypeIsValid" method
> of the file:
> ./java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
> The Derby Reference Manual also states this same restriction (albeit rather
> briefly):
> ----
> Column Default
> For the definition of a default value, a ConstantExpression is an expression
> that does not refer to any table. It can include constants, date-time special
> registers, current schemas, users, and null.
> ----
> A "date-time special register" here means a date-time function such as
> "date(current_date)" in your first example.
> Since the function "char" is NOT a date-time function, it will throw an error.
> I believe this restriction was put in place as part of the "DB2
> compatibility" work was that done in Cloudscape a while back.
> Hope that answers your question,
> Army
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
