[
https://issues.apache.org/jira/browse/OAK-7166?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17259196#comment-17259196
]
Mohammad commented on OAK-7166:
-------------------------------
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)
