db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-118) Allow any build-in function as default values in table create for columns
Date Tue, 29 May 2012 16:27:23 GMT

    [ 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

       

Mime
View raw message