You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Daniel John Debrunner (JIRA)" <de...@db.apache.org> on 2006/02/22 20:59:39 UTC
[jira] Created: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
-----------------------------------------------------------------------------------------------
Key: DERBY-1030
URL: http://issues.apache.org/jira/browse/DERBY-1030
Project: Derby
Type: Bug
Components: SQL
Versions: 10.1.2.1
Reporter: Daniel John Debrunner
Assigned to: Mamta A. Satoor
Fix For: 10.2.0.0
The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1030?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12784809#action_12784809 ]
Rick Hillegas commented on DERBY-1030:
--------------------------------------
Thanks for catching that howler, Bryan. I have corrected the comment. Cheers.
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: https://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Assignee: Rick Hillegas
> Attachments: derby-1030-01-aa-disableOptimization.diff, derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1030?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-1030:
---------------------------------
Issue & fix info: [Patch Available, Repro attached] (was: [Repro attached])
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: https://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Assignee: Rick Hillegas
> Attachments: derby-1030-01-aa-disableOptimization.diff, derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1030?page=all ]
Daniel John Debrunner reassigned DERBY-1030:
--------------------------------------------
Assignee: Daniel John Debrunner
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: http://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Assigned To: Daniel John Debrunner
> Fix For: 10.2.2.0
>
> Attachments: derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1030?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas resolved DERBY-1030.
----------------------------------
Resolution: Fixed
Issue & fix info: [Repro attached] (was: [Repro attached, Patch Available])
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: https://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Assignee: Rick Hillegas
> Attachments: derby-1030-01-aa-disableOptimization.diff, derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Issue Comment Edited: (DERBY-1030) In some situations a
RETURNS NULL ON NULL function is called when one ot its parameters is NULL
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1030?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12784329#action_12784329 ]
Rick Hillegas edited comment on DERBY-1030 at 12/2/09 1:17 PM:
---------------------------------------------------------------
The bug is caused by an optimization intended to eliminate needless switching between the Java domain and the SQL domain.
Two compile-time classes manage this switching:
JavaToSQLNode - This class is responsible for changing Java data values into SQL data values that can be used by other expressions in the query. So for instance, this class is responsible for compiling the code which changes Integer and primitive int into SQLInteger.
SQLToJavaNode - This class is responsible for the reverse operation, that is, for turning SQL data values into Java data values before they are passed as arguments to Java methods. So for instance, this class is responsible for compiling the runtime code which changes a SQLInteger into a primitive int before invoking a Java method.
SQLToJavaNode also compiles the NULL ON NULL INPUT check: If the SQL data value is going to be used as the argument to a NULL ON NULL INPUT routine, then the SQLToJavaNode compiles short-circuiting logic that prevents the routine from being called if the SQL data value is null.
Some performance can be lost when switching between the Java and SQL domains. We eliminate this switching if a Java data value becomes a SQL data value only to become a Java data value immediately afterwards. This happens when the return value of a Function is passed as an argument to another Routine. E.g.:
values ( outerFunction( innerFunction( 1 ) ) )
In this case the AST shows a SQLToJavaNode wrapping a JavaToSQLNode returned by innerFunction. An optimization was put in to eliminate both conversion nodes in this case and to pass the return value of innerFunction directly as an argument to outerFunction.
This optimization has a couple problems:
1) If innerFunction is a NULL ON NULL INPUT Function and innerFunction is called with null inputs, then Derby tries to pass a null to outerFunction. This produces a verification error if outerFunction takes primitive arguments. DERBY-479 fixes this by eliminating the optimization in this case.
2) If outerFunction is a NULL ON NULL INPUT Function, then the elimination of the SQLToJavaNode means that the NULL ON NULL INPUT check is not performed. This is the bug described by this JIRA.
The fix is to also eliminate the optimization if the outer function is NULL ON NULL INPUT.
was (Author: rhillegas):
The bug is caused by an optimization intended to eliminate needless switching between the Java domain and the SQL domain.
Two compile-time classes manage this switching:
JavaToSQLNode - This class is responsible for the reverse operation, that is, for changing Java data values into SQL data values that can be used by other expressions in the query. So for instance, this class is responsible for compiling the code which changes Integer and primitive int into SQLInteger.
SQLToJavaNode - This class is responsible for turning SQL data values into Java data values before they are passed as arguments to Java methods. So for instance, this class is responsible for compiling the runtime code which changes a SQLInteger into a primitive int before invoking a Java method.
SQLToJavaNode also compiles the NULL ON NULL INPUT check: If the SQL data value is going to be used as the argument to a NULL ON NULL INPUT routine, then the SQLToJavaNode compiles short-circuiting logic that prevents the routine from being called if the SQL data value is null.
Some performance can be lost when switching between the Java and SQL domains. We eliminate this switching if a Java data value becomes a SQL data value only to become a Java data value immediately afterwards. This happens when the return value of a Function is passed as an argument to another Routine. E.g.:
values ( outerFunction( innerFunction( 1 ) ) )
In this case the AST shows a SQLToJavaNode wrapping a JavaToSQLNode returned by innerFunction. An optimization was put in to eliminate both conversion nodes in this case and to pass the return value of innerFunction directly as an argument to outerFunction.
This optimization has a couple problems:
1) If innerFunction is a NULL ON NULL INPUT Function and innerFunction is called with null inputs, then Derby tries to pass a null to outerFunction. This produces a verification error if outerFunction takes primitive arguments. DERBY-479 fixes this by eliminating the optimization in this case.
2) If outerFunction is a NULL ON NULL INPUT Function, then the elimination of the SQLToJavaNode means that the NULL ON NULL INPUT check is not performed. This is the bug described by this JIRA.
The fix is to also eliminate the optimization if the outer function is NULL ON NULL INPUT.
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: https://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Assignee: Rick Hillegas
> Attachments: derby-1030-01-aa-disableOptimization.diff, derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
Posted by "Mamta A. Satoor (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1030?page=all ]
Mamta A. Satoor reassigned DERBY-1030:
--------------------------------------
Assignee: (was: Mamta A. Satoor)
Busy with grant revoke work, so unassigning myself from this one.
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: http://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Fix For: 10.2.0.0
>
> Attachments: derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1030?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas reassigned DERBY-1030:
------------------------------------
Assignee: Rick Hillegas
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: https://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Assignee: Rick Hillegas
> Attachments: derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1030?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden reassigned DERBY-1030:
-------------------------------------
Assignee: (was: Daniel John Debrunner)
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: https://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Attachments: derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1030?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12784968#action_12784968 ]
Rick Hillegas commented on DERBY-1030:
--------------------------------------
Committed derby-1030-01-aa-disableOptimization.diff at subversion revision 886277.
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: https://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Assignee: Rick Hillegas
> Attachments: derby-1030-01-aa-disableOptimization.diff, derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1030?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12784329#action_12784329 ]
Rick Hillegas commented on DERBY-1030:
--------------------------------------
The bug is caused by an optimization intended to eliminate needless switching between the Java domain and the SQL domain.
Two compile-time classes manage this switching:
JavaToSQLNode - This class is responsible for turning SQL data values into Java data values before they are passed as arguments to Java methods. So for instance, this class is responsible for compiling the runtime code which changes a SQLInteger into a primitive int before invoking a Java method.
SQLToJavaNode - This class is responsible for the reverse operation, that is, for changing Java data values into SQL data values that can be used by other expressions in the query. So for instance, this class is responsible for compiling the code which changes Integer and primitive int into SQLInteger.
SQLToJavaNode also compiles the NULL ON NULL INPUT check: If the SQL data value is going to be used as the argument to a NULL ON NULL INPUT routine, then the SQLToJavaNode compiles short-circuiting logic that prevents the routine from being called if the SQL data value is null.
Some performance can be lost when switching between the Java and SQL domains. We eliminate this switching if a Java data value becomes a SQL data value only to become a Java data value immediately afterwards. This happens when the return value of a Function is passed as an argument to another Routine. E.g.:
values ( outerFunction( innerFunction( 1 ) ) )
In this case the AST shows a SQLToJavaNode wrapping a JavaToSQLNode returned by innerFunction. An optimization was put in to eliminate both conversion nodes in this case and to pass the return value of innerFunction directly as an argument to outerFunction.
This optimization has a couple problems:
1) If innerFunction is a NULL ON NULL INPUT Function and innerFunction is called with null inputs, then Derby tries to pass a null to outerFunction. This produces a verification error if outerFunction takes primitive arguments. DERBY-479 fixes this by eliminating the optimization in this case.
2) If outerFunction is a NULL ON NULL INPUT Function, then the elimination of the SQLToJavaNode means that the NULL ON NULL INPUT check is not performed. This is the bug described by this JIRA.
The fix is to also eliminate the optimization if the outer function is NULL ON NULL INPUT.
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: https://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Assignee: Rick Hillegas
> Attachments: derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1030?page=all ]
Rick Hillegas updated DERBY-1030:
---------------------------------
Fix Version/s: 10.2.2.0
(was: 10.2.1.0)
Moving to 10.2.2.0.
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: http://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Fix For: 10.2.2.0
>
> Attachments: derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1030?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-1030:
--------------------------------------
Issue & fix info: [Repro attached]
Bug behavior facts: [Wrong query result]
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: https://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Attachments: derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1030?page=all ]
Daniel John Debrunner updated DERBY-1030:
-----------------------------------------
Attachment: derby479.java
derby479.sql
Called derby-479 as that's the issue I was looking at when I saw this related but separate issue.
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: http://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Assignee: Mamta A. Satoor
> Fix For: 10.2.0.0
> Attachments: derby479.java, derby479.sql
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Issue Comment Edited: (DERBY-1030) In some situations a
RETURNS NULL ON NULL function is called when one ot its parameters is NULL
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1030?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12784329#action_12784329 ]
Rick Hillegas edited comment on DERBY-1030 at 12/2/09 1:14 PM:
---------------------------------------------------------------
The bug is caused by an optimization intended to eliminate needless switching between the Java domain and the SQL domain.
Two compile-time classes manage this switching:
JavaToSQLNode - This class is responsible for the reverse operation, that is, for changing Java data values into SQL data values that can be used by other expressions in the query. So for instance, this class is responsible for compiling the code which changes Integer and primitive int into SQLInteger.
SQLToJavaNode - This class is responsible for turning SQL data values into Java data values before they are passed as arguments to Java methods. So for instance, this class is responsible for compiling the runtime code which changes a SQLInteger into a primitive int before invoking a Java method.
SQLToJavaNode also compiles the NULL ON NULL INPUT check: If the SQL data value is going to be used as the argument to a NULL ON NULL INPUT routine, then the SQLToJavaNode compiles short-circuiting logic that prevents the routine from being called if the SQL data value is null.
Some performance can be lost when switching between the Java and SQL domains. We eliminate this switching if a Java data value becomes a SQL data value only to become a Java data value immediately afterwards. This happens when the return value of a Function is passed as an argument to another Routine. E.g.:
values ( outerFunction( innerFunction( 1 ) ) )
In this case the AST shows a SQLToJavaNode wrapping a JavaToSQLNode returned by innerFunction. An optimization was put in to eliminate both conversion nodes in this case and to pass the return value of innerFunction directly as an argument to outerFunction.
This optimization has a couple problems:
1) If innerFunction is a NULL ON NULL INPUT Function and innerFunction is called with null inputs, then Derby tries to pass a null to outerFunction. This produces a verification error if outerFunction takes primitive arguments. DERBY-479 fixes this by eliminating the optimization in this case.
2) If outerFunction is a NULL ON NULL INPUT Function, then the elimination of the SQLToJavaNode means that the NULL ON NULL INPUT check is not performed. This is the bug described by this JIRA.
The fix is to also eliminate the optimization if the outer function is NULL ON NULL INPUT.
was (Author: rhillegas):
The bug is caused by an optimization intended to eliminate needless switching between the Java domain and the SQL domain.
Two compile-time classes manage this switching:
JavaToSQLNode - This class is responsible for turning SQL data values into Java data values before they are passed as arguments to Java methods. So for instance, this class is responsible for compiling the runtime code which changes a SQLInteger into a primitive int before invoking a Java method.
SQLToJavaNode - This class is responsible for the reverse operation, that is, for changing Java data values into SQL data values that can be used by other expressions in the query. So for instance, this class is responsible for compiling the code which changes Integer and primitive int into SQLInteger.
SQLToJavaNode also compiles the NULL ON NULL INPUT check: If the SQL data value is going to be used as the argument to a NULL ON NULL INPUT routine, then the SQLToJavaNode compiles short-circuiting logic that prevents the routine from being called if the SQL data value is null.
Some performance can be lost when switching between the Java and SQL domains. We eliminate this switching if a Java data value becomes a SQL data value only to become a Java data value immediately afterwards. This happens when the return value of a Function is passed as an argument to another Routine. E.g.:
values ( outerFunction( innerFunction( 1 ) ) )
In this case the AST shows a SQLToJavaNode wrapping a JavaToSQLNode returned by innerFunction. An optimization was put in to eliminate both conversion nodes in this case and to pass the return value of innerFunction directly as an argument to outerFunction.
This optimization has a couple problems:
1) If innerFunction is a NULL ON NULL INPUT Function and innerFunction is called with null inputs, then Derby tries to pass a null to outerFunction. This produces a verification error if outerFunction takes primitive arguments. DERBY-479 fixes this by eliminating the optimization in this case.
2) If outerFunction is a NULL ON NULL INPUT Function, then the elimination of the SQLToJavaNode means that the NULL ON NULL INPUT check is not performed. This is the bug described by this JIRA.
The fix is to also eliminate the optimization if the outer function is NULL ON NULL INPUT.
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: https://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Assignee: Rick Hillegas
> Attachments: derby-1030-01-aa-disableOptimization.diff, derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1030?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12784566#action_12784566 ]
Bryan Pendleton commented on DERBY-1030:
----------------------------------------
Thanks for the clear and detailed explanation, Rick, it was very interesting!
> JavaToSQLNode - This class is responsible for turning SQL data values into Java data values before
> they are passed as arguments to Java methods. So for instance, this class is responsible for compiling
> the runtime code which changes a SQLInteger into a primitive int before invoking a Java method.
>
> SQLToJavaNode - This class is responsible for the reverse operation, that is, for changing Java
> data values into SQL data values that can be used by other expressions in the query. So for instance,
> this class is responsible for compiling the code which changes Integer and primitive int into SQLInteger.
Did I misread this? It seems backward to me. It seems like JavaToSQLNode should
be the one which turns a Java value into a SQL value, and SQLToJavaNode should be the one which
turns a SQL value into a Java value.
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: https://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Assignee: Rick Hillegas
> Attachments: derby-1030-01-aa-disableOptimization.diff, derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1030?page=all ]
Daniel John Debrunner updated DERBY-1030:
-----------------------------------------
Fix Version/s: (was: 10.2.2.0)
Not going to get to this in the 10.2.2 timeframe
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: http://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Assigned To: Daniel John Debrunner
> Attachments: derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1030?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-1030:
---------------------------------
Attachment: derby-1030-01-aa-disableOptimization.diff
Attaching derby-1030-01-aa-disableOptimization.diff. This removes the over-eager optimization of switching between language domains when the outer Function is NULL ON NULL INPUT.
Touches the following files:
M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
Removes the optimization.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/RoutineTest.java
As part of DERBY-479, a test case was added for DERBY-1030, enshrining the wrong behavior described by this bug. The test case is changed to enshrine the correct behavior.
The regression tests passed cleanly for me except for an error in GeneratedColumnsTest which was recently introduced into the trunk by other work:
There was 1 failure:
1) test_031_derby_4413(org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest)junit.framework.ComparisonFailure: expected:<[1]> but was:<[2]>
at org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsHelper.assertResults(GeneratedColumnsHelper.java:346)
at org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsHelper.assertResults(GeneratedColumnsHelper.java:310)
at org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.test_031_derby_4413(GeneratedColumnsTest.java:5442)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
at junit.extensions.TestSetup.run(TestSetup.java:25)
at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
at junit.extensions.TestSetup.run(TestSetup.java:25)
FAILURES!!!
Tests run: 34, Failures: 1, Errors: 0
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: https://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Assignee: Rick Hillegas
> Attachments: derby-1030-01-aa-disableOptimization.diff, derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-1030) In some situations a RETURNS NULL ON
NULL function is called when one ot its parameters is NULL
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1030?page=all ]
Rick Hillegas updated DERBY-1030:
---------------------------------
Urgency: Normal
> In some situations a RETURNS NULL ON NULL function is called when one ot its parameters is NULL
> -----------------------------------------------------------------------------------------------
>
> Key: DERBY-1030
> URL: http://issues.apache.org/jira/browse/DERBY-1030
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.2.1
> Reporter: Daniel John Debrunner
> Fix For: 10.2.0.0
>
> Attachments: derby479.java, derby479.sql
>
>
> The NULL argument to the function has to come from another function and that function's Java method has to return a Java object type corresponding to a fixed length SQL type, such as DATE, TIME and TIMESTAMP.
> Will attach repro scripts.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira