1. Derby
  2. DERBY-5901

You can declare user-defined functions which shadow builtin functions by the same name.


    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s:
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Deviation from standard, Wrong query result


      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.

        Issue Links


          Rick Hillegas created issue -
          Rick Hillegas made changes -
          Field Original Value New Value
          Bug behavior facts Wrong query result [ 10366 ] Deviation from standard,Wrong query result [ 10367, 10366 ]
          Rick Hillegas made changes -
          Link This issue is related to DERBY-672 [ DERBY-672 ]
          Knut Anders Hatlen made changes -
          Labels derby_triage10_10
          Urgency Normal [ 10052 ]
          Gavin made changes -
          Workflow jira [ 12721064 ] Default workflow, editable Closed status [ 12801950 ]
          Knut Anders Hatlen made changes -
          Link This issue is related to DERBY-6370 [ DERBY-6370 ]


            • Assignee:
              Rick Hillegas
            • Votes:
              1 Vote for this issue
              5 Start watching this issue


              • Created: