[
https://issues.apache.org/jira/browse/DERBY-118?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dag H. Wanvik resolved DERBY-118.
---------------------------------
Resolution: Won't Fix
Issue & fix info: (was: Patch Available)
Closing this as "Won't fix", but see also DERBY-5829 and DERBY-5825.
> 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-b.diff, derby-118-limited-even-more-b.status,
> 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
> [Edit: dagw 2012-06-23
> DERBY-118 started out as a request to allow date-time functions values to be
> assigned to character columns. This is not standard SQL, but it turns out the
> Derby allows such conversions at DML time, e.g. in an INSERT statement.
> Investigating this we found that there a re several instances in which Derby
> is more restrictive in type conversion at DDL time that at DML time. These
> instances can be classified as
> a) legacy restrictions that are DB2 specific, but would be legal SQL if
> lifted (DERBY-5829)
> b) restrictions that are not legal SQL per the standard (this issue)
> We decided not to fix issues in b) those since this would allow users to
> write non-standard SQL, notwithstanding that fact the Derby also extends on
> the standard at DML time. Ideally, we would restrict those cases to follow
> the standard as well, but that would cause compatibility concerns.
> Furthermore, a related issue is that type checks at DDL time, e.g. that a
> character literal is not too long for its column, are performed at DML time.
> This is not as per the standard, and we track an improvement in this area as
> c) Catch type mismatch of DEFAULT values at DDL time (DERBY-5825)
> ]
--
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
