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] Issue Comment Edited: (DERBY-4357) TableFunctions provide no information to limit underlying query
Date Fri, 11 Sep 2009 14:46:57 GMT

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

Rick Hillegas edited comment on DERBY-4357 at 9/11/09 7:46 AM:
---------------------------------------------------------------

Thanks for the additional comments, Knut and Chris. Some responses follow:

1) I do see the value of enforcing the restriction outside the table function even if the
restriction is pushed into the table function. I'll change the spec to say that's what we'll
do. However, heads up: I may revert back to the original contract if this turns out to be
difficult to implement. In that case, we can file a separate JIRA for this relaxed contract--it
would be backward-compatible with the original, more exacting contract.

2) In order to remove ambiguity about the order in which the RestrictedVTI methods are called,
I will combine them into a single initScan() method.

3) I will also clarify that initScan() is called once per scan and is called before any other
ResultSet methods are called.

4) It's certainly possible to build on this work and turn an IN list into a series of probes
of the table function. This should be possible for table functions which satisfiy the following
conditions:

a) their returned ResultSets implement RestrictedVTI

b) they are declared in classes which implement org.apache.derby.vti VTICosting, provided
that VTICosting.supportsMultipleInstantiations() returns true.

This, however, would be another JIRA. For the record, I don't think that you can get around
this problem by rewriting your IN list as a series of ORs. I think that the Derby optimizer
will outfox you and transform that series of ORs back into an IN list which won't operate
efficiently on a table function, even after this JIRA is done. See http://db.apache.org/derby/docs/10.5/tuning/tuning-single.html#rtuntransform590
I will note this in the spec.

5) I think that a related improvement would be the ability to optimize joins with table functions
which satisfy the conditions in (4). That's another JIRA, too.

6) I think that the LIKE and BETWEEN optimizations should play well with this feature, but
we'll have to prove that when this work is done. Those optimizations are described here: http://db.apache.org/derby/docs/10.5/tuning/tuning-single.html#rtuntransform139

7) I don't see much value in supporting the <> operator. However, if we added this operator
to the list of simple comparisons which can be pushed into the Derby store, then we could
piggyback the corresponding table function work on top of that effort.

What other operations would you like to push into table functions?

Thanks,
-Rick


      was (Author: rhillegas):
    Thanks for the additional comments, Knut and Chris. Some responses follow:

1) I do see the value of enforcing the restriction outside the table function even if the
restriction is pushed into the table function. I'll change the spec to say that's what we'll
do. However, heads up: I may revert back to the original contract if this turns out to be
difficult to implement. In that case, we can file a separate JIRA for this relaxed contract--it
would be backward-compatible with the original, more exacting contract.

2) In order to remove ambiguity about the order in which the RestrictedVTI methods are called,
I will combine them into a single initScan() method.

3) I will also clarify that initScan() is called once per scan and is called before any other
ResultSet methods are called.

4) It's certainly possible to build on this work and turn an IN list into a series of probes
of the table function. This should be possible for table functions which satisfiy the following
conditions:

a) their returned ResultSets implement RestrictedVTI

b) they are declared in classes which implement org.apache.derby.vti VTICosting, provided
that VTICosting.supportsMultipleInstantiations() returns true.

This, however, would be another JIRA. For the record, I don't think that you can get around
this problem by rewriting your IN list as a series of ORs. I think that the Derby optimizer
will outfox you and transform that series of ORs back into an IN list which won't operate
efficiently on a table function, even after this JIRA is done. See http://db.apache.org/derby/docs/10.5/tuning/tuning-single.html#rtuntransform590
I will note this in the spec.

5) I think that a related improvement would be the ability to optimize joins with table functions
which satisfy the conditions in (5). That's another JIRA, too.

6) I think that the LIKE and BETWEEN optimizations should play well with this feature, but
we'll have to prove that when this work is done. Those optimizations are described here: http://db.apache.org/derby/docs/10.5/tuning/tuning-single.html#rtuntransform139

7) I don't see much value in supporting the <> operator. However, if we added this operator
to the list of simple comparisons which can be pushed into the Derby store, then we could
piggyback the corresponding table function work on top of that effort.

What other operations would you like to push into table functions?

Thanks,
-Rick

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


Mime
View raw message