Unable to sort in 8.4+ if a field doesn't exist.

[desean1625]

Elasticsearch Version

8.10.3

Installed Plugins

No response

Java Version

bundled

OS Version

docker

Problem Description

Cannot sort by field if there are blank values
{
"name": "es-1-master-2",
"cluster_name": "es-1",
"cluster_uuid": "kQ8-xy6FQeqjR8Zmc_a9HQ",
"version": {
"number": "8.10.3",
"build_flavor": "default",
"build_type": "docker",
"build_hash": "c63272efed16b5a1c25f3ce500715b7fddf9a9fb",
"build_date": "2023-10-05T10:15:55.152563867Z",
"build_snapshot": false,
"lucene_version": "9.7.0",
"minimum_wire_compatibility_version": "7.17.0",
"minimum_index_compatibility_version": "7.0.0"
},
"tagline": "You Know, for Search"
}

Steps to Reproduce

Steps to recreate issue.
From kibana add sample data sets ['Sample flight data','Sample eCommerce orders']
Create dataview
name:test
index patter:kibana_*
timefield: --- I don't want to use the time filter ---

From discover sort by AvgTicketPrice, or run query

POST kibana_*/_search
{
    "sort": [
        {
            "AvgTicketPrice": {
                "order": "desc",
                "unmapped_type": "boolean"
            }
        }
    ],
    "fields": [
        {
            "field": "*",
            "include_unmapped": "true"
        }
    ],
    "size": 500
}

Can't sort on field [AvgTicketPrice]; the field has incompatible sort types: [LONG] and [FLOAT] across shards!

Logs (if relevant)

No response

[desean1625]

I think this PR might be the related #88399

[elasticsearchmachine]

Pinging @elastic/es-search (Team:Search)

[benwtrent]

"unmapped_type": "boolean"

AvgTicketPrice isn't a boolean field. So you are trying to sort things that are two different types?

@desean1625 does it work when you set unmapped_type to the same type as AvgTicketPrice?

[MiguelNunes3344]

@desean1625 I reproduced your steps, when i switch the type to float which is the same type of avgticketprice, it is works.

[ndmitch311]

@Villegas12 We are seeing this issue on our production system. (What is Glenn's username to tag?)
We used sample data on this network best we could to show reproduction steps of what our users are experiencing.
Also opened elastic/kibana#172105 which relates.

[benwtrent]

@desean1625 @Villegas12 if you use a "unmapped_type": "boolean" and then search over things where values are missing, but the values that DO exist are not boolean, this is expected behavior.

Looking at the Kibana issue, this does seem like a bug in Discover, I will comment over there to clarify.

Basically, Elasticsearch is attempting to sort things of two different types. It cannot do that. unmapped_type should equal the expected mapped type.

Elasticsearch could be better about merging sorted types if one of the types has no results (and thus doesn't actually need to be merged). I am not sure if that is the case here or not.

[benwtrent]

OK, I have confirmed indeed that #88399 changed the behavior.

The following test passes if I revert the change in that PR. But fails currently.

    public void testSortMixedFieldTypesWithNoDocsForOneType() {
        assertAcked(prepareCreate("index_long").setMapping("foo", "type=long").get());
        assertAcked(prepareCreate("index_other").setMapping("bar", "type=keyword").get());
        assertAcked(prepareCreate("index_double").setMapping("foo", "type=double").get());

        prepareIndex("index_long").setId("1").setSource("foo", "123").get();
        prepareIndex("index_long").setId("2").setSource("foo", "124").get();
        prepareIndex("index_other").setId("1").setSource("bar", "124").get();
        refresh();

        assertNoFailures(prepareSearch("index_long", "index_double", "index_other").addSort(new FieldSortBuilder("foo").unmappedType("boolean")).setSize(10));
    }

Basically, if there are no docs in the other index with the sort field at all, we don't run into any issues. This is the changed behavior, we are eagerly checking even if there are no docs with the field that mismatch the types.

I will talk with the team to see if we can think of a way to not throw in the scenario.

Thank you @desean1625 @Villegas12 for digging in and finding this issue!

[desean1625]

@benwtrent Thanks. Is there any chance of a backport to 8.6.2?

[benwtrent]

@desean1625 we don't do any additional bug-fix releases once a minor has been released. We will work on getting it into 8.11.x.

[desean1625]

Yes, sorry that makes sense. What I meant to ask was any change of a back port to the 8.6 branch and a minor release 8.6.3 to fix this.

[benwtrent]

What I meant to ask was any change of a back port to the 8.6 branch and a minor release 8.6.3 to fix this.

There will not be an 8.6.3 release. We only do bug fix releases for the last major 7.x.y and the last minor 8.11.y (8.11 is the currently released minor, 8.12 will be next). https://www.elastic.co/support/eol

Elastic will provide maintenance (e.g., provide bug fixes or address security vulnerabilities) for each major release during the Maintenance Term (as defined in the Elastic Support Services Policy) either through either a new minor release or a new maintenance release to the latest minor version. For example, if major release series 8.x is within the Maintenance Term, and 8.3 is the current minor version, fixes will be made either through the next minor version (e.g., 8.4) or a maintenance release in 8.3 (e.g., 8.3.2).