[jira] Created: (DERBY-3652) Derby does not follow the SQL Standard when trying to map SQL routines to Java methods.

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

                 Key: DERBY-3652
                 URL: https://issues.apache.org/jira/browse/DERBY-3652
             Project: Derby
          Issue Type: Bug
          Components: SQL
    Affects Versions: 10.5.0.0
            Reporter: Rick Hillegas


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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-3652?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-3652:
---------------------------------

    Attachment: SignatureMapping.html

Attaching SignatureMapping.html. This summarizes the simply mappable and object mappable rules.

> Derby does not follow the SQL Standard when trying to map SQL routines to Java methods.
> ---------------------------------------------------------------------------------------
>
>                 Key: DERBY-3652
>                 URL: https://issues.apache.org/jira/browse/DERBY-3652
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.0.0
>            Reporter: Rick Hillegas
>         Attachments: SignatureMapping.html
>
>
> 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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-3652?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-3652:
---------------------------------

    Attachment: signatureProblems.sql
                SignatureProblems.java

Attaching a Java class (SignatureProblems.java) and sql script (signatureProblems.sql) which show the following problem:

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

> Derby does not follow the SQL Standard when trying to map SQL routines to Java methods.
> ---------------------------------------------------------------------------------------
>
>                 Key: DERBY-3652
>                 URL: https://issues.apache.org/jira/browse/DERBY-3652
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.0.0
>            Reporter: Rick Hillegas
>         Attachments: SignatureMapping.html, SignatureProblems.java, signatureProblems.sql
>
>
> 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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-3652?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-3652:
---------------------------------

    Attachment: derby-3652-01-aa-mixTypesOnFirstPass.diff

Attaching derby-3652-01-aa-mixTypesOnFirstPass.diff. This makes a one line change to ClassInspector, which lets the attached problem case run correctly. I have not run regression tests yet. If this turns out to be the solution, we will need to add some more regression test cases.

The changed line (in the method resolver) used to remove methods from consideration which had wrapper arguments rather than primitive arguments. According to the header comment on resolveMethod(), those methods were supposed to be considered during a second pass through the methods--but this check in the first pass effectively excluded these methods from later consideration. This logic is very tricky and not very well documented. I have changed a boolean argument from false to true. The meaning of this argument is "consider matching wrappers as well as primitives". If I make this change, then the argument could actually be removed because the affected method would then always be called with the argument set to true. This change, however small, makes me uneasy. I will write some more tests to see if this change breaks anything obvious. However, it would be great if this tricky code could be explained by someone who has been through it before.

> Derby does not follow the SQL Standard when trying to map SQL routines to Java methods.
> ---------------------------------------------------------------------------------------
>
>                 Key: DERBY-3652
>                 URL: https://issues.apache.org/jira/browse/DERBY-3652
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.0.0
>            Reporter: Rick Hillegas
>         Attachments: derby-3652-01-aa-mixTypesOnFirstPass.diff, SignatureMapping.html, SignatureProblems.java, signatureProblems.sql
>
>
> 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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-3652?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-3652:
---------------------------------

    Attachment: SignatureMapping.html

Attaching new version of SignatureMapping.html. The previous version located BigDecimal in the wrong package.

> Derby does not follow the SQL Standard when trying to map SQL routines to Java methods.
> ---------------------------------------------------------------------------------------
>
>                 Key: DERBY-3652
>                 URL: https://issues.apache.org/jira/browse/DERBY-3652
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.0.0
>            Reporter: Rick Hillegas
>         Attachments: derby-3652-01-aa-mixTypesOnFirstPass.diff, SignatureMapping.html, SignatureMapping.html, SignatureProblems.java, signatureProblems.sql
>
>
> 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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-3652?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-3652:
---------------------------------

    Attachment: derby-3652-01-ab-mixTypesOnFirstPass.diff

Attaching second rev of patch, derby-3652-01-ab-mixTypesOnFirstPass.diff. Again, no new tests yet. This version passes the regression tests. The first version failed because the database metadata inspection of indexes ended up relying on there being only one qualifying overload of IndexDescriptorImpl.getKeyColumnPosition(). Unfortunately, there are two which qualify under the ANSI method resolution rules, resulting in an ambiguity:

public int getKeyColumnPosition(int heapColumnPosition)
public java.lang.Integer getKeyColumnPosition(java.lang.Integer heapColumnPosition)

That is, internal Derby code relies on non-ANSI method resolution rules. Two approaches to this problem occurred to me:

1) Rework the internal Derby methods to remove the ambiguities.

2) Make the method resolver sensitive to whether it is resolving an internal Derby method--user written procedures and functions are not allowed to bind to internal Derby methods.

I thought that (1) was a bit more straightforward but also more likely to have edge cases which we don't test for today. I opted for (2) as the less risky alternative.

> Derby does not follow the SQL Standard when trying to map SQL routines to Java methods.
> ---------------------------------------------------------------------------------------
>
>                 Key: DERBY-3652
>                 URL: https://issues.apache.org/jira/browse/DERBY-3652
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.0.0
>            Reporter: Rick Hillegas
>         Attachments: derby-3652-01-aa-mixTypesOnFirstPass.diff, derby-3652-01-ab-mixTypesOnFirstPass.diff, SignatureMapping.html, SignatureMapping.html, SignatureProblems.java, signatureProblems.sql
>
>
> 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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-3652?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12593838#action_12593838 ]

Daniel John Debrunner commented on DERBY-3652:
----------------------------------------------

I would advise approach 1) I would guess there is no path to call the methods with the Integer signature.

> Derby does not follow the SQL Standard when trying to map SQL routines to Java methods.
> ---------------------------------------------------------------------------------------
>
>                 Key: DERBY-3652
>                 URL: https://issues.apache.org/jira/browse/DERBY-3652
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.0.0
>            Reporter: Rick Hillegas
>         Attachments: derby-3652-01-aa-mixTypesOnFirstPass.diff, derby-3652-01-ab-mixTypesOnFirstPass.diff, SignatureMapping.html, SignatureMapping.html, SignatureProblems.java, signatureProblems.sql
>
>
> 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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-3652?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-3652:
---------------------------------

    Attachment: derby-3652-01-ac-mixTypesOnFirstPass.diff

Thanks for the feedback, Dan. Attaching a third rev of this patch, derby-3652-01-ac-mixTypesOnFirstPass.diff. This rev returns to the strategy of the first patch (always allowing the mixing of primitives with wrappers) but removes the Integer overload of IndexDescriptor.getKeyColumnPosition(). This involved adjusting a couple more files. Here are the contents of this third rev of the patch:

M      java/engine/org/apache/derby/iapi/services/loader/ClassInspector.java

Always allow mixing of primitives with wrappers when resolving method calls.

M      java/engine/org/apache/derby/impl/sql/compile/UnaryComparisonOperatorNode.java
M      java/engine/org/apache/derby/iapi/sql/dictionary/IndexRowGenerator.java
M      java/engine/org/apache/derby/catalog/IndexDescriptor.java
M      java/engine/org/apache/derby/catalog/types/IndexDescriptorImpl.java

Remove the Integer overload of IndexDescriptor.getKeyColumnPosition()

A      java/testing/org/apache/derbyTesting/functionTests/tests/lang/AnsiSignatures.java

This is the beginning of a collection of methods which we can use to test our Ansi compliance.

I'm running regression tests now.

> Derby does not follow the SQL Standard when trying to map SQL routines to Java methods.
> ---------------------------------------------------------------------------------------
>
>                 Key: DERBY-3652
>                 URL: https://issues.apache.org/jira/browse/DERBY-3652
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.0.0
>            Reporter: Rick Hillegas
>         Attachments: derby-3652-01-aa-mixTypesOnFirstPass.diff, derby-3652-01-ab-mixTypesOnFirstPass.diff, derby-3652-01-ac-mixTypesOnFirstPass.diff, SignatureMapping.html, SignatureMapping.html, SignatureProblems.java, signatureProblems.sql
>
>
> 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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-3652?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12593906#action_12593906 ]

Rick Hillegas commented on DERBY-3652:
--------------------------------------

The regression tests pass with the third rev of the patch too.

> Derby does not follow the SQL Standard when trying to map SQL routines to Java methods.
> ---------------------------------------------------------------------------------------
>
>                 Key: DERBY-3652
>                 URL: https://issues.apache.org/jira/browse/DERBY-3652
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.0.0
>            Reporter: Rick Hillegas
>         Attachments: derby-3652-01-aa-mixTypesOnFirstPass.diff, derby-3652-01-ab-mixTypesOnFirstPass.diff, derby-3652-01-ac-mixTypesOnFirstPass.diff, SignatureMapping.html, SignatureMapping.html, SignatureProblems.java, signatureProblems.sql
>
>
> 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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-3652?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12595154#action_12595154 ]

Thomas Nielsen commented on DERBY-3652:
---------------------------------------

Even though it does not have "patch available" checked, I went ahead and tested the third patch.

Regression tests run sucessfully for me as well, and the patch fixes the broken behavior for int/Integer mapping. I extended the attached SignatureProblems.java and accompanying sql script with double/Double conversions and they work fine with the patch too.

+1 to commit the third patch as is, and follow up later with a proper test case.

> Derby does not follow the SQL Standard when trying to map SQL routines to Java methods.
> ---------------------------------------------------------------------------------------
>
>                 Key: DERBY-3652
>                 URL: https://issues.apache.org/jira/browse/DERBY-3652
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.0.0
>            Reporter: Rick Hillegas
>         Attachments: derby-3652-01-aa-mixTypesOnFirstPass.diff, derby-3652-01-ab-mixTypesOnFirstPass.diff, derby-3652-01-ac-mixTypesOnFirstPass.diff, SignatureMapping.html, SignatureMapping.html, SignatureProblems.java, signatureProblems.sql
>
>
> 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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-3652?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12595154#action_12595154 ]

thomanie edited comment on DERBY-3652 at 5/8/08 2:01 AM:
---------------------------------------------------------------

Even though it does not have "patch available" checked, I went ahead and tested the third patch.

Regression tests run sucessfully for me as well, and the patch fixes the broken behavior for int/Integer mapping. I extended the attached SignatureProblems.java and accompanying sql script with double/Double mapping - which still work with the patch applied.

+1 to commit the third patch as is, and follow up later with a proper test case.

      was (Author: thomanie):
    Even though it does not have "patch available" checked, I went ahead and tested the third patch.

Regression tests run sucessfully for me as well, and the patch fixes the broken behavior for int/Integer mapping. I extended the attached SignatureProblems.java and accompanying sql script with double/Double conversions and they work fine with the patch too.

+1 to commit the third patch as is, and follow up later with a proper test case.
 

> Derby does not follow the SQL Standard when trying to map SQL routines to Java methods.
> ---------------------------------------------------------------------------------------
>
>                 Key: DERBY-3652
>                 URL: https://issues.apache.org/jira/browse/DERBY-3652
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.0.0
>            Reporter: Rick Hillegas
>         Attachments: derby-3652-01-aa-mixTypesOnFirstPass.diff, derby-3652-01-ab-mixTypesOnFirstPass.diff, derby-3652-01-ac-mixTypesOnFirstPass.diff, SignatureMapping.html, SignatureMapping.html, SignatureProblems.java, signatureProblems.sql
>
>
> 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.

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.