Derby
  1. Derby
  2. DERBY-5945

Inappropriate error message when calling a procedure as a function (as if it had a return value)

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.10.1.1
    • Component/s: JDBC, SQL
    • Labels:
      None
    • Urgency:
      Normal
    • Bug behavior facts:
      Seen in production
    1. derby-2927-5945-diag.diff
      42 kB
      Dag H. Wanvik
    2. derby-2927-5945-diag.stat
      0.4 kB
      Dag H. Wanvik
    3. derby-2927-5945-diag-2.diff
      43 kB
      Dag H. Wanvik
    4. derby-2927-5945-diag-2.stat
      0.5 kB
      Dag H. Wanvik
    5. derby-2927-5945-diag-3.diff
      45 kB
      Dag H. Wanvik
    6. derby-2927-5945-diag-3.stat
      0.5 kB
      Dag H. Wanvik
    7. derby-5945.diff
      117 kB
      Dag H. Wanvik
    8. derby-5945.stat
      0.5 kB
      Dag H. Wanvik

      Issue Links

        Activity

        Hide
        Holger Rehn added a comment -

        When a procedure is created and later called with non-matching parameters (namely with a return value), Derby raises an error like
        'PROC' is not recognized as a function or procedure.

        But the procedure exists. If trying to re-create the procedure, Derby correctly throws an error "PROCEDURE 'PROC' already exists.", and the procedure can be dropped via "drop procedure PROC".

        to reproduce just execute...

        "CREATE PROCEDURE PROC( inout ret in ) parameter style java modifies sql data language java external name 'DBProcs.PROC'"
        "{?=call PROC(1)}"

        The error message should point out, that either the procedure cannot return a value or that no matching procedure can be found.

        Cheers,
        momo

        Show
        Holger Rehn added a comment - When a procedure is created and later called with non-matching parameters (namely with a return value), Derby raises an error like 'PROC' is not recognized as a function or procedure. But the procedure exists. If trying to re-create the procedure, Derby correctly throws an error "PROCEDURE 'PROC' already exists.", and the procedure can be dropped via "drop procedure PROC". to reproduce just execute... "CREATE PROCEDURE PROC( inout ret in ) parameter style java modifies sql data language java external name 'DBProcs.PROC'" "{?=call PROC(1)}" The error message should point out, that either the procedure cannot return a value or that no matching procedure can be found. Cheers, momo
        Hide
        Dag H. Wanvik added a comment -

        Moved diagnostic patches over from DERBY-2927.

        Show
        Dag H. Wanvik added a comment - Moved diagnostic patches over from DERBY-2927 .
        Hide
        Dag H. Wanvik added a comment -

        For discussion of patches up and including derby-2927-5945-diag-3, see DERBY-2927.

        Show
        Dag H. Wanvik added a comment - For discussion of patches up and including derby-2927-5945-diag-3, see DERBY-2927 .
        Hide
        Dag H. Wanvik added a comment -

        Cf DERBY-2516: When this is fixed, enable LangProcedureTest.

        Show
        Dag H. Wanvik added a comment - Cf DERBY-2516 : When this is fixed, enable LangProcedureTest.
        Hide
        Knut Anders Hatlen added a comment -

        The patch looks like a good improvement. Some small comments about the test:

        • testDerby2927() should probably use this bug number (5945) instead.
        • The try/catch statements in testDerby2927() should call fail() last in the try blocks to report if the call unexpectedly succeeds.
        • When checking the message text, maybe use getMessage() instead of toString(), since toString() includes the exception class name, and I suspect the assert will fail on Java 5 (where it's a plain SQLException vs SQLSyntaxErrorException on Java 6 and later).
        • testDerby2927() ends with a call to rollback(), but it never disables auto-commit, so that's presumably a no-op.
        • The suite() method sets the locale by using territory based collation. Strictly speaking, changing the collation isn't necessary, so an alternative is to use TestConfiguration.singleUseDatabaseDecorator() + LocaleTestSetup.
        Show
        Knut Anders Hatlen added a comment - The patch looks like a good improvement. Some small comments about the test: testDerby2927() should probably use this bug number (5945) instead. The try/catch statements in testDerby2927() should call fail() last in the try blocks to report if the call unexpectedly succeeds. When checking the message text, maybe use getMessage() instead of toString(), since toString() includes the exception class name, and I suspect the assert will fail on Java 5 (where it's a plain SQLException vs SQLSyntaxErrorException on Java 6 and later). testDerby2927() ends with a call to rollback(), but it never disables auto-commit, so that's presumably a no-op. The suite() method sets the locale by using territory based collation. Strictly speaking, changing the collation isn't necessary, so an alternative is to use TestConfiguration.singleUseDatabaseDecorator() + LocaleTestSetup.
        Hide
        Dag H. Wanvik added a comment - - edited

        Thanks, Knut. Took those comments, and also cleaned up LangProcedureTest wrt long lines, whitespace and IDE warnings. I also went through it to vet any embedded/client differences and could remove one that was no longer needed (DERBY-2515).

        Uploaded a new patch derby-5945. Running regressions.

        [Update: regressions passed with no errors]

        Show
        Dag H. Wanvik added a comment - - edited Thanks, Knut. Took those comments, and also cleaned up LangProcedureTest wrt long lines, whitespace and IDE warnings. I also went through it to vet any embedded/client differences and could remove one that was no longer needed ( DERBY-2515 ). Uploaded a new patch derby-5945. Running regressions. [Update: regressions passed with no errors]
        Hide
        Dag H. Wanvik added a comment -

        Committed patch as svn 1429197, resolving.

        Show
        Dag H. Wanvik added a comment - Committed patch as svn 1429197, resolving.
        Hide
        Dag H. Wanvik added a comment -

        Holger, if you believe this patch solves the issue, please close it.

        Show
        Dag H. Wanvik added a comment - Holger, if you believe this patch solves the issue, please close it.
        Hide
        Holger Rehn added a comment -

        Dag, thanks for your efforts! I'm currently out of office but will take a look when I'm back in a week.

        Show
        Holger Rehn added a comment - Dag, thanks for your efforts! I'm currently out of office but will take a look when I'm back in a week.
        Hide
        Myrna van Lunteren added a comment -

        The change to outparams.out was also required for the j9_foundation canon. I updated this with revision 1431120:
        http://svn.apache.org/viewvc?view=revision&revision=1431120.

        Show
        Myrna van Lunteren added a comment - The change to outparams.out was also required for the j9_foundation canon. I updated this with revision 1431120: http://svn.apache.org/viewvc?view=revision&revision=1431120 .
        Hide
        Dag H. Wanvik added a comment -

        Sorry for missing that one, Myrna! Thanks

        Dag

        Show
        Dag H. Wanvik added a comment - Sorry for missing that one, Myrna! Thanks Dag
        Hide
        Holger Rehn added a comment -

        Hi Dag,

        I just build Derby from SVN trunk (revision 1433353) to test your changes but am now getting an NPE. Any idea what I am doing wrong?

        Cheers,
        momo

        java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:98)
        at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:142)
        at org.apache.derby.impl.jdbc.Util.javaException(Util.java:299)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:436)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2400)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:82)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:619)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:559)
        at com.sqlgmbh.tc.test.adapter.jdbc.OutboundTest.Proc(OutboundTest.java:367)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:601)
        at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:80)
        at org.testng.internal.Invoker.invokeMethod(Invoker.java:714)
        at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:901)
        at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1231)
        at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:127)
        at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:111)
        at org.testng.TestRunner.privateRun(TestRunner.java:767)
        at org.testng.TestRunner.run(TestRunner.java:617)
        at org.testng.SuiteRunner.runTest(SuiteRunner.java:334)
        at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:329)
        at org.testng.SuiteRunner.privateRun(SuiteRunner.java:291)
        at org.testng.SuiteRunner.run(SuiteRunner.java:240)
        at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
        at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
        at org.testng.TestNG.runSuitesSequentially(TestNG.java:1197)
        at org.testng.TestNG.runSuitesLocally(TestNG.java:1122)
        at org.testng.TestNG.run(TestNG.java:1030)
        at org.testng.remote.RemoteTestNG.run(RemoteTestNG.java:111)
        at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:204)
        at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:175)
        Caused by: java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:42)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:122)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:71)
        ... 33 more
        Caused by: java.lang.NullPointerException
        at org.apache.derby.impl.sql.compile.StaticMethodCallNode.coerceMethodParameter(StaticMethodCallNode.java:702)
        at org.apache.derby.impl.sql.compile.StaticMethodCallNode.resolveRoutine(StaticMethodCallNode.java:620)
        at org.apache.derby.impl.sql.compile.StaticMethodCallNode.bindExpression(StaticMethodCallNode.java:270)
        at org.apache.derby.impl.sql.compile.JavaToSQLValueNode.bindExpression(JavaToSQLValueNode.java:237)
        at org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(ResultColumn.java:737)
        at org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(ResultColumnList.java:831)
        at org.apache.derby.impl.sql.compile.RowResultSetNode.bindExpressions(RowResultSetNode.java:261)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(DMLStatementNode.java:227)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(DMLStatementNode.java:140)
        at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(CursorNode.java:267)
        at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:400)
        at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:99)
        at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1103)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:610)
        ... 26 more

        Show
        Holger Rehn added a comment - Hi Dag, I just build Derby from SVN trunk (revision 1433353) to test your changes but am now getting an NPE. Any idea what I am doing wrong? Cheers, momo java.sql.SQLException: Java exception: ': java.lang.NullPointerException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:98) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:142) at org.apache.derby.impl.jdbc.Util.javaException(Util.java:299) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:436) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2400) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:82) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:619) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:559) at com.sqlgmbh.tc.test.adapter.jdbc.OutboundTest.Proc(OutboundTest.java:367) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:80) at org.testng.internal.Invoker.invokeMethod(Invoker.java:714) at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:901) at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1231) at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:127) at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:111) at org.testng.TestRunner.privateRun(TestRunner.java:767) at org.testng.TestRunner.run(TestRunner.java:617) at org.testng.SuiteRunner.runTest(SuiteRunner.java:334) at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:329) at org.testng.SuiteRunner.privateRun(SuiteRunner.java:291) at org.testng.SuiteRunner.run(SuiteRunner.java:240) at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52) at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86) at org.testng.TestNG.runSuitesSequentially(TestNG.java:1197) at org.testng.TestNG.runSuitesLocally(TestNG.java:1122) at org.testng.TestNG.run(TestNG.java:1030) at org.testng.remote.RemoteTestNG.run(RemoteTestNG.java:111) at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:204) at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:175) Caused by: java.sql.SQLException: Java exception: ': java.lang.NullPointerException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:42) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:122) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:71) ... 33 more Caused by: java.lang.NullPointerException at org.apache.derby.impl.sql.compile.StaticMethodCallNode.coerceMethodParameter(StaticMethodCallNode.java:702) at org.apache.derby.impl.sql.compile.StaticMethodCallNode.resolveRoutine(StaticMethodCallNode.java:620) at org.apache.derby.impl.sql.compile.StaticMethodCallNode.bindExpression(StaticMethodCallNode.java:270) at org.apache.derby.impl.sql.compile.JavaToSQLValueNode.bindExpression(JavaToSQLValueNode.java:237) at org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(ResultColumn.java:737) at org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(ResultColumnList.java:831) at org.apache.derby.impl.sql.compile.RowResultSetNode.bindExpressions(RowResultSetNode.java:261) at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(DMLStatementNode.java:227) at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(DMLStatementNode.java:140) at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(CursorNode.java:267) at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:400) at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:99) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1103) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:610) ... 26 more
        Hide
        Dag H. Wanvik added a comment -

        Not sure. Seems there is a bug in the code base if you built correctly. Can you post a repro? I'll take a look at it.

        Show
        Dag H. Wanvik added a comment - Not sure. Seems there is a bug in the code base if you built correctly. Can you post a repro? I'll take a look at it.
        Hide
        Holger Rehn added a comment -

        Thanks for your fast reply. To reproduce the problem, simply execute the following few lines of code:

        Class.forName( "org.apache.derby.jdbc.EmbeddedDriver" );
        Connection conn = DriverManager.getConnection( "jdbc:derby:db;create=true", "user", "pwd" );
        Statement sta = conn.createStatement();

        try

        { sta.executeUpdate( "drop procedure \"PROC\"" ); }

        catch( Exception e )

        { // ignore, PROC doesn't exist when run for the first time }

        sta.execute( "create procedure PROC( inout x int ) parameter style java modifies sql data language java external name 'DUMMY.PROC'" );
        sta.execute( "

        {call PROC(1)}

        " );

        Please note, that there is only one parameter of type INOUT and the parameter value is provided directly.

        Beside the NPE thing, your fix to the original bug seems to work well. Should I close this ticket then or would you prefer to keep it open until you had a look into the NPE problem which didn't occur before?

        Cheers,
        momo

        Show
        Holger Rehn added a comment - Thanks for your fast reply. To reproduce the problem, simply execute the following few lines of code: Class.forName( "org.apache.derby.jdbc.EmbeddedDriver" ); Connection conn = DriverManager.getConnection( "jdbc:derby:db;create=true", "user", "pwd" ); Statement sta = conn.createStatement(); try { sta.executeUpdate( "drop procedure \"PROC\"" ); } catch( Exception e ) { // ignore, PROC doesn't exist when run for the first time } sta.execute( "create procedure PROC( inout x int ) parameter style java modifies sql data language java external name 'DUMMY.PROC'" ); sta.execute( " {call PROC(1)} " ); Please note, that there is only one parameter of type INOUT and the parameter value is provided directly. Beside the NPE thing, your fix to the original bug seems to work well. Should I close this ticket then or would you prefer to keep it open until you had a look into the NPE problem which didn't occur before? Cheers, momo
        Hide
        Dag H. Wanvik added a comment - - edited

        Thanks for trying it out! Please close this issue; what you are seeing is an unrelated error (a regression), I'll file a new issue for it. [Update: done; DERBY-6047].

        Show
        Dag H. Wanvik added a comment - - edited Thanks for trying it out! Please close this issue; what you are seeing is an unrelated error (a regression), I'll file a new issue for it. [Update: done; DERBY-6047] .

          People

          • Assignee:
            Dag H. Wanvik
            Reporter:
            Holger Rehn
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development