CMIS Exact Term Search in ACS 23.1.0 does not work

[brunosantos]

My CMIS search with '=' does not work.

Query: SELECT * FROM colClient:document where colClient:caseNumber = 'case number postman'

Model:

<property name="colClient:caseNumber">
<type>d:text</type>
<mandatory>false</mandatory>
<index enabled="true">
<atomic>true</atomic>
<stored>false</stored>
<tokenised>false</tokenised>
</index>
</property>

This property it was added in the file 'exactTermSearch.properties':

alfresco.cross.locale.datatype.0={http://www.alfresco.org/model/dictionary/1.0}text

alfresco.cross.locale.property.9={http://domain/collectif/client/model/1.0}caseNumber

The mapping created in elasticsearch:

"colClient%3AcaseNumber": {
                    "type": "keyword",
                    "meta": {
                        "DecodedQualifiedName": "colClient:caseNumber"
                    }
                },
                "colClient%3AcaseNumber_untokenized": {
                    "type": "alias",
                    "path": "colClient%3AcaseNumber"
                }

API:

org.alfresco.repo.search.impl.elasticsearch.ElasticsearchSearchService

Method: query(SearchParameters searchParameters)

Variable searchParameters populated:

SearchParameters searchParameters = new SearchParameters();
searchParameters.addStore(getStoreRef());
searchParameters.setMaxItems(nbrElementParPage);
searchParameters.setSkipCount(pageForSearch * nbrElementParPage);
searchParameters.setQuery(query);
searchParameters.setLanguage(org.alfresco.service.cmr.search.SearchService.LANGUAGE_CMIS_STRICT);

searchParameters.setDefaultFTSFieldConnective(SearchParameters.Operator.AND);
searchParameters.setDefaultFTSOperator(SearchParameters.Operator.AND);
searchParameters.setDefaultOperator(SearchParameters.Operator.AND);

JSON generated:

{
    "from": 0,
    "size": 500,
    "query": {
        "bool": {
            "must": [
                {
                    "bool": {
                        "must": [
                            {
                                "query_string": {
                                    "query": "TYPE:colClient\\:document",
                                    "fields": [],
                                    "type": "best_fields",
                                    "default_operator": "or",
                                    "max_determinized_states": 10000,
                                    "enable_position_increments": true,
                                    "fuzziness": "AUTO",
                                    "fuzzy_prefix_length": 0,
                                    "fuzzy_max_expansions": 50,
                                    "phrase_slop": 0,
                                    "escape": false,
                                    "auto_generate_synonyms_phrase_query": true,
                                    "fuzzy_transpositions": true,
                                    "boost": 1.0
                                }
                            },
                            {
                                "query_string": {
                                    "query": "+colClient%3AcaseNumber:case +colClient%3AcaseNumber:number +colClient%3AcaseNumber:postman",
                                    "fields": [],
                                    "type": "best_fields",
                                    "default_operator": "or",
                                    "max_determinized_states": 10000,
                                    "enable_position_increments": true,
                                    "fuzziness": "AUTO",
                                    "fuzzy_prefix_length": 0,
                                    "fuzzy_max_expansions": 50,
                                    "phrase_slop": 0,
                                    "escape": false,
                                    "auto_generate_synonyms_phrase_query": true,
                                    "fuzzy_transpositions": true,
                                    "boost": 1.0
                                }
                            }
                        ],
                        "adjust_pure_negative": true,
                        "boost": 1.0
                    }
                }
            ],
            "filter": [
                {
                    "match_all": {
                        "boost": 1.0
                    }
                }
            ],
            "adjust_pure_negative": true,
            "boost": 1.0
        }
    },
    "track_scores": true,
    "track_total_hits": 10000
}

With that, I got any documents returned, but exist at least one document with the property 'caseNumber' with a value = 'case number postman'

The only way that works is when I change

<tokenised>false</tokenised>

to

<tokenised>both</tokenised>

But, in this case, if I search for colClient:caseNumber = 'case' or colClient:caseNumber = 'number', I receive at least one document as a result, but this is not correct, because I try to search documents with exactly this value and not like or contains.

Any one could help me?

[angelborroy]

What is the CMIS query used?

This works as expected for me:

select * from colClient:test where colClient:caseNumber='case number postman'

[brunosantos]

Hi Angel Borroy, I appreciate your answer.

Yes this query cmis works, but it returns documents that should not be returned:

For example: If I have 2 documents where the attribut 'caseNumber' has:

case number postman

and another

case number postman test

The query return these 2 documents.

If I search just the word 'case', these 2 documents are returned also.

For me, the 'exact term search' does not work correctly.

Could you give help me with some ideas, please?

[angelborroy]

That is not true in my local deployment:

Double-check you're applying properly the exact term search configuration:

https://docs.alfresco.com/search-enterprise/latest/config/#exact-term-search

This is my local Docker Compose, exactTermSearch.properties includes exactly what is described in Alfresco Docs.

services:
  alfresco:
    image: quay.io/alfresco/alfresco-content-repository:${ALFRESCO_TAG}
    environment:
      JAVA_TOOL_OPTIONS: "
        -Dencryption.keystore.type=JCEKS
        -Dencryption.cipherAlgorithm=DESede/CBC/PKCS5Padding
        -Dencryption.keyAlgorithm=DESede
        -Dencryption.keystore.location=/usr/local/tomcat/shared/classes/alfresco/extension/keystore/keystore
        -Dmetadata-keystore.password=mp6yc0UD9e
        -Dmetadata-keystore.aliases=metadata
        -Dmetadata-keystore.metadata.password=oKIWzVdEdA
        -Dmetadata-keystore.metadata.algorithm=DESede
        "
      JAVA_OPTS: "
        -Ddb.driver=org.postgresql.Driver
        -Ddb.username=alfresco
        -Ddb.password=alfresco
        -Ddb.url=jdbc:postgresql://postgres:5432/alfresco 
        -Delasticsearch.createIndexIfNotExists=true
        -Dindex.subsystem.name=elasticsearch
        -Delasticsearch.host=elasticsearch
        -Delasticsearch.indexName=${ELASTICSEARCH_INDEX_NAME}  
        -Dshare.host=127.0.0.1
        -Dshare.port=8080 
        -Dalfresco.host=localhost
        -Dalfresco.port=8080
        -Daos.baseUrlOverwrite=http://localhost:8080/alfresco/aos
        -Dmessaging.broker.url=\"failover:(nio://activemq:61616)?timeout=3000&jms.useCompression=true\"
        -Ddeployment.method=DOCKER_COMPOSE
        -Dtransform.service.enabled=true
        -Dtransform.service.url=http://transform-router:8095
        -Dsfs.url=http://shared-file-store:8099/ 
        -DlocalTransform.core-aio.url=http://transform-core-aio:8090/
        -Dcsrf.filter.enabled=false
        -XX:MinRAMPercentage=50 -XX:MaxRAMPercentage=80
        "
    volumes:
      - ./exactTermSearch.properties:/usr/local/tomcat/webapps/alfresco/WEB-INF/classes/alfresco/search/elasticsearch/config/exactTermSearch.properties        

  transform-router:
    image: quay.io/alfresco/alfresco-transform-router:${TRANSFORM_ROUTER_TAG}
    environment:
      JAVA_OPTS: " -XX:MinRAMPercentage=50 -XX:MaxRAMPercentage=80"
      ACTIVEMQ_URL: "nio://activemq:61616"
      CORE_AIO_URL: "http://transform-core-aio:8090"
      FILE_STORE_URL: "http://shared-file-store:8099/alfresco/api/-default-/private/sfs/versions/1/file"
    ports:
      - "8095:8095"
    links:
      - activemq

  transform-core-aio:
    image: alfresco/alfresco-transform-core-aio:${TRANSFORM_ENGINE_TAG}
    environment:
      JAVA_OPTS: " -XX:MinRAMPercentage=50 -XX:MaxRAMPercentage=80"
      ACTIVEMQ_URL: "nio://activemq:61616"
      FILE_STORE_URL: "http://shared-file-store:8099/alfresco/api/-default-/private/sfs/versions/1/file"
    ports:
      - "8090:8090"
    links:
      - activemq

  shared-file-store:
    image: quay.io/alfresco/alfresco-shared-file-store:${SHARED_FILE_STORE_TAG}
    environment:
      JAVA_OPTS: " -XX:MinRAMPercentage=50 -XX:MaxRAMPercentage=80"
      scheduler.content.age.millis: 86400000
      scheduler.cleanup.interval: 86400000
    ports:
      - "8099:8099"
    volumes:
      - shared-file-store-volume:/tmp/Alfresco/sfs

  share:
    image: quay.io/alfresco/alfresco-share:${SHARE_TAG}
    mem_limit: 1g
    environment:
      REPO_HOST: "alfresco"
      REPO_PORT: "8080"
      JAVA_OPTS: "
        -XX:MinRAMPercentage=50
        -XX:MaxRAMPercentage=80
        -Dalfresco.host=localhost
        -Dalfresco.port=8080
        -Dalfresco.context=alfresco
        -Dalfresco.protocol=http
        "


  postgres:
    image: postgres:${POSTGRES_TAG}
    environment:
      - POSTGRES_PASSWORD=alfresco
      - POSTGRES_USER=alfresco
      - POSTGRES_DB=alfresco
    command: postgres -c max_connections=300 -c log_min_messages=LOG
    ports:
      - "5432:5432"

  elasticsearch:
    image: elasticsearch:${ELASTICSEARCH_TAG}
    platform: linux/amd64
    environment:
      - xpack.security.enabled=false
      - discovery.type=single-node
    ulimits:
      memlock:
        soft: -1
        hard: -1
      nofile:
        soft: 65536
        hard: 65536
    cap_add:
      - IPC_LOCK
    ports:
      - 9200:9200
      - 9300:9300

  kibana:
    image: kibana:${KIBANA_TAG}
    platform: linux/amd64
    environment:
      - ELASTICSEARCH_HOSTS=http://elasticsearch:9200
    ports:
      - 5601:5601
    depends_on:
      - elasticsearch

  live-indexing:
    image: quay.io/alfresco/alfresco-elasticsearch-live-indexing:${LIVE_INDEXING_TAG}
    depends_on:
      - elasticsearch
      - alfresco
    environment:
      SPRING_ELASTICSEARCH_REST_URIS: http://elasticsearch:9200
      SPRING_ACTIVEMQ_BROKERURL: nio://activemq:61616
      ALFRESCO_ACCEPTEDCONTENTMEDIATYPESCACHE_BASEURL: http://transform-core-aio:8090/transform/config
      ALFRESCO_SHAREDFILESTORE_BASEURL: http://shared-file-store:8099/alfresco/api/-default-/private/sfs/versions/1/file/

  # zeppelin

  activemq:
    image: alfresco/alfresco-activemq:${ACTIVEMQ_TAG}
    ports:
      - "8161:8161" # Web Console
      - "5672:5672" # AMQP
      - "61616:61616" # OpenWire
      - "61613:61613" # STOMP

  # digital-workspace

  # control-center

  proxy:
    image: alfresco/alfresco-acs-nginx:${ACS_NGINX_TAG}
    depends_on:
      - alfresco  
    environment:  
      - DISABLE_SYNCSERVICE=true   
      - DISABLE_ADW=true  
      - DISABLE_CONTROL_CENTER=true  
      - USE_SSL=false
    ports:
      - "8080:8080"
    links:  
      - alfresco 
      - share 

  # sync-service

volumes:
  shared-file-store-volume:
    driver_opts:
      type: tmpfs
      device: tmpfs

.env file describes the versions used:

# Docker Image versions
ALFRESCO_TAG=23.1.0
SEARCH_TAG=2.0.8.2
SHARE_TAG=23.1.0
POSTGRES_TAG=14.4
TRANSFORM_ROUTER_TAG=4.0.0
TRANSFORM_ENGINE_TAG=5.0.0
SHARED_FILE_STORE_TAG=4.0.0
ACTIVEMQ_TAG=5.17.1-jre11-rockylinux8
ADW_TAG=4.2.0
CONTROL_CENTER_TAG=8.2.0
ACS_NGINX_TAG=3.4.2
SYNC_SERVICE_TAG=4.0.0
ELASTICSEARCH_TAG=7.10.1
KIBANA_TAG=7.10.1
OPENSEARCH_TAG=1.3.13
OPENSEARCH_DASHBOARDS_TAG=1.3.13
LIVE_INDEXING_TAG=4.0.0

ELASTICSEARCH_INDEX_NAME=alfresco

[brunosantos]

Hello,

My exact term search configuration is correct. I'm capable of visualize every property in exactTermSearch.properties file in mode debug.

By the way, for your property 'caseNumber' it's configured how?

<tokenised>false</tokenised>

or

<tokenised>both</tokenised>

Do you placed it too in the exactTermSearch.properties file like

alfresco.cross.locale.property.N={http://your-domain/model/1.0}caseNumber

?

Thank you in advance

[brunosantos]

Hi,

Could you answer my question, please? I'd like to check if our configuration is the same.

Thanks

[agermain]

Hi Angelborrow,

I have the same exact problem.

Lets say I have 2 documents. one with sc:test = test and one with sc:test = test whitespace

when I execute this query : select * from sc:doc where sc:test='test whitespace' The only result I got is the wrong document (the one with sc:test = test not the one with sc:test = test whitespace)

It looks like exact term Search doesn't work when there is a whitespace. The only way I got the right result is when I used db-cmis instead of cmis-strict.

[agermain]

Here an example :

But I shouldn't be getting this document because I searched : 'test whitespace'.