[
https://issues.apache.org/jira/browse/DERBY-6115?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13801875#comment-13801875
]
David Vyvyan commented on DERBY-6115:
-------------------------------------
I have also verified the behaviour with "IN" clauses.
The table index does appear to be used - meaning the "IN" clause is probably
correctly resolved to CNF and then applied to the index.
Unfortunately this is not the case with Table Functions... i.e. The predicates
are not being resolved to a set of ORed predicates and passed in the
Restriction object.
It would be nice if this could also be fixed as part of this defect.
> 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#6144)
