Hi David,
Thanks for the additional clarifications. Some more responses inline...
On 3/13/13 7:09 AM, drv wrote:
Hi Rick - further thoughts below.
(0 - New issue...)
I have found another big issue for us by the way: It appears that Table
Functions do not recognize the IQualifyable and Pushable interfaces anymore
- this is understandable given that RestrictedVTI was intended to replace
them. However, the methods on the 2 old interfaces passed a VTIEnvironment
object which RestrictedVTI.initScan() does not. This object crucially holds
the "orginalSQL" string which we need *at execution time* in GaianDB.
Methods on the VTICosting interface also provide the VTIEnvironment, but
they are only called once by Derby (and on a separate instance of the TF
class), when it fetches stats for the table function. This means that we
would only ever know the original SQL of the FIRST SQL query that referenced
the table function - which is not very useful.
By the way - we need the original SQL for many reasons: e.g.: to extract
HINTs from it and to extract complex predicates (involving functions etc -
which cannot be expressed with a Restriction) - through shallow parsing.
Also - In response to your comments:
1)
Rick> The static typing of table functions is a feature of the ANSI/ISO
syntax. Each possible table shape needs to be bound by its own CREATE
FUNCTION statement. I think you have discovered that you can hide the
messy arguments by wrapping the table function invocations in helper
views. You may find that varargs routines (to be introduced by Derby
10.10) will help you get more mileage if you want to write a single Java
method which generates multiple, independently declared table functions.
=> .. yes, we already wrapped VTI invocations for standardization, e.g, for
"logical table" LT0:
create view LT0 as SELECT * from new com.ibm.db2j.GaianTable('LT0') T
=> By the way - given that this standardization approach works - why has the
support for VTIs been removed? Does it really matter that the nasty syntax
is available? VTIs already provide dynamic "table shapes" - which is a
really nice feature.
IBM removed user-written VTIs from Derby before open-sourcing the code
seven years ago. I believe that user-written VTIs were removed as part
of a general effort to cull features which fell outside the SQL Standard.
Note that a backdoor was left for VTIs written by IBM. User-written VTIs
are allowed if they live in a package whose name starts with
"com.ibm.db2j." Since gaiandb is an IBM project, I think you could feel
comfortable moving your VTIs into that namespace. This may be your
quickest migration path.
=> ... and yes, there are many constructors for the GaianTable class, with
up to 4 arguments - and yes, I already tried declaring the table function
static method using a varargs construct ( Object... args ) which didn't
work, so that would help with version 10.10.
2)
Rick> Are you trying to make the single Java method smart enough to figure
out
at run time which table shape to return? What about giving the Java
method an argument which encodes the table shape?
=> That is what I have had to do, yes - but it makes the syntax
repetitive/nasty, e.g:
"select * from table ( lt0('lt0') ) T" ; instead of preferred syntax:
"select * from table ( lt0() ) T".
=> So is there no means of getting the table function name from inside the
java method?
There might be a way for the Java method to dig up one of the Derby
contexts and find the SQL text. However, that would not be a supported api.
3)
Rick> ... you shouldn't need to implement all of the getXXX()
methods if you use the building-block table functions which appear in
Derby's public api:
org.apache.derby.vti.StringColumnVTI
org.apache.derby.vti.VTITemplate.
=> .. I see what you mean - but I was referring to the methods that we
actually USE: With IFastPath, I only had to implement ONE method. Whereas
with ResultSet (or VTITemplate), I would need to implement/override all the
methods we need, for all the types, as well as the next() method...
IFastPath was really handy in avoiding this and making the code simpler to
read/maintain.
I see. Note that if you extend StringColumnVTI, then all you have to
implement are next(), close(), and getRawColumn().
4)
Rick> Restrictions are passed through views into RestrictedVTIs, but
projections aren't yet. It's a tricky problem tracked by
https://issues.apache.org/jira/browse/DERBY-6036.
=> OK. Hopefully there will be a fix for this at some point - the impact on
memory + performance can be considerable.
+1
5)
(Capability to define a SYNONYM on a table function)
Rick> Afraid I'm not following this point. Do you need this as a workaround
for (4) or do you have some other use case in mind?
=> This relates to 1) and 4). If we could define a SYNONYM for a table
function (given a fixed set of arguments), then this could replace the
overhead of the VIEW - and yes it would solve 4), because we would no longer
be wrapping it with "SELECT *", e.g:
CEATE SYNONYM LT0 for TABLE( LT0() ) T
CEATE SYNONYM LT0_P for TABLE( LT0('with_provenance') ) T
...
What is the feasibility of this in future?
I can't find the CREATE SYNONYM syntax in the SQL Standard. However,
Derby already supports this syntax for tables and views. I don't think
the community would push back if we extended this syntax to cover other
kinds of <table primary>. I would remove the trailing correlation name:
CREATE SYNONYM LT0 for TABLE( LT0() )
CREATE SYNONYM LT0_P for TABLE( LT0('with_provenance') )
I have logged an enhancement request for this feature:
https://issues.apache.org/jira/browse/DERBY-6109
6) (Issue from my 2nd post - conversion of a Restriction tree to a
Qualifier[][])
Rick> The code for converting a predicate to CNF can be found in
org.apache.derby.impl.sql.compile.SelectNode.normExpressions()
=> Thank you - I'll take a look if/when I'm allowed to (!) - but I'll
probably just code it.
(Conclusion)
Rick> You are welcome to file additional enhancement requests for table
functions. We do want to make table functions even more useful.
In conclusion again - the main issues are the new one (0) and 1). Next is
issue 4), and the others are lower priority.
What is the process to file enhancement requests?
It's very simple. Just log an Apache JIRA as described here:
http://db.apache.org/derby/derby_comm.html
What time frame would we
be looking at?
Depends on the enhancement. This is open source, so there are no guarantees.
Do you expect VTIs to be removed altogether at some point?
Derby relies on a number of internal VTIs. Removing those dependencies
would be a significant hurdle for anyone who wanted to drop VTI support
altogether. No-one has expressed any interest in such a project. No-one
has expressed any interest in removing the com.ibm.db2j backdoor, either.
That said, I don't think you're going to see a lot of interest in
VTI-related bugs unless those bugs also affect the internal VTIs which
Derby uses. I would not expect a lot of support for opening the backdoor
further.
Hope this is helpful,
-Rick
--
View this message in context:
http://apache-database.10148.n7.nabble.com/Limitations-of-Table-Functions-vs-old-VTIs-tp127988p128078.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.
