[jira] [Commented] (DERBY-6115) Certain OR expressions are not passed to Table indexes or Table Function initScan()

["David Vyvyan (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-6115?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13961402#comment-13961402 ] 

David Vyvyan commented on DERBY-6115:
-------------------------------------

I think the optimisation of "multi-probe" queries is a separate issue.

The primary issue raised here is that Derby is failing to translate some WHERE clauses to conjunctive normal form.

WHERE NAME > 'zzz' or (NAME > 'Wz' and NAME < 'XA')
should become:
WHERE (NAME > 'zzz' or NAME > 'Wz') and (NAME > 'zzz' or NAME < 'XA')
and ideally simplified to:
WHERE NAME > 'Wz' and (NAME > 'zzz' or NAME < 'XA')

Predicates are only being pushed down to initScan() when the WHERE clause is already expressed in CNF, but we need them when they aren't as well.
(...and ideally "IN" lists would also be passed through as ORed equality expressions...)

> 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.2#6252)