db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5901) You can declare user-defined functions which shadow builtin functions by the same name.
Date Mon, 10 Sep 2012 17:35:08 GMT

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

Rick Hillegas commented on DERBY-5901:

The Standard only addresses the situation where the operator/function name is a reserved word.
For instance, DATE is a reserved word under the Standard and so an application should not
be allowed to create a function called DATE--Derby allows this and this creates a conflict
with the builtin Derby DATE operator.

The Standard is silent on operator/function names which are not reserved words. So, for instance,
the Standard has nothing to say about whether an application can create a function called
ABS or SIN. Note that these are both the names of JDBC escape functions. We would not have
a problem with these operator names if Derby only invoked them via JDBC escape syntax.

But like other RDBMSes, Derby can declare its own operators on top of those defined by the
Standard. As we go forward, we should be careful to not enlarge the problem described by this
JIRA. That is, if we add new operators, we should make sure that they cannot collide with
the names of application-defined functions.

I don't see how to address this issue without introducing a backward incompatibility. Regardless
of the approach we take, we could add a scrap of upgrade logic which looks for conflicting
application-defined function names. If we find a conflict, we could fail the upgrade with
a message advising the user that the conflict needs to be fixed first.


> You can declare user-defined functions which shadow builtin functions by the same name.
> ---------------------------------------------------------------------------------------
>                 Key: DERBY-5901
>                 URL: https://issues.apache.org/jira/browse/DERBY-5901
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions:
>            Reporter: Rick Hillegas
> You can override a Derby builtin function by creating a function with the same name.
This can give rise to wrong results.
> Consider the following user code:
> public class FakeSin
> {
>     public  static  Double  sin( Double input ) { return new Double( 3.0 ); }
> }
> Now run the following script:
> connect 'jdbc:derby:memory:db;create=true';
> values sin( 0.5 );
> create function sin( a double ) returns double language java parameter style java no
sql external name 'FakeSin.sin';
> values sin( 0.5 );
> values sin(  0.5 );
> Note the following:
> 1) The first invocation of sin() returns the expected result.
> 2) You are allowed to create a user-defined function named "sin" which can shadow the
builtin function.
> 3) The second invocation of sin() returns the result of running the builtin function.
This is because the second invocation is character-for-character identical to the first, so
Derby just uses the previously prepared statement. 
> 4) But the third invocation of sin() returns the result of running the user-defined function.
Note that the third invocation has an extra space in it, which causes Derby to compile it
from scratch, picking up the user-defined function instead of the builtin one.

This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

View raw message