MarkLogic optic query using two indexes returns no results

I want to use the MarkLogic optic API to join two range indexes but somehow they don't join. Is the query I wrote wrong or can't I compare the indexes used?

I have two indexes defined:

an element-attribute range index x/@refid
a range field index 'id'

Both are of type string and have the same collation defined. Both indexes have data that I can retrieve with cts:values() function. Both are huge indexes and I want to join them using optics so I have constructed the following query :

import module namespace op="http://marklogic.com/optic"
at "/MarkLogic/optic.xqy";

let $subfrag := op:fragment-id-col("subfrag") 
let $notfrag := op:fragment-id-col("notfrag") 

let $query :=
cts:and-query((
  cts:collection-query("latest")
))

let $subids := op:from-lexicons(
   map:entry("subid", cts:field-reference("id")), (), $subfrag) => op:where($query)

let $notids := op:from-lexicons(
   map:entry("notid", cts:element-attribute-reference(xs:QName("x"), xs:QName("refid"))),
   (),
       $notfrag)
    
return $subids
   => op:join-cross-product($notids)
   => op:where(op:eq($notfrag, $subfrag))
   => op:result()

This query uses the join-cross-product and when I remove the op:where clause I get all values left and right. I verified and some are equal so the clause should filter only those rows i'm actually interested in. But somehow it doesn't work and I get an empty result. Also, if I replace one of the values in the op:eq with a string value it doesn't return a result.

When I use the same variable in the op:eq operator (like op:eq($notfrag, $notfrag)) I get results back so the statement as is works. Just not the comparison between the two indexes.

I have also used variants with join-inner and left-outer-join but those are also returning no results.

Am I comparing two incomparable indexes or am I missing some statement (as documentation/example is a bit thin).

(of course I can solve by not using optics but in this case it would be a perfect fit)

[update]

I got it working by eventually by changing the final statement:

return $subids
=> op:join-cross-product($notids)
=> op:where(op:eq(op:col('subid'), op:col('notid')))
=> op:result()

So somehow you cannot use the fragment definitions in the condition. After this I replaced the join-cross-product with a join-inner construction which should be a bit more efficient.

And to be complete, I initially used the example from the MarkLogic documentation found here (https://docs.marklogic.com/guide/app-dev/OpticAPI#id_87356), specifically the last example where they use a fragment column definition to be used as param in the join-inner statement that didn't work in my case.

Solution

Cross products are typically useful only for small rows sets.

Putting both reference in the same from-lexicons() accessor does an implicit join, meaning that the engine forms rows by constructing a local cross-product of the values indexed for each document.

Such a query could be expressed by:

op:from-lexicons(
   map:entry("subid", cts:field-reference("id"))
   =>map:with("notid", cts:element-attribute-reference(xs:QName("x"),
     xs:QName("refid")))
=>op:where(cts:collection-query("latest"))
=>op:result()

Making the joins explicitly could be done with:

let $subids := op:from-lexicons(
   map:entry("subid", cts:field-reference("id")), (), $subfrag)
   => op:where($query)

let $notids := op:from-lexicons(
   map:entry("notid", cts:element-attribute-reference(xs:QName("x"),
       xs:QName("refid"))),
   (),
   $notfrag)

return $subids
   => op:join-inner($notids, op:on($notfrag, $subfrag))
   => op:result()

Hoping that helps,