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] [Commented] (DERBY-6115) Restricted Table Function initScan() does not pass complex OR expressions
Date Tue, 22 Oct 2013 14:31:47 GMT

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

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

I have now proved that this issue also affects ordinary table scans - which hopefully will
raise its urgency:

1. Create an ordinary table with 1M rows, having a non-indexed column 'NAME' which is not
unique per row.
2. Verify that a count(*) query against this table "WHERE NAME = 'X'" takes about 1 second
to return, showing that a table scan was run.
3. Create an index on column 'NAME'
4. Verify that the same count(*) query returns about 100 times faster (~20ms)
5. Now use a predicates expression similar to a failure case described in this defect, e.g:
WHERE NAME > 'zzz' or ( NAME > 'Wz' and NAME < 'XA' )

This final query should return quickly (~20ms) but actually appears to require 2 table scans
as it returns in almost 2 seconds. The predicates are not being applied against the index.

This defect would affect all queries having predicate expressions which need to be re-factored
to "Conjunctive Normal Form". I suspect this re-factoring is not working...

I hope that this will raise the issue's urgency and also be fixed in previous versions of
Derby. We have a product using version 10.8.

> 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.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)

Mime
View raw message