[
https://issues.apache.org/jira/browse/DERBY-118?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13286950#comment-13286950
]
Dag H. Wanvik commented on DERBY-118:
-------------------------------------
Thanks, Knut. I already added an test case for CHAR, also adopted your
suggestion of Formatters.repeatChar().
Uploading a patch derby-118-all-defaults, which removes the all (I believe) DB2
restrictions in the DEFAULT clause, iff the restriction does not apply at
insert time. This makes Derby more liberal, as discussed, but symmetric.
(*) Another thing is that more checks could be made as table creation time
(rather than at insert time), since we are mostly talking literals here, and
even the datetime functions have predictable (modulo locale?) lengths. This is
an existing weakness, and I do not to intend to address that improvement in
this JIRA.
List of restrictions lifted:
- datetime builtins can be used for CHAR and VARCHAR columns
- loss of precision in default value when used for a decimal column
- allow float literals to be used as default for INT, BIGINT columns.
Interestingly, this was not restricted for SMALLINT, maybe unintentionally.
- allow string literals longer than 254 for VARCHAR columns. Truncation checks
will still happen at insert time. Note that this makes the (valid) check for
strings longer than 254 for CHAR columns move to INSERT time. I could
reintroduce this at create time, since it changes the behavior in a "negative"
way. Note that most truncation checks already happened at insert time; this
particular check, even is performed, is no guarantee, unless the CHAR type is
CHAR(254). A string of say, 200, would still be caught at insert time if the
type is CHAR(199), so the move makes the checking more regular, if sometimes
delayed, but cf. item (*) above.
Added positive and negative tests for the above.
Running regressions.
> 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-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
