[
https://issues.apache.org/jira/browse/OAK-2539?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15013582#comment-15013582
]
Thomas Mueller edited comment on OAK-2539 at 11/19/15 2:06 PM:
---------------------------------------------------------------
With trunk, the debug log for this query is (see below) indicates problem is
solved:
{noformat}
QueryEngineImpl Parsing JCR-SQL2 statement: SELECT * FROM [nt:base] AS s
WHERE ISDESCENDANTNODE([/content/usergenerated/])
AND (s.[stringa] = 'a' OR CONTAINS(s.[stringb], 'b'))
QueryEngineImpl Optimised query available. select [s].[jcr:primaryType] as
[s.jcr:primaryType]
from [nt:base] as [s] where ([s].[stringa] = 'a')
and (isdescendantnode([s], [/content/usergenerated/]))
union select [s].[jcr:primaryType] as [s.jcr:primaryType]
from [nt:base] as [s] where (contains([s].[stringb], 'b'))
and (isdescendantnode([s], [/content/usergenerated/]))
QueryEngineImpl Preparing: select [s].[jcr:primaryType] as [s.jcr:primaryType]
from [nt:base] as [s] where (isdescendantnode([s],
[/content/usergenerated/]))
and ((contains([s].[stringb], 'b')) or ([s].[stringa] = 'a'))
QueryImpl cost using filter Filter(query=SELECT * FROM [nt:base] AS s
WHERE ISDESCENDANTNODE([/content/usergenerated/]) AND (s.[stringa] = 'a'
OR CONTAINS(s.[stringb], 'b')), path=/content/usergenerated//*)
QueryImpl cost for reference is Infinity
QueryImpl cost for property is Infinity
QueryImpl cost for nodeType is Infinity
QueryImpl cost for lucene-property is Infinity
QueryImpl cost for aggregate lucene is Infinity
QueryImpl cost for ordered is Infinity
QueryImpl cost for traverse is 2100.0
QueryEngineImpl actualCost: 2100.0 - costOverhead: 1.7976931348623157E308 -
overallCost: 1.7976931348623157E308
QueryEngineImpl Preparing: select [s].[jcr:primaryType] as [s.jcr:primaryType]
from [nt:base] as [s] where ([s].[stringa] = 'a')
and (isdescendantnode([s], [/content/usergenerated/]))
union select [s].[jcr:primaryType] as [s.jcr:primaryType]
from [nt:base] as [s] where (contains([s].[stringb], 'b'))
and (isdescendantnode([s], [/content/usergenerated/]))
QueryImpl cost using filter Filter(query=SELECT * FROM [nt:base] AS s
WHERE ([s].[stringa] = 'a') and (isdescendantnode([s],
[/content/usergenerated/])),
path=/content/usergenerated//*, property=[stringa=[a]])
QueryImpl cost for reference is Infinity
QueryImpl cost for property is Infinity
QueryImpl cost for nodeType is Infinity
QueryImpl cost for lucene-property is Infinity
QueryImpl cost for aggregate lucene is Infinity
QueryImpl cost for ordered is Infinity
QueryImpl cost for traverse is 2100.0
QueryImpl cost using filter Filter(query=SELECT * FROM [nt:base] AS s
WHERE (contains([s].[stringb], 'b')) and (isdescendantnode([s],
[/content/usergenerated/]))
fullText=stringb:"b", path=/content/usergenerated//*, property=[stringb=[is
not null]])
QueryImpl cost for reference is Infinity
QueryImpl cost for property is Infinity
QueryImpl cost for nodeType is Infinity
QueryImpl cost for lucene-property is Infinity
QueryImpl cost for aggregate lucene is 198919.0
QueryImpl cost for ordered is Infinity
QueryImpl cost for traverse is Infinity
QueryEngineImpl actualCost: 201019.0 - costOverhead: 0.0 - overallCost: 201019.0
QueryEngineImpl Cheapest cost: 201019.0 - query: select [s].[jcr:primaryType]
as [s.jcr:primaryType]
from [nt:base] as [s] where ([s].[stringa] = 'a')
and (isdescendantnode([s], [/content/usergenerated/]))
union select [s].[jcr:primaryType] as [s.jcr:primaryType]
from [nt:base] as [s] where (contains([s].[stringb], 'b'))
and (isdescendantnode([s], [/content/usergenerated/]))
UnionQueryImpl query union plan [nt:base] as [s] /* traverse
"/content/usergenerated//*"
where ([s].[stringa] = 'a') and (isdescendantnode([s],
[/content/usergenerated/])) */
union [nt:base] as [s] /* aggregate stringb:b ft:(stringb:"b")
where (contains([s].[stringb], 'b'))
and (isdescendantnode([s], [/content/usergenerated/])) */
QueryImpl query execute SELECT * FROM [nt:base] AS s WHERE ([s].[stringa] =
'a')
and (isdescendantnode([s], [/content/usergenerated/]))
QueryImpl query plan [nt:base] as [s] /* traverse "/content/usergenerated//*"
where ([s].[stringa] = 'a') and (isdescendantnode([s],
[/content/usergenerated/])) */
QueryImpl query execute SELECT * FROM [nt:base] AS s WHERE
(contains([s].[stringb], 'b'))
and (isdescendantnode([s], [/content/usergenerated/]))
QueryImpl query plan [nt:base] as [s] /* aggregate stringb:b ft:(stringb:"b")
where (contains([s].[stringb], 'b'))
and (isdescendantnode([s], [/content/usergenerated/])) */
{noformat}
was (Author: tmueller):
With trunk, the debug log for this query is (see below) indicates the cost for
traversal is low in this case, in the case of the original query (with "or").
The query engine should probably detect that there is a fulltext condition that
is not using an index, and rule out using the non-union plan.
{noformat}
persistentCache="crx-quickstart/repository/cache,size\=1024,binary\=0,broadcast\=tcp:key
123"
http://localhost:8080/crx/explorer/testing/createNodes.jsp
QueryEngineImpl Parsing JCR-SQL2 statement: SELECT * FROM [nt:base] AS s
WHERE ISDESCENDANTNODE([/content/usergenerated/])
AND (s.[stringa] = 'a' OR CONTAINS(s.[stringb], 'b'))
QueryEngineImpl Optimised query available. select [s].[jcr:primaryType] as
[s.jcr:primaryType]
from [nt:base] as [s] where ([s].[stringa] = 'a')
and (isdescendantnode([s], [/content/usergenerated/]))
union select [s].[jcr:primaryType] as [s.jcr:primaryType]
from [nt:base] as [s] where (contains([s].[stringb], 'b'))
and (isdescendantnode([s], [/content/usergenerated/]))
QueryEngineImpl Preparing: select [s].[jcr:primaryType] as [s.jcr:primaryType]
from [nt:base] as [s] where (isdescendantnode([s],
[/content/usergenerated/]))
and ((contains([s].[stringb], 'b')) or ([s].[stringa] = 'a'))
QueryImpl cost using filter Filter(query=SELECT * FROM [nt:base] AS s
WHERE ISDESCENDANTNODE([/content/usergenerated/]) AND (s.[stringa] = 'a'
OR CONTAINS(s.[stringb], 'b')), path=/content/usergenerated//*)
QueryImpl cost for reference is Infinity
QueryImpl cost for property is Infinity
QueryImpl cost for nodeType is Infinity
QueryImpl cost for lucene-property is Infinity
QueryImpl cost for aggregate lucene is Infinity
QueryImpl cost for ordered is Infinity
QueryImpl cost for traverse is 2100.0
QueryEngineImpl actualCost: 2100.0 - costOverhead: 1.7976931348623157E308 -
overallCost: 1.7976931348623157E308
QueryEngineImpl Preparing: select [s].[jcr:primaryType] as [s.jcr:primaryType]
from [nt:base] as [s] where ([s].[stringa] = 'a')
and (isdescendantnode([s], [/content/usergenerated/]))
union select [s].[jcr:primaryType] as [s.jcr:primaryType]
from [nt:base] as [s] where (contains([s].[stringb], 'b'))
and (isdescendantnode([s], [/content/usergenerated/]))
QueryImpl cost using filter Filter(query=SELECT * FROM [nt:base] AS s
WHERE ([s].[stringa] = 'a') and (isdescendantnode([s],
[/content/usergenerated/])),
path=/content/usergenerated//*, property=[stringa=[a]])
QueryImpl cost for reference is Infinity
QueryImpl cost for property is Infinity
QueryImpl cost for nodeType is Infinity
QueryImpl cost for lucene-property is Infinity
QueryImpl cost for aggregate lucene is Infinity
QueryImpl cost for ordered is Infinity
QueryImpl cost for traverse is 2100.0
QueryImpl cost using filter Filter(query=SELECT * FROM [nt:base] AS s
WHERE (contains([s].[stringb], 'b')) and (isdescendantnode([s],
[/content/usergenerated/]))
fullText=stringb:"b", path=/content/usergenerated//*, property=[stringb=[is
not null]])
QueryImpl cost for reference is Infinity
QueryImpl cost for property is Infinity
QueryImpl cost for nodeType is Infinity
QueryImpl cost for lucene-property is Infinity
QueryImpl cost for aggregate lucene is 198919.0
QueryImpl cost for ordered is Infinity
QueryImpl cost for traverse is Infinity
QueryEngineImpl actualCost: 201019.0 - costOverhead: 0.0 - overallCost: 201019.0
QueryEngineImpl Cheapest cost: 201019.0 - query: select [s].[jcr:primaryType]
as [s.jcr:primaryType]
from [nt:base] as [s] where ([s].[stringa] = 'a')
and (isdescendantnode([s], [/content/usergenerated/]))
union select [s].[jcr:primaryType] as [s.jcr:primaryType]
from [nt:base] as [s] where (contains([s].[stringb], 'b'))
and (isdescendantnode([s], [/content/usergenerated/]))
UnionQueryImpl query union plan [nt:base] as [s] /* traverse
"/content/usergenerated//*"
where ([s].[stringa] = 'a') and (isdescendantnode([s],
[/content/usergenerated/])) */
union [nt:base] as [s] /* aggregate stringb:b ft:(stringb:"b")
where (contains([s].[stringb], 'b'))
and (isdescendantnode([s], [/content/usergenerated/])) */
QueryImpl query execute SELECT * FROM [nt:base] AS s WHERE ([s].[stringa] =
'a')
and (isdescendantnode([s], [/content/usergenerated/]))
QueryImpl query plan [nt:base] as [s] /* traverse "/content/usergenerated//*"
where ([s].[stringa] = 'a') and (isdescendantnode([s],
[/content/usergenerated/])) */
QueryImpl query execute SELECT * FROM [nt:base] AS s WHERE
(contains([s].[stringb], 'b'))
and (isdescendantnode([s], [/content/usergenerated/]))
QueryImpl query plan [nt:base] as [s] /* aggregate stringb:b ft:(stringb:"b")
where (contains([s].[stringb], 'b'))
and (isdescendantnode([s], [/content/usergenerated/])) */
{noformat}
> SQL2 query not working with filter (s.[stringa] = 'a' OR
> CONTAINS(s.[stringb], 'b'))
> ------------------------------------------------------------------------------------
>
> Key: OAK-2539
> URL: https://issues.apache.org/jira/browse/OAK-2539
> Project: Jackrabbit Oak
> Issue Type: Bug
> Components: core, query
> Reporter: Calvin Wong
> Assignee: Davide Giannella
> Fix For: 1.4
>
>
> Create node /content/usergenerated/qtest with jcr:primaryType nt:unstrucuted.
> Add 2 String properties: stringa = "a", stringb = "b".
> Use query tool in CRX/DE to do SQL2 search:
> This search will find qtest:
> SELECT * FROM [nt:base] AS s WHERE
> ISDESCENDANTNODE([/content/usergenerated/]) AND (s.[stringa] = 'a' OR
> CONTAINS(s.[stringb], 'b'))
> This search will find qtest:
> SELECT * FROM [nt:base] AS s WHERE
> ISDESCENDANTNODE([/content/usergenerated/]) AND (CONTAINS(s.[stringb], 'b'))
> This search will not find qtest:
> SELECT * FROM [nt:base] AS s WHERE
> ISDESCENDANTNODE([/content/usergenerated/]) AND (s.[stringa] = 'x' OR
> CONTAINS(s.[stringb], 'b'))
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
