Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-3652

Derby does not follow the SQL Standard when trying to map SQL routines to Java methods.



    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s:
    • Fix Version/s:
    • Component/s: SQL
    • Labels:
    • Issue & fix info:
      Release Note Needed


      I have only tested this in the 10.5 trunk. However, I suspect that this affects all previous releases of Derby as well.

      In resolving method signatures for function/procedure invocations, the SQL standard makes the following definitions in part 13, section 4.5 (parameter mapping). These definitions, in turn, refer to tables B-1 and B-3 in JDBC 3.0 Specification, Final Release, October 2001 ([JDBC]).

      • Simply mappable - This refers to the correspondence of SQL and Java types described in [JDBC] table B-1. This is the table which defines the mapping of SQL types to Java primitives.
      • Object mappable - This refers to the correspondence of SQL and Java types described in [JDBC] table B-3. This is the table which defines the mapping of SQL types to Java wrapper objects.
      • Output mappable - For OUT and INOUT parameters, this refers to a single element array whose cell is simply mappable or object mappable. E.g. Integer[] or float[].
      • Mappable - This means simply, object, or output mappable.
      • Result set mappable - This means a single element array whose cell is a type which implements either java.sql.ResultSet or sqlj.runtime.ResultSetIterator.

      Putting all of this together, section 4.5 continues:

      "A Java method with M parameters is mappable (to SQL) if and only if, for some N, 0 (zero) <= N <= M, the data types of the first N parameters are mappable, the last M - N parameters are result set mappable, and the result type is either simply mappable, object mappable, or void."

      Section 8.6 gives more detailed rules, but they are hard to follow. According to section 8.6, when resolving a routine invocation, Derby should expect to find one and only one static mappable method with the expected external name (Java class + method name).

      I believe that this is a fair description of the rules. This, at least, is what some other databases appear to do. See, for instance, http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.java/html/java/java126.htm and http://www.service-architecture.com/database/articles/mapping_sql_and_java_data_types.html

      We do not have a regression test which verifies that Derby applies the SQL standard resolution rules. There may be several divergences from the standard. This JIRA is a place to track those discrepancies. Here is one that I have noticed:

      The following SQL signature

      ( a int ) returns int

      should be mappable to any of the following Java signatures

      public static int f( int a )
      public static int f( Integer a )
      public static Integer f( int a )
      public static Integer f( Integer a )

      However, I observe that Derby is only able to resolve the first and third signatures (the ones with primitive arguments). I will attach a test case showing this problem.

      I will also attach an html table summarizing the simply and object mappable rules.


        1. SignatureMapping.html
          6 kB
          Richard N. Hillegas
        2. SignatureProblems.java
          0.4 kB
          Richard N. Hillegas
        3. signatureProblems.sql
          1.0 kB
          Richard N. Hillegas
        4. derby-3652-01-aa-mixTypesOnFirstPass.diff
          0.7 kB
          Richard N. Hillegas
        5. SignatureMapping.html
          6 kB
          Richard N. Hillegas
        6. derby-3652-01-ab-mixTypesOnFirstPass.diff
          2 kB
          Richard N. Hillegas
        7. derby-3652-01-ac-mixTypesOnFirstPass.diff
          11 kB
          Richard N. Hillegas
        8. derby-3652-badmatches.diff
          16 kB
          Richard N. Hillegas
        9. badsignatures.sql
          2 kB
          Richard N. Hillegas
        10. derby-3652-01-ad-mixTypesOnFirstPass.diff
          38 kB
          Richard N. Hillegas
        11. derby-3652-02-aa-dontWidenExceptForSmalllint.diff
          4 kB
          Richard N. Hillegas
        12. derby-3652-02-ab-dontWidenExceptForSmalllint.diff
          39 kB
          Richard N. Hillegas
        13. derby-3652-03-aa-dontWidenSmallint.diff
          6 kB
          Richard N. Hillegas
        14. derby-3652-03-ab-dontWidenSmallint.diff
          6 kB
          Richard N. Hillegas
        15. SignatureMapping.html
          7 kB
          Richard N. Hillegas
        16. derby-3652-04-aa-deprecateJavaRules.diff
          5 kB
          Richard N. Hillegas
        17. derby-3652-05-aa-moreTests.diff
          32 kB
          Richard N. Hillegas
        18. derby-3652-06-aa-dontWidenString.diff
          2 kB
          Richard N. Hillegas
        19. derby-3652-07-aa-dontWidenBigDecimal.diff
          42 kB
          Richard N. Hillegas
        20. derby-3652-08-aa-dontWidenAtAll.diff
          3 kB
          Richard N. Hillegas
        21. derby-3652-09-aa-mixedTypes.diff
          2 kB
          Richard N. Hillegas
        22. derby-3652-10-aa-SignatureChecker.diff
          23 kB
          Richard N. Hillegas
        23. derby-3652-11-aa-binaryTests.diff
          8 kB
          Richard N. Hillegas
        24. derby-3652-12-aa-charAndLongvarchar.diff
          4 kB
          Richard N. Hillegas
        25. derby-3652-13-aa-decimalDateTimeTimestamp.diff
          37 kB
          Richard N. Hillegas
        26. derby-3652-14-aa-blobClobTests.diff
          28 kB
          Richard N. Hillegas
        27. releaseNote.html
          6 kB
          Richard N. Hillegas

          Issue Links



              • Assignee:
                rhillegas Richard N. Hillegas
                rhillegas Richard N. Hillegas
              • Votes:
                0 Vote for this issue
                0 Start watching this issue


                • Created: