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?