db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4357) TableFunctions provide no information to limit underlying query
Date Wed, 28 Oct 2009 18:57:59 GMT

     [ 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:,,,,
>         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.

View raw message