[
https://issues.apache.org/jira/browse/DERBY-6115?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13847248#comment-13847248
]
Richard Huddleston commented on DERBY-6115:
-------------------------------------------
we are using derby VTI to expose data in a remote data store. we know which
columns / fields are indexed in that remote data store, if we implement a
ResultSetMetaData object in our ResultSet implementation, would Derby look at
isSearchable to determine if multi-probe support would work ?
we have derby views wrapping derby functions
so derby view remote_data -> derby function remote_data_func
and a simple in clause does not get pushed down to our function, and we have to
throw a WE_CANT_DO_THAT exception because we cannot do full scans
when we do
select s.* from remote_data where someField in ( 'A' , 'B')
derby initScan does not pass a Restriction on someField
first, it's sad derby does not support passing in down to initScan
second, it's sad derby does not just transform in to a OR clause
third, it's sad derby does not appear to do multi-probe if we indicate on a
metaData that isSearchable is true
perhaps isSearchable on ResultSetMetaData is the wrong thing to do
perhaps it is too late for derby to decide how to execute the query. maybe the
function needs to declare which fields are indexed / correctly interpreted as a
restriction by the table function. for example, our implementation of result
set turns the entire restriction into sql and executes against a remote data
store that has a SQL api. it would be nice to derby, our VTI implementation is
awesome, it will do exactly what you want us to do, we aren't just table
scanning and giving back everything.
> Certain OR expressions are not passed to Table indexes or Table Function
> initScan()
> -----------------------------------------------------------------------------------
>
> Key: DERBY-6115
> URL: https://issues.apache.org/jira/browse/DERBY-6115
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1,
> 10.7.1.1, 10.8.1.2, 10.8.2.2, 10.8.3.0, 10.9.1.0, 10.10.1.1
> Reporter: David Vyvyan
> Labels: derby_triage10_11
>
> Issue originally posted here:
> ====================
> http://apache-database.10148.n7.nabble.com/RestrictedVTI-initScan-does-not-pass-certain-Table-Functions-predicate-expressions-td128229.html
> Note by Rick Hillegas:
> ================
> Hi David,
> I think it's worth filing a JIRA for this issue. If the defect is shared
> by VTIs and table functions then there's a possibility that ordinary
> table scans suffer from it too. That would raise the problem's urgency.
> Thanks,
> -Rick
> Summary Description:
> ================
> Basically some WHERE clause expressions do not get passed through via
> RestrictedVTI.initScan().
> This can have a severe impact on memory/performance.
> (I suspect the issue may be related to logic which tries to move AND nodes to
> the top of the tree...?)
> Examples (I have a few more here than in the post above):
> These get passed ok in the Restriction object:
> - C1>6
> - C1>1 AND C2<'d'
> - C1>6 OR C2<'d'
> - C1>1 AND (C1<6 OR C2<'d')
> This one gets passed partially by initScan():
> C1>1 AND (C1<6 OR (C2>'e' AND C2<'d')) ===> initScan() passes only:
> "C1" > 1
> These do not get passed at all (initScan() Restriction argument object is
> null):
> - C1>6 OR (C1>1 AND C2<'d')
> - C1>6 OR ((C1>1 AND C2<'d') AND C2>'b')
> - C1 in ( 1, 4 )
> - C1 in ( 1, 4 ) OR C2>'f' -- Can Derby resolve in() clauses to a list of '='
> conditions ? This would be useful!
> My table function is defined as follows:
> CREATE FUNCTION TF_TEST1() RETURNS TABLE(C1 INT, C2 VARCHAR(32672)) PARAMETER
> STYLE DERBY_JDBC_RESULT_SET LANGUAGE JAVA NOT DETERMINISTIC READS SQL DATA
> EXTERNAL NAME 'core.TestTableFunctions.TF_TEST1'
--
This message was sent by Atlassian JIRA
(v6.1.4#6159)
