marklogicmarklogic-10marklogic-optic-api

Performing an op:join-inner with op:from-lexicons not working as expected with Optic API


I have the following optic query using the op:from-lexicons access plan:

op:from-lexicons((
        map:map()
            => map:with("lexiconUuid", cts:element-reference(xs:QName("lexiconUuid")))
            => map:with("elementInDocumentToFilter", cts:element-reference(xs:QName("elementInDocumentToFilter")))
            => map:with("documentUuid", cts:element-reference(xs:QName("documentUuid")))
    ),"lexiconView")
    => op:where(op:eq(op:view-col("lexiconView","elementInDocumentToFilter"), "Filter Value"))
    => op:group-by(op:view-col("lexiconView","lexiconUuid"), 
        op:count("countOfDocsMeetingFilter", "documentUuid", map:entry('values', 'distinct'))
    )
    => op:select((
        op:view-col("lexiconView","lexiconUuid"),
        op:as("hasCountOver0", op:gt(op:col("countOfDocsMeetingFilter"), 0))
    ))

When I add op:result() to this query I get an output like:

lexiconView.lexiconUuid hasCountOver0
lexicon-uuid-1 true
lexicon-uuid-2 false

This query is using the existing indexes to return the lexiconUuid and whether or not it appears in any documents based on the filter. When I try to use the query above as the $rightPlan for an op:join-inner I get no results (as in, no join took place). Here is my query using the lexicon query above:

op:from-view("schema", "table1")
    (: table2 contains the lexiconUuid element I want to join the lexicon query with :)
    => op:join-inner(op:from-view("schema", "table2"),
        op:on(
            op:view-col("table1", "someUuid"),
            op:view-col("table2", "someUuid")
        )
    )
    => op:join-inner(
        (: op:from-lexicons() access plan here :),
        op:on(
            op:view-col("table2", "lexiconUuid"),
            op:view-col("lexiconView", "lexiconUuid")
        )
    )

I know for a fact that the lexiconUuid is present after joining table2 and exists in the op:from-lexicons query (i.e. lexicon-uuid-1 exists in table2 so it should join on the "lexiconView" without a problem). I replicated this "lexiconView" as a TDE and the join performs as expected but requires an aggregation on the existing data so I have to op:group-by() all of the columns in table1 and table2. I was hoping to be able to use the lexicon to avoid having another TDE and performing a group-by/aggregation on more columns. Is there something I am missing about using the op:from-lexicons access plan? Can it be used in joins? Has anyone ran into this before?


Solution

  • I heard back from MarkLogic engineers on this and it turns out to be a collation issue. The TDE column is a regular string whereas the lexicon index has a collation in our range index configuration of http://marklogic.com/collation/. I was able to fix the issue by adding the collation to my TDE column configuration.

    <column>
      <name>uuid</name>
      <scalar-type>string</scalar-type>
      <val>uuid</val>
      <collation>http://marklogic.com/collation/</collation>
    </column>