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