[
https://issues.apache.org/jira/browse/DERBY-4357?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-4357:
---------------------------------
Attachment: derby-4357-02-ac-passThrough.diff
Attaching derby-4357-02-ac-passThrough.diff. This is an initial implementation
of this feature.
Chris, you are welcome to take this for a test-drive and let me know about bugs
and whether this solves your problem. Thanks.
Here is the approach taken by this patch:
1) The code generator decides whether to push projections and restrictions to
the table function.
2) The code generator relies on the fact that the optimizer inserts a
ProjectRestrict node above the table function. As its name implies, the PR node
holds the following:
a) The projection of the table function. This is the list of all columns in the
table function which are referenced in the query.
b) The restriction of the table function. This is all of the WHERE clause
fragments which can be evaluated using only columns from the table function.
3) The code generator further relies on the fact that the optimizer has turned
on the isQualifier flag on all predicates in the restriction which can be
pushed into the table function. These are predicates of the form
column OP constant
where OP is one of the relational operators:
< <= = > >= IS NULL IS NOT NULL
4) At code generation time, the PR node checks to see whether its child is a
FromVTI node for a RestrictedVTI. If so, the PR node tells its child to
construct the column list and Restriction which will be passed to the
RestrictedVTI at run-time.
Note that if the child FromVTI doesn't understand the predicates which are
passed to it, then the FromVTI computes a null Restriction. Over time, we can
make this logic smarter and pass more complicated Restrictions to table
functions.
5) At run time, the VTIResultSet does the following:
a) Clones the Restriction and plugs parameters into it in case any of the
ColumnQualifiers refer to ? parameters.
b) Stuffs the column list and Restriction into the RestrictedVTI by calling the
initScan() method of the RestrictedVTI.
Touches the following files:
M java/engine/org/apache/derby/vti/Restriction.java
Explicit support for IS NULL and IS NOT NULL predicates.
M java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java
Bind-time changes so that the declared return type of the Java method bound to
a table function can be a subtype of ResultSet and not just a ResultSet.
M java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
Code-generation-time change: step (4) described above.
M java/engine/org/apache/derby/impl/sql/compile/FromVTI.java
Mostly code-generation-time changes to support step (4) above.
M
java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
M java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
M java/engine/org/apache/derby/impl/sql/execute/VTIResultSet.java
Run-time changes to support step (5) above.
M tools/javadoc/publishedapi.ant
Wire the new public api into the published javadoc.
A
java/testing/org/apache/derbyTesting/functionTests/tests/lang/IntegerArrayVTI.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
A
java/testing/org/apache/derbyTesting/functionTests/tests/lang/RestrictedVTITest.java
A couple initial tests to verify the soundness of this approach. Follow-on
patches will supply more tests.
> TableFunctions provide no information to limit underlying query
> ---------------------------------------------------------------
>
> Key: DERBY-4357
> URL: https://issues.apache.org/jira/browse/DERBY-4357
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0
> Environment: ALL
> Reporter: Chris Goodacre
> Assignee: Rick Hillegas
> Attachments: derby-4357-01-aa-publicAPI.diff,
> derby-4357-02-ac-passThrough.diff, RestrictedTableFunctions.html,
> RestrictedTableFunctions.html, RestrictedTableFunctions.html,
> RestrictedTableFunctions.html, RestrictedTableFunctions.html
>
>
> The API specification for TableFunctions cannot provide information to the
> implementer of the TableFunction about the details of the query. For
> example:
> (a) I defined a table function named MyFunction with columns a,b, & c
> (b) I bind the table function properly using the CREATE FUNCTION SQL.
> User executes the following SQL:
> select a,b from table ( MyFunction() ) where c = 123
> Without passing the column list and/or where clause as arguments to the table
> function, my implementation can not know that it only needs two of the three
> columns, and only rows where c = 123.
> For TableFunctions that are built to integrate distant/legacy data, the cost
> of the query can be prohibitive. It would be better if information
> regarding the columns in the select and restrictions from the where clause
> could be passed to the developer.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
