[
https://issues.apache.org/jira/browse/DERBY-4357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12751525#action_12751525
]
Rick Hillegas commented on DERBY-4357:
--------------------------------------
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
>
>
> 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.
