db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "David Vyvyan (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DERBY-6115) Restricted Table Function initScan() does not pass complex OR expressions
Date Wed, 20 Mar 2013 16:57:15 GMT
David Vyvyan created DERBY-6115:
-----------------------------------

             Summary: 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: Bug
          Components: SQL
    Affects Versions: 10.8.3.0
            Reporter: David Vyvyan


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

Mime
View raw message