[
https://issues.apache.org/jira/browse/DERBY-6115?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dag H. Wanvik updated DERBY-6115:
---------------------------------
Urgency: Normal (was: Urgent)
Bug behavior facts: Performance,Seen in production
Labels: derby_triage10_11 (was: )
Issue Type: Improvement (was: Bug)
Triaged for 10.11.
> Restricted Table Function initScan() does not pass complex OR expressions
> -------------------------------------------------------------------------
>
> Key: DERBY-6115
> URL: https://issues.apache.org/jira/browse/DERBY-6115
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.8.3.0
> 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 is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
