[jira] [Comment Edited] (OAK-7166) Union with different selector names

["Mohammad (Jira)" <ji...@apache.org>]

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

Mohammad edited comment on OAK-7166 at 1/5/21, 8:41 PM:
--------------------------------------------------------

I have same issue with join query and contains condition. The condition is on join query but when query converted to solr query, the condtion applied on joined type. When I change contains to equal the query convert and execute correctly.

After debugging I found this query has two execution plan. when The query engine select below plan it does not have any result but when use other plan it has result. This problem caused random result in search feature.

 

SQL2 query:

{{SELECT san_sinaBaseDossier.* FROM [san:lkeBaseDossier] AS san_lkeBaseDossier INNER JOIN [san:lkeBaseDocument] as san_lkeBaseDossier_san_documents ON san_lkeBaseDossier_san_documents.[jcr:uuid] = san_lkeBaseDossier.[san:documents] WHERE ((CONTAINS([san_lkeBaseDossier_san_documents].[san:docDesc], cast('*123*' AS string)))) AND ISDESCENDANTNODE(san_lkeBaseDossier, '/contents/dossiers') ORDER BY [san_lkeBaseDossier].[jcr:createdBy] DESC}}

{{Converted solr query:}}
 {{q.op=AND&fl=path_exact+score&df=catch_all&rows=10&sort=jcr\:createdBy_string_sort+desc&fq=(jcr\:primaryType:san\:lkeBaseDossier+)+&fq=path_des:\/contents\/dossiers&fq=\{!collapse+field%3Dpath_collapsed+min%3Dpath_depth+hint%3Dtop_fc+nullPolicy%3Dexpand}&q=*123*+san\:documents:*+}}


was (Author: mahdavi):
I have same issue with join query and contains condition. The condition is on join query but when query converted to solr query, the condtion applied on joined type. When I change contains to equal the query convert and execute correctly.

SQL2 query:

{{SELECT san_sinaBaseDossier.* FROM [san:lkeBaseDossier] AS san_lkeBaseDossier INNER JOIN [san:lkeBaseDocument] as san_lkeBaseDossier_san_documents ON san_lkeBaseDossier_san_documents.[jcr:uuid] = san_lkeBaseDossier.[san:documents] WHERE ((CONTAINS([san_lkeBaseDossier_san_documents].[san:docDesc], cast('*123*' AS string)))) AND ISDESCENDANTNODE(san_lkeBaseDossier, '/contents/dossiers') ORDER BY [san_lkeBaseDossier].[jcr:createdBy] DESC}}

{{Converted solr query:}}
{{q.op=AND&fl=path_exact+score&df=catch_all&rows=10&sort=jcr\:createdBy_string_sort+desc&fq=(jcr\:primaryType:san\:lkeBaseDossier+)+&fq=path_des:\/contents\/dossiers&fq=\{!collapse+field%3Dpath_collapsed+min%3Dpath_depth+hint%3Dtop_fc+nullPolicy%3Dexpand}&q=*123*+san\:documents:*+}}

> Union with different selector names
> -----------------------------------
>
>                 Key: OAK-7166
>                 URL: https://issues.apache.org/jira/browse/OAK-7166
>             Project: Jackrabbit Oak
>          Issue Type: Bug
>          Components: query
>            Reporter: Thomas Mueller
>            Assignee: Thomas Mueller
>            Priority: Major
>
> The following query returns the wrong nodes:
> {noformat}
> /jcr:root/libs/(* | */* | */*/* | */*/*/* | */*/*/*/*)/install
> select b.[jcr:path] as [jcr:path], b.[jcr:score] as [jcr:score], b.* from [nt:base] as a
>  inner join [nt:base] as b on ischildnode(b, a)
>  where ischildnode(a, '/libs') and name(b) = 'install' 
>  union select c.[jcr:path] as [jcr:path], c.[jcr:score] as [jcr:score], c.* from [nt:base] as a
>  inner join [nt:base] as b on ischildnode(b, a)
>  inner join [nt:base] as c on ischildnode(c, b)
>  where ischildnode(a, '/libs') and name(c) = 'install' 
>  union select d.[jcr:path] as [jcr:path], d.[jcr:score] as [jcr:score], d.* from [nt:base] as a
>  inner join [nt:base] as b on ischildnode(b, a)
>  inner join [nt:base] as c on ischildnode(c, b)
>  inner join [nt:base] as d on ischildnode(d, c)
>  where ischildnode(a, '/libs') and name(d) = 'install' 
> {noformat}
> If I change the selector name to "x" in each subquery, then it works. There is no XPath version of this workaround:
> {noformat}
> select x.[jcr:path] as [jcr:path], x.[jcr:score] as [jcr:score], x.* from [nt:base] as a
>  inner join [nt:base] as x on ischildnode(x, a)
>  where ischildnode(a, '/libs') and name(x) = 'install' 
>  union select x.[jcr:path] as [jcr:path], x.[jcr:score] as [jcr:score], x.* from [nt:base] as a
>  inner join [nt:base] as b on ischildnode(b, a)
>  inner join [nt:base] as x on ischildnode(x, b)
>  where ischildnode(a, '/libs') and name(x) = 'install' 
>  union select x.[jcr:path] as [jcr:path], x.[jcr:score] as [jcr:score], x.* from [nt:base] as a
>  inner join [nt:base] as b on ischildnode(b, a)
>  inner join [nt:base] as c on ischildnode(c, b)
>  inner join [nt:base] as x on ischildnode(x, c)
>  where ischildnode(a, '/libs') and name(x) = 'install' 
> {noformat}
> Need to check if this is a Oak bug, or a bug in the query tool I use.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)