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