[
https://issues.apache.org/jira/browse/DERBY-6117?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13851261#comment-13851261
]
Richard Huddleston commented on DERBY-6117:
-------------------------------------------
i agree with all above, especially the name of the function called.
adding information from
https://issues.apache.org/jira/browse/DERBY-6115
1) derby should support passing IN to initScan
2) derby should transform IN to an OR clause as a work around to not passing
down IN scan
3) function should have access to information about the function's return type,
i.e. the if the result type is a table definition, function should have access
to that definition. this is necessary when a function is declared with
multiple names and return types. for example, with foreign table loads, we
want to pre-transform data from remote result set in 1 thread, then hand to
derby thread. we need to know what the main derby thread reading resultset
wants in sqltype for each column.
4) derby should introspect resultset to see which column names are searchable,
and thus, fast for querying, perhaps isSearchable on ResultSetMetaData is the
right / wrong thing to do. maybe can do this with VTIConsting ?
5) derby should do multi-probe on vti function if vti function indicates on a
metaData that isSearchable is true, or that it implements perfectly the
initscan restriction.
6) vti function should be able to tell derby that it correctly implements the
vti restriction, either for a given one, or for any, and derby should not
request the column in initscan columnNames, and derby should not check again
the restriction. ( for example, assume foreign table with username and picture
as BLOB) , a query for select username where picture is not null, currently,
derby will pass select username and picture as columnnames, and function will
have to pull all the username and blob data and hand to derby, just to allow
derby to check again is not null, even though function already did this . This
is a HUGE performance issue we are experiencing.
i'm sure i'll have more issues to add :)
> Extend the Table Functions java interface to pass more query context
> information from Derby
> -------------------------------------------------------------------------------------------
>
> Key: DERBY-6117
> URL: https://issues.apache.org/jira/browse/DERBY-6117
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.8.3.0
> Reporter: David Vyvyan
> Labels: derby_triage10_11
>
> General requirement is to extend the Table Functions java interface (through
> RestrictedVTI or another interface) and pass more context information from
> Derby to Table Functions - esp in query execution phase.
> Greater urgency is required for the first 2 items below, especially the
> ability to access the original SQL which was available with VTIs. This is
> critical to the GaianDB project - we extract HINTs from the query (where we
> pass meta data like user credentials), and also extract full original complex
> predicate expressions (involving functions etc - which cannot be expressed
> with a Restriction) - to push on in our query prorogation...
> In order of importance + simplicity:
> --------------------------------------------------
> 1 - Original SQL (this used to be available with VTIs through VTIEnvironment
> for both compilation and execution phases)
> 2 - Name of function that was called
> 3 - User Info (ID, etc) - (this can currently be passed in the SQL hint)
> 4 - Richer predicate expressions (incl functions, etc)
> 5 - Context within Join query (e.g. inner or outer table, join type)
> 6 - Other Query Plan information
> 7 - Anything else...?
--
This message was sent by Atlassian JIRA
(v6.1.4#6159)
