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

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 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
• Component/s: SQL
• Labels: derby_triage10_11
• Urgency: Normal
• Bug behavior facts: Performance, Seen in production
• Assignee: Unassigned
• Reporter: David Vyvyan
• Created: 20/Mar/13 16:55
• Updated: 06/Apr/14 13:18

Description

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'

Issue Links

• is related to: DERBY-6301 SQL layer should push down IN list predicates to store when doing a scan