Uploaded image for project: 'Nuxeo Platform'
  1. Nuxeo Platform
  2. NXP-27168

Fix NXQL operator ILIKE when querying elasticsearch

    Details

    • Release Notes Summary:
      Suggestion page provider now uses Elasticsearch fulltext index.
    • Backlog priority:
      550
    • Sprint:
      nxAI Sprint 11.1.9, nxplatform 11.1.22, nxplatform 11.1.23, nxplatform 11.1.24
    • Story Points:
      0

      Description

      When page provider default_collection is configured to use ES, the Collections filter does not suggest any collection, even though collections exist.

      How to reproduce:

      • create a collection named Collection 1
      • in nuxeo.conf, add default_collection to configuration variable elasticsearch.override.pageproviders
      • restart the Nuxeo Platform
      • in the JSF UI, navigate to the faceted search under the tab SEARCH
      • in the Collections widget in the search form, enter Coll

      Expected result: the Collections widget suggests collection Collection 1
      Actual result: the Collections widget does not suggest anything

      This is due to the use of the operator ILIKE in the NXQL query of page provider default_collection.

      Changing the operator ILIKE to LIKE mitigates the issue but makes the search case-sensitive.

      Workaround: override the page provider in Studio:

        <require>org.nuxeo.ecm.collections.pageprovider</require>
      
        <extension target="org.nuxeo.ecm.platform.query.api.PageProviderService" point="providers">
      
          <coreQueryPageProvider name="default_collection">
            <property name="maxResults">PAGE_SIZE</property>
            <pattern escapeParameters="true" quoteParameters="false">
              SELECT * FROM Document WHERE dc:title LIKE '?%' AND ecm:mixinType = 'Collection' AND
              (ecm:primaryType != 'Favorites' OR (ecm:primaryType = 'Favorites' AND dc:creator = '?')) AND
              ecm:isProxy = 0 AND ecm:mixinType != 'HiddenInNavigation' AND
              ecm:isCheckedInVersion = 0 AND ecm:currentLifeCycleState != 'deleted'
            </pattern>
            <parameter>#{currentUser.name}</parameter>
            <sort ascending="true" column="dc:title"/>
            <pageSize>50</pageSize>
          </coreQueryPageProvider>
      
        </extension>
      

      Here is the ES DSL query (from log file elastic.log):

      2019-04-04 18:47:04,278 DEBUG [http-nio-0.0.0.0-8080-exec-4][org.nuxeo.elasticsearch.core.ElasticSearchServiceImpl] Search query: curl -XGET 'http://localhost:9200/nuxeo/doc/_search?pretty&search_type=dfs_query_then_fetch' -d '{
        "from" : 0,
        "size" : 20,
        "query" : {
          "bool" : {
            "must" : [
              {
                "match_phrase_prefix" : {
                  "dc:title.lowercase" : {
                    "query" : "coll",
                    "slop" : 0,
                    "max_expansions" : 50,
                    "boost" : 1.0
                  }
                }
              },
              {
                "constant_score" : {
                  "filter" : {
                    "term" : {
                      "ecm:mixinType" : {
                        "value" : "Collection",
                        "boost" : 1.0
                      }
                    }
                  },
                  "boost" : 1.0
                }
              },
              {
                "bool" : {
                  "should" : [
                    {
                      "constant_score" : {
                        "filter" : {
                          "bool" : {
                            "must_not" : [
                              {
                                "term" : {
                                  "ecm:primaryType" : {
                                    "value" : "Favorites",
                                    "boost" : 1.0
                                  }
                                }
                              }
                            ],
                            "disable_coord" : false,
                            "adjust_pure_negative" : true,
                            "boost" : 1.0
                          }
                        },
                        "boost" : 1.0
                      }
                    },
                    {
                      "bool" : {
                        "must" : [
                          {
                            "constant_score" : {
                              "filter" : {
                                "term" : {
                                  "ecm:primaryType" : {
                                    "value" : "Favorites",
                                    "boost" : 1.0
                                  }
                                }
                              },
                              "boost" : 1.0
                            }
                          },
                          {
                            "constant_score" : {
                              "filter" : {
                                "term" : {
                                  "dc:creator" : {
                                    "value" : "Administrator",
                                    "boost" : 1.0
                                  }
                                }
                              },
                              "boost" : 1.0
                            }
                          }
                        ],
                        "disable_coord" : false,
                        "adjust_pure_negative" : true,
                        "boost" : 1.0
                      }
                    }
                  ],
                  "disable_coord" : false,
                  "adjust_pure_negative" : true,
                  "boost" : 1.0
                }
              },
              {
                "constant_score" : {
                  "filter" : {
                    "term" : {
                      "ecm:isProxy" : {
                        "value" : "0",
                        "boost" : 1.0
                      }
                    }
                  },
                  "boost" : 1.0
                }
              },
              {
                "constant_score" : {
                  "filter" : {
                    "bool" : {
                      "must_not" : [
                        {
                          "term" : {
                            "ecm:mixinType" : {
                              "value" : "HiddenInNavigation",
                              "boost" : 1.0
                            }
                          }
                        }
                      ],
                      "disable_coord" : false,
                      "adjust_pure_negative" : true,
                      "boost" : 1.0
                    }
                  },
                  "boost" : 1.0
                }
              },
              {
                "constant_score" : {
                  "filter" : {
                    "term" : {
                      "ecm:isVersion" : {
                        "value" : "0",
                        "boost" : 1.0
                      }
                    }
                  },
                  "boost" : 1.0
                }
              },
              {
                "constant_score" : {
                  "filter" : {
                    "bool" : {
                      "must_not" : [
                        {
                          "term" : {
                            "ecm:currentLifeCycleState" : {
                              "value" : "deleted",
                              "boost" : 1.0
                            }
                          }
                        }
                      ],
                      "disable_coord" : false,
                      "adjust_pure_negative" : true,
                      "boost" : 1.0
                    }
                  },
                  "boost" : 1.0
                }
              }
            ],
            "disable_coord" : false,
            "adjust_pure_negative" : true,
            "boost" : 1.0
          }
        },
        "_source" : {
          "includes" : [
            "_id"
          ],
          "excludes" : [ ]
        },
        "sort" : [
          {
            "dc:title" : {
              "order" : "asc",
              "unmapped_type" : "string"
            }
          }
        ]
      }'{{PathInfo,/automation/WebUI.RunOperationInSeam}{RemoteAddr,192.168.129.1}{RequestURL,http://localhost:8080/nuxeo/site/automation/WebUI.RunOperationInSeam}{ServletPath,/site}{SessionID,F66C028FE2C90C6D531C9FE5BB81E9DA.nuxeo}{UserPrincipal,Administrator}{tx,28300000047544d4944}}
      2019-04-04 18:47:04,303 DEBUG [http-nio-0.0.0.0-8080-exec-4][org.nuxeo.elasticsearch.core.ElasticSearchServiceImpl] Response: {"took":22,"timed_out":false,"_shards":{"total":5,"successful":5,"skipped":0,"failed":0},"hits":{"total":0,"max_score":null,"hits":[]}}{{PathInfo,/automation/WebUI.RunOperationInSeam}{RemoteAddr,192.168.129.1}{RequestURL,http://localhost:8080/nuxeo/site/automation/WebUI.RunOperationInSeam}{ServletPath,/site}{SessionID,F66C028FE2C90C6D531C9FE5BB81E9DA.nuxeo}{UserPrincipal,Administrator}{tx,28300000047544d4944}}
      

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day, 6 hours
                  1d 6h

                    PagerDuty

                    Error rendering 'com.pagerduty.jira-server-plugin:PagerDuty'. Please contact your Jira administrators.