[SEARCH-903] Order by with a custom property (type = text) results in error Created: 11-Jun-18  Updated: 28-Jul-20  Resolved: 27-Jun-18

Status: Done
Project: Search and Discovery
Component/s: None
Affects Version/s: Insight Engine 1.0
Fix Version/s: Insight Engine 1.0

Type: Bug
Reporter: Meenal Bhave [X] (Inactive) Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: 0 minutes
Time Spent: Not Specified
Original Estimate: 0 minutes

Bug Priority:
Category 2

 Description   

Setup:

ACS 6.0 + Insight Engine +DBVisualiser

Steps:

  •  Created a custom model (see attached for reference) with a type / aspect and a property with data type say amount, with data type say, double.
  • Activate the model
  • Create 3 documents (See attached for reference), add a specific type / aspect created above (I added type = expense, aspect = ParkEx).
  • Edit Properties for the content (no, amount, location) and assign the value for the properties above.
  • Run a query to list the expenses by location:

Query:

select cm_name, finance_amount, finance_location
from alfresco
order by finance_location desc

Results in response:

{
    "error": {
        "errorKey": "framework.exception.ApiDefault",
        "statusCode": 400,
        "briefSummary": "05110257 Unable to execute the query, error caused by: Error from server at http://localhost:8986/solr/alfresco: unexpected docvalues type SORTED_SET for field 'text@s__lt@{Finance}Location' (expected=SORTED). Re-index with correct docvalues type.",
        "stackTrace": "For security reasons the stack trace is no longer displayed, but the property is kept for previous versions",
        "descriptionURL": "https://api-explorer.alfresco.com"
    }
}

DBVisualiser: Error:

16:53:39  START Executing for: 'InsightAlfresco' [Generic]
16:53:39  FAILED  [SELECT - 0 rows, 0.042 secs]  java.sql.SQLException: java.io.IOException: 05110256 Unable to execute the query, error caused by: Error from server at http://localhost:8986/solr/alfresco: unexpected docvalues type SORTED_SET for field 'text@s__lt@{Finance}Location' (expected=SORTED). Re-index with correct docvalues type.  
select cm_name, finance_amount, finance_location
from alfresco
order by finance_location desc;
16:53:39  END Execution 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.042/0.000 secs   [0 successful, 1 errors]

Error:

<order by> works with the existing text field but fails with the following error when ordered by text property / field in the custom model.

Caused by: java.sql.SQLException: Error while executing SQL "select cm_name, finance_amount, finance_location from alfresco order by finance_location desc limit 1000": java.io.IOException: org.apache.solr.client.solrj.impl.HttpSolrClient$RemoteSolrException: Error from server at http://localhost:8986/solr/alfresco: unexpected docvalues type SORTED_SET for field 'text@s__lt@{Finance}Location' (expected=SORTED). Re-index with correct docvalues type.
    at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
    at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
    at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
    at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:218)
    at org.alfresco.solr.stream.JDBCStream.open(JDBCStream.java:271)
    ... 41 more
Caused by: java.lang.RuntimeException: java.io.IOException: org.apache.solr.client.solrj.impl.HttpSolrClient$RemoteSolrException: Error from server at http://localhost:8986/solr/alfresco: unexpected docvalues type SORTED_SET for field 'text@s__lt@{Finance}Location' (expected=SORTED). Re-index with correct docvalues type.
    at org.alfresco.solr.sql.SolrEnumerator.<init>(SolrEnumerator.java:51)
    at org.alfresco.solr.sql.SolrTable$1.enumerator(SolrTable.java:315)
    at org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
    at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:89)
    at org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:196)
    at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:67)
    at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:44)
    at org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:607)
    at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:599)
    at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:615)
    at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:148)
    ... 43 more
Caused by: java.io.IOException: org.apache.solr.client.solrj.impl.HttpSolrClient$RemoteSolrException: Error from server at http://localhost:8986/solr/alfresco: unexpected docvalues type SORTED_SET for field 'text@s__lt@{Finance}Location' (expected=SORTED). Re-index with correct docvalues type.
    at org.alfresco.solr.stream.SearchStream.open(SearchStream.java:254)
    at org.alfresco.solr.stream.LimitStream.open(LimitStream.java:62)
    at org.alfresco.solr.stream.AlfrescoExpressionStream.open(AlfrescoExpressionStream.java:99)
    at org.alfresco.solr.sql.SolrEnumerator.<init>(SolrEnumerator.java:49)
    ... 53 more
Caused by: org.apache.solr.client.solrj.impl.HttpSolrClient$RemoteSolrException: Error from server at http://localhost:8986/solr/alfresco: unexpected docvalues type SORTED_SET for field 'text@s__lt@{Finance}Location' (expected=SORTED). Re-index with correct docvalues type.
    at org.apache.solr.client.solrj.impl.HttpSolrClient.executeMethod(HttpSolrClient.java:610)
    at org.apache.solr.client.solrj.impl.HttpSolrClient.request(HttpSolrClient.java:279)
    at org.apache.solr.client.solrj.impl.HttpSolrClient.request(HttpSolrClient.java:268)
    at org.apache.solr.client.solrj.SolrRequest.process(SolrRequest.java:160)
    at org.apache.solr.client.solrj.SolrRequest.process(SolrRequest.java:177)
    at org.alfresco.solr.stream.SearchStream.open(SearchStream.java:247)
    ... 56 more


 Comments   
Comment by Michael Suzuki [X] (Inactive) [ 27-Jun-18 ]

This is not a bug, we do not support ordering on text.

Comment by Meenal Bhave [X] (Inactive) [ 06-Jul-18 ]

Tested that order by text property works well, when the text fields support indexing. i.e. property / field type is either of list of values (whole or partial match), or pattern match (unique or many matches)

If text property is defined with indexing option as <none> or <freetext>, the order by does not work, as order by isn't supported for this indexing type.

Generated at Tue Aug 04 07:17:51 BST 2020 using JIRA 7.6.3#76005-sha1:8a4e38d34af948780dbf52044e7aafb13a7cae58.