[
https://issues.apache.org/jira/browse/DERBY-5901?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13832548#comment-13832548
]
Knut Anders Hatlen commented on DERBY-5901:
-------------------------------------------
The functions that can be shadowed live in the SYSFUN schema and can be invoked
with a qualified name, like this:
values sysfun.sin(0.5);
See the discussion about SQL-path in DERBY-6362. I suppose Derby's current
behaviour is mostly consistent with what you'd expect if CURRENT_PATH =
(CURRENT_SCHEMA, SYSFUN). What does not seem to be as expected, I think, is that
values sin(0.5)
has different behaviour depending on whether it is actually compiled or just
fetched from the internal statement cache. The presence of an internal
statement cache should be invisible to the applications. The resolution of the
unqualified function name should happen when the application prepares the
statement and not be influenced by identical-looking, previously compiled
statements.
> 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)
