Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Mon, 4 Jun 2012 21:37:25 +0000 (UTC)
From: "Dag H. Wanvik (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <2071501184.36327.1338845845666.JavaMail.jiratomcat@issues-vm>
Subject: [jira] [Comment Edited] (DERBY-118) Lift some DB2 restrictions on
 DEFAULT values [was: Allow any build-in function as default values in table
 create for columns]
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Dag H. Wanvik edited comment on DERBY-118 at 6/4/12 9:36 PM:
-------------------------------------------------------------

As for type checking, conversion and code sharing, I think  we are ok:

InsertNode#bindStatement:

        checks the type compatibility here:

        a) resultColumnList.checkStorableExpressions(resultSet.getResultColumns());
           which mobilizes TypeCompilers (ca line 461)

           ResultColumn#checkStorableExpression:
           :
           if (!getTypeCompiler().storable(toStoreTypeId, getClassFactory())) {
              throw..

           which in turn calls
           NumericTypeCompiler#numberStorable

           and a float literal (represented by parser as a decimal) passes muster for an int column, for example:
           :
           if (otherType.isNumericTypeId()) { return true; }

        then later in bindStatement, ca line 470

        b) NormalizeResultSetNode#init - see class Javadoc example for storing
           "2.0" into a BIGINT.

           inserts a node to actually convert the value to the type of
           the column, e.g leads to the correct code generation for
           it, possibly throwing for truncation etc.

ColumnDefinitionNode:

        after the special checks for default, also uses the type
        compiler to check, cf.

        ColumnDefinitionNode#validateDefault:
        :
        // Now check 'not storable' errors.
        if (! getTypeCompiler(columnTypeId).
              storable(defaultTypeId, getClassFactory())) {
            throw..

        If this passes, the conversion will be handled the same way as
        in plain insert, since, the default value is expanded *before*
        the NormalizeResultSetNode of InertNode#bindStatement is
        constructed:

        InsertNode#bindStatement, ca line 323
        :
        /* Replace any DEFAULTs with the associated tree, or flag DEFAULTs if
         * not allowed (inside top level set operator nodes). Subqueries are
         * checked for illegal DEFAULTs elsewhere.
         */
        resultSet.replaceOrForbidDefaults(targetTableDescriptor,
                                          targetColumnList,
                                          isTableConstructor);

so, my conclusion is the conversion code is shared here. No cast node
is needed either.


      was (Author: dagw):
    As for type checking, conversion and code sharing, I think  we are ok:

InsertNode#bindStatement:

        checks the type compatibility here:

        a) resultColumnList.checkStorableExpressions(resultSet.getResultColumns());
           which mobilizes TypeCompilers (ca line 461)

           ResultColumn#checkStorableExpression:
           :
           if (!getTypeCompiler().storable(toStoreTypeId, getClassFactory())) {
              throw..

           which in turn calls
           NumericTypeCompiler#numberStorable

           and a decimal passes muster for an int column, for example:
           :
           if (otherType.isNumericTypeId()) { return true; }

        then later in bindStatement, ca line 470

        b) NormalizeResultSetNode#init - see class Javadoc example for storing
           "2.0" into a BIGINT.

           inserts a node to actually convert the value to the type of
           the column, e.g leads to the correct code generation for
           it, possibly throwing for truncation etc.

ColumnDefinitionNode:

        after the special checks for default, also uses the type
        compiler to check, cf.

        ColumnDefinitionNode#validateDefault:
        :
        // Now check 'not storable' errors.
        if (! getTypeCompiler(columnTypeId).
              storable(defaultTypeId, getClassFactory())) {
            throw..

        If this passes, the conversion will be handled the same way as
        in plain insert, since, the default value is expanded *before*
        the NormalizeResultSetNode of InertNode#bindStatement is
        constructed:

        InsertNode#bindStatement, ca line 323
        :
        /* Replace any DEFAULTs with the associated tree, or flag DEFAULTs if
         * not allowed (inside top level set operator nodes). Subqueries are
         * checked for illegal DEFAULTs elsewhere.
         */
        resultSet.replaceOrForbidDefaults(targetTableDescriptor,
                                          targetColumnList,
                                          isTableConstructor);

so, my conclusion is the conversion code is shared here. No cast node
is needed either.


> Lift some DB2 restrictions on DEFAULT values [was: 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-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

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