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?
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>