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] [Updated] (DERBY-118) Allow any build-in function as default values in table create for columns.
Date Fri, 22 Jun 2012 22:08:42 GMT

     [ https://issues.apache.org/jira/browse/DERBY-118?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Dag H. Wanvik updated DERBY-118:
--------------------------------

    Description: 
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 classifies 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) 



  was:
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





        Summary: Allow any build-in function as default values in table create for columns.
 (was: Lift some DB2 restrictions on DEFAULT values [was: Allow any build-in function as default
values in table create for columns])
    
> 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 classifies 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

        

Mime
View raw message