[
https://issues.apache.org/jira/browse/DERBY-118?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13284912#comment-13284912
]
Dag H. Wanvik commented on DERBY-118:
-------------------------------------
By my reading of the standard, Derby is more liberal than the standard in the
assignment context of an INSERT, sml applicable sections from SQL 1993:
SR 2 in section 9.2. Store assignment (SQL 1993):
<quote> "2) If TD is character string, binary string, numeric, boolean,
datetime, interval, or a user-defined type, then either SD shall be assignable
to TD or there shall exist an appropriate user-defined cast function UDCF from
SD to TD. NOTE 204 — “Appropriate user-defined cast function” is defined in
Subclause 4.11, “Data conversions”. </quote>
Since we don't support user-defined cast functions in Derby, it would have to
be "assignable" for the assingment to be legal without an explicit CAST. The
definition os "assignable", is (4.6.2 Datetimes):
<quote> "A datetime is assignable to a site only if the source and target of
the assignment are both of type DATE, or both of type TIME (regardless whether
WITH TIME ZONE or WITHOUT TIME ZONE is specified or implicit), or both of type
TIMESTAMP (regardless whether WITH TIME ZONE or WITHOUT TIME ZONE is specified
or implicit)." </quote>
According to my tests of Derby and also the current doc set, implicit
conversion is allowed, cf. the table in
http://db.apache.org/derby/docs/10.8/ref/rrefsqlj58560.html
(Conversion to LONG VARCHAR is not allowed, however, but who cares..)
We probably do not want to restrict this behavior now, due to upward
compatibility concerns.
According to Rick, Cloudscape was liberal in the DEFAULT context, allowing also
function calls.
So, although I am not proposing we allow function calls now, I do think it
would be good to restore a bit of symmetry: since we allow implicit conversion
in INSERT and UPDATE contexts (I'll check if it is also available in call
context) - as described in the docs - I recommend we allow the implicit
conversion also in the DEFAULT context, i.e. the present patch be committed.
Your opinions are welcome.
> 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.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
