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] [Comment Edited] (DERBY-118) Allow any build-in function as default values in table create for columns
Date Wed, 23 May 2012 23:13:40 GMT

    [ https://issues.apache.org/jira/browse/DERBY-118?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13282025#comment-13282025
] 

Dag H. Wanvik edited comment on DERBY-118 at 5/23/12 11:12 PM:
---------------------------------------------------------------

Looking at the standard, I see that the default value is defined as 

<default option> ::=
<literal>
| <datetime value function>
| USER
| CURRENT_USER
| CURRENT_ROLE
| SESSION_USER
| SYSTEM_USER
| CURRENT_PATH
| <implicitly typed value specification>

This would seem to preclude a cast in the form of CHAR(CURRENT_DATE),
but this would be ok:

  CREATE TABLE ok (create_date_string VARCHAR(40) DEFAULT CURRENT_DATE)

and similarly for CURRENT_TIME and CURRENT_TIMESTAMP - it doesn't
currently. We do allow this implicit coercion on INSERT. Uploading a
proof-of-concept patch which makes the above table creation work:
derby-118.diff. No tests yet, running regressions.

                
      was (Author: dagw):
    Looking at the standard, I see that the default value is defined as 

<default option> ::=
<literal>
| <datetime value function>
| USER
| CURRENT_USER
| CURRENT_ROLE
| SESSION_USER
| SYSTEM_USER
| CURRENT_PATH
| <implicitly typed value specification>

This would seem to preclude a cast in the form of CHAR(CURRENT_DATE),
but this would be ok:

  CREATE TABLE ok (create_date_string VARCHAR(40) DEFAULT CURRENT_DATE)

and similarly for CURRENT_TIME and CURRENT_TIMESTAMP. We do allow this
implicit coercion on INSERT. Uploading a proof-of-concept patch which
makes the above table creation work: derby-118.diff. No tests yet,
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
>            Priority: Minor
>         Attachments: derby-118.diff, derby-118.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