[jira] [Commented] (DERBY-5901) You can declare user-defined functions which shadow builtin functions by the same name.

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

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

Knut Anders Hatlen commented on DERBY-5901:
-------------------------------------------

I'm not sure. Normalizing statements before storing them in the cache would probably mean that we'd have to run them through parsing and binding, which is what the cache is there to avoid in the first place. Perhaps we could find a way to have CREATE FUNCTION evict affected statements from the cache if a function that shadows SYSFUN is created. Then we'd limit the performance hit to CREATE FUNCTION time and avoid the extra cost on each cache lookup.

One rather broad fix, that I think is possible in the current code without adding any new dependency tracking or parsing, is to make CREATE FUNCTION invalidate all statements in the cache whose original compilation schema is the same as the schema of the shadowing function. It is broader than it needs to be, since we only need to invalidate those statements that have unqualified invocations of the affected function. But if it is limited to only those CREATE FUNCTION calls that actually create a shadowing function, it might be an acceptable price to pay, and better than building something complex in order to improve the performance of the statement cache in some rare edge cases. (Maybe these cases are so rare that it would be acceptable to make it even simpler: clear the entire statement cache each time a shadowing function is created.)

> 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: 10.10.1.1
>            Reporter: Rick Hillegas
>              Labels: derby_triage10_10
>
> 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 was sent by Atlassian JIRA
(v6.1#6144)