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:
Explicit support for IS NULL and IS NOT NULL predicates.
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.
Code-generation-time change: step (4) described above.
Mostly code-generation-time changes to support step (4) above.
Run-time changes to support step (5) above.
Wire the new public api into the published javadoc.
A couple initial tests to verify the soundness of this approach. Follow-on patches will supply more tests.