[jira] [Comment Edited] (OAK-7370) order by jcr:score desc doesn't work across union query created by optimizing OR clauses

["Thomas Mueller (JIRA)" <ji...@apache.org>]

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

Thomas Mueller edited comment on OAK-7370 at 4/3/18 8:59 AM:
-------------------------------------------------------------

A workaround might be, instead of using "isdescendantnode x or isdescendantnode y", use "jcr:path like 'x' or jcr:path like 'y'". Example:

{noformat}
SELECT * FROM [nt:base]
WHERE contains(*, 'text') 
AND (ISDESCENDANTNODE('/content/catalog/x8000') 
  OR ISDESCENDANTNODE('/content/catalog/y-900') ) 
order by [jcr:score] desc
{noformat}

Workaround:

{noformat}
SELECT * FROM [nt:base]
WHERE contains(*, 'text') 
AND ISDESCENDANTNODE('/content/catalog')
AND ([jcr:path] like '/content/catalog/x8000/%' 
  OR [jcr:path] like '/content/catalog/y-900/%') ) 
order by [jcr:score] desc
{noformat}

This will consider using union, but that one has a higher cost, so the non-union version is used. (Would need to be tested). So "jcr:path like x" is a condition that doesn't use an index.

In theory this could be used: "isdescendantnode common-y-x and (isdescendantnode x or isdescendantnode y)", but in reality here the cost of the union version would be lower, so that one would be used.



was (Author: tmueller):
A workaround might be, instead of using "isdescendantnode x or isdescendantnode y", use "jcr:path like 'x' or jcr:path like 'y'". Example:

{noformat}
SELECT * FROM [nt:base]
WHERE contains(*, 'text') 
AND (ISDESCENDANTNODE('/content/catalog/x8000') 
  OR ISDESCENDANTNODE('/content/catalog/y-900') ) 
order by [jcr:score] desc
{noformat}

Workaround:

{noformat}
SELECT * FROM [nt:base]
WHERE contains(*, 'text') 
AND ISDESCENDANTNODE('/content/catalog')
AND ([jcr:path] like '/content/catalog/x8000/%' 
  OR [jcr:path] like '/content/catalog/y-900/%') ) 
order by [jcr:score] desc
{noformat}

This will consider using union, but the one has a higher cost, so the non-union version is used.

In theory this couls be used: "isdescendantnode common-y-x and (isdescendantnode x or isdescendantnode y)", but in reality here the cost of the union version would be lower, so that one would be used.


> order by jcr:score desc doesn't work across union query created by optimizing OR clauses
> ----------------------------------------------------------------------------------------
>
>                 Key: OAK-7370
>                 URL: https://issues.apache.org/jira/browse/OAK-7370
>             Project: Jackrabbit Oak
>          Issue Type: Bug
>          Components: core
>            Reporter: Vikas Saurabh
>            Assignee: Vikas Saurabh
>            Priority: Major
>             Fix For: 1.10
>
>
> Merging of sub-queries created due to optimizing OR clauses doesn't work for sorting on {{jcr:score}}



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)