db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5901) You can declare user-defined functions which shadow builtin functions by the same name.
Date Sun, 09 Sep 2012 20:50:07 GMT

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

Kathey Marsden commented on DERBY-5901:

I think the most likely trouble for existing applications with option 1 would be if  someone
implemented a function before it was added as a builtin function in Derby later.  For example
these added in 10.3:

 Similarly if an application written against 10.9  creates a function by the same name as
 a builtin function that is  added sometime in the future, it would  also break when upgraded
to the new derby version which has the new builtin function.

I am not sure what the correct answer is but wonder if the standard speaks to this point wrt
non-reserved words like SIN and what other database products do.  I am concerned about introducing
an incompatibility that is not specific to one release but has the potential to create an
incompatibility for every function added at a time when the likely affected applications are
quite old and possibly don't have developers ready to investigate and fix such an issue. 

> 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