Time range causing empty result

[Heenawter]

Elasticsearch Version

9.1.0-SNAPSHOT

Installed Plugins

No response

Java Version

bundled

OS Version

Darwin Kernel Version 24.3.0

Problem Description

In my investigation of elastic/kibana#218953, I found that the ES server is giving different results in the 9.1.0-SNAPSHOT version compared to previous versions. See the "Steps to reproduce" section for a more thorough description.

Steps to Reproduce

1. Enter export ES_SNAPSHOT_MANIFEST="https://storage.googleapis.com/kibana-ci-es-snapshots-daily/9.1.0/archives/20250418-021642_492b0363/manifest.json" into your command window

2. Enter yarn es snapshot into the same command window

3. Enter yarn start into another command window

4. Install Web Logs sample data

5. Navigate to the dev console

6. Enter the following query:

   GET kibana_sample_data_logs/_search
   {
     "size": 0,
     "timeout": "1000ms",
     "terminate_after": 100000,
     "query": {
       "bool": {
         "filter": [
           {
             "bool": {
               "filter": [
                 {
                   "range": {
                     "timestamp": {
                       "format": "strict_date_optional_time",
                       "gte": "2025-04-16T14:45:41.953Z",
                       "lte": "2025-04-23T14:45:41.953Z"
                     }
                   }
                 }
               ]
             }
           }
         ]
       }
     },
     "aggs": {
       "filteredSuggestions": {
         "filter": {
           "prefix": {
             "machine.os.keyword": {
               "value": "w",
               "case_insensitive": true
             }
           }
         },
         "aggs": {
           "suggestions": {
             "terms": {
               "size": 10,
               "field": "machine.os.keyword",
               "shard_size": 10,
               "order": {
                 "_count": "desc"
               }
             }
           }
         }
       }
     }
   }
   

In the 9.1.0-SNAPSHOT version, it returns an empty result:

{
  "took": 1,
  "timed_out": false,
  "terminated_early": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 1608,
      "relation": "eq"
    },
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "filteredSuggestions": {
      "doc_count": 0,
      "suggestions": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": []
      }
    }
  }
}

But when testing in 8.18 Kibana, it returns the expected filtered values:

  "took": 4,
  "timed_out": false,
  "terminated_early": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 1608,
      "relation": "eq"
    },
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "filteredSuggestions": {
      "doc_count": 956,
      "suggestions": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": "win xp",
            "doc_count": 348
          },
          {
            "key": "win 7",
            "doc_count": 309
          },
          {
            "key": "win 8",
            "doc_count": 299
          }
        ]
      }
    }
  }
}

However, when I remove the time range filter from the 9.1.0-SNAPSHOT query, I no longer get empty buckets:

• Query:

  GET kibana_sample_data_logs/_search
  {
    "size": 0,
    "timeout": "1000ms",
    "terminate_after": 100000,
    "query": {
      "bool": {
        "filter": []
      }
    },
    "aggs": {
      "filteredSuggestions": {
        "filter": {
          "prefix": {
            "machine.os.keyword": {
              "value": "w",
              "case_insensitive": true
            }
          }
        },
        "aggs": {
          "suggestions": {
            "terms": {
              "size": 10,
              "field": "machine.os.keyword",
              "shard_size": 10,
              "order": {
                "_count": "desc"
              }
            }
          }
        }
      }
    }
  }
  

• Result:

  {
    "took": 2,
    "timed_out": false,
    "terminated_early": false,
    "_shards": {
      "total": 1,
      "successful": 1,
      "skipped": 0,
      "failed": 0
    },
    "hits": {
      "total": {
        "value": 10000,
        "relation": "gte"
      },
      "max_score": null,
      "hits": []
    },
    "aggregations": {
      "filteredSuggestions": {
        "doc_count": 8512,
        "suggestions": {
          "doc_count_error_upper_bound": 0,
          "sum_other_doc_count": 0,
          "buckets": [
            {
              "key": "win xp",
              "doc_count": 2885
            },
            {
              "key": "win 7",
              "doc_count": 2814
            },
            {
              "key": "win 8",
              "doc_count": 2813
            }
          ]
        }
      }
    }
  }
  

Logs (if relevant)

No response

[benwtrent]

Behavior is troubling, I am not sure whats up here.

But, adding the filter to the query returns valid buckets

GET kibana_sample_data_logs/_search
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "filter": [
              {
                "range": {
                  "timestamp": {
                    "format": "strict_date_optional_time",
                    "gte": "2025-04-16T14:45:41.953Z",
                    "lte": "2025-04-23T14:45:41.953Z"
                  }
                }
              },
              {
                "prefix": {
                  "machine.os.keyword": {
                    "value": "w",
                    "case_insensitive": true
                  }
                }
              }
            ]
          }
        }
      ]
    }
  },
  "aggs": {
    "filteredSuggestions": {
      "filter": {
        "prefix": {
          "machine.os.keyword": {
            "value": "w",
            "case_insensitive": true
          }
        }
      },
      "aggs": {
        "suggestions": {
          "terms": {
            "size": 10,
            "field": "machine.os.keyword",
            "shard_size": 10,
            "order": {
              "_count": "desc"
            }
          }
        }
      }
    }
  }
}

Or a simple exists query (which likely gets rewritten to a match_all):

GET kibana_sample_data_logs/_search
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "filter": [
              {
                "exists": {
                    "field": "timestamp"
                }
              }
            ]
          }
        }
      ]
    }
  },
  "aggs": {
    "filteredSuggestions": {
      "filter": {
        "prefix": {
          "machine.os.keyword": {
            "value": "w",
            "case_insensitive": true
          }
        }
      },
      "aggs": {
        "suggestions": {
          "terms": {
            "size": 10,
            "field": "machine.os.keyword",
            "shard_size": 10,
            "order": {
              "_count": "desc"
            }
          }
        }
      }
    }
  }
}

But, indeed, the following returns nothing:

GET kibana_sample_data_logs/_search
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "filter": [
              {
                "range": {
                  "timestamp": {
                    "format": "strict_date_optional_time",
                    "gte": "2025-04-16T14:45:41.953Z",
                    "lte": "2025-04-24T14:45:41.953Z"
                  }
                }
              }
            ]
          }
        }
      ]
    }
  },
  "aggs": {
    "filteredSuggestions": {
      "filter": {
        "prefix": {
          "machine.os.keyword": {
            "value": "w",
            "case_insensitive": true
          }
        }
      }
    }
  }
}

[benwtrent]

Likely related: #126955

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[elasticsearchmachine]

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