db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Chris Goodacre (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4357) TableFunctions provide no information to limit underlying query
Date Thu, 03 Sep 2009 18:10:57 GMT

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

Chris Goodacre commented on DERBY-4357:
---------------------------------------

I think it would be helpful to be a little clearer about the semantics and invocation of the
the RestrictedVTI interface methods.  I'd like to see something like this (if this is actually
correct):

There is no implicit or explicit order of invocation for setMaterializedColumnNames() and
setRestriction().

The setMaterializedColumnNames(...) method is guaranteed to be invoked only 1 time per TableFunction
execution.  This invocation must occur prior to any invocation of any of the following methods:
    getXXXXXX()
    next()
    isFirst()
    first()
    isLast()
    last()

The setRestriction(....) method is guaranteed to be invoked only 1 time per TableFunction
execution.   NOTE:  the Restriction class is a binary tree representing all restrictions in
the WHERE clause of the underlying query in which a column/field from the TableFunction is
compared against a constant value.    Specifically, setRestriction is *not* invoked once for
each restriction.  This invocation must occur prior to any invocation of the methods enumated
in setMaterializedColumnNames().

Btw- I realize that the API doc for Restriction also specifically states that it is a binary
tree, I just think it's helpful in the interface doc to head off any misunderstanding.


> 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
>         Attachments: 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.


Mime
View raw message