[BUG] Missing bucket in terms aggregation with missing value

[noCharger]

Describe the bug

When executing a terms aggregation with a specified missing value, the expected bucket for documents without the specified field is not appearing in the result. This is identified via an SQL IT comparing v3.0.0 alpha1 to non-alpha1 snapshot.

Related component

No response

To Reproduce

Example docs

{
  "_shards": {
    "total": 1,
    "failed": 0,
    "successful": 1,
    "skipped": 0
  },
  "hits": {
    "hits": [
      {
        "_index": "opensearch-sql_test_index_game_of_thrones",
        "_source": {
          "gender": "F",
          "name": {
            "ofHerName": 1,
            "firstname": "Daenerys",
            "lastname": "Targaryen"
          },
          "nickname": "Daenerys \"Stormborn\"",
          "titles": [
            "motherOfDragons",
            "queenOfTheAndals",
            "breakerOfChains",
            "Khaleesi"
          ],
          "house": "Targaryen",
          "parents": {
            "mother": "Rhaella",
            "father": "Aerys"
          }
        },
        "_id": "1",
        "_score": 1
      },
      {
        "_index": "opensearch-sql_test_index_game_of_thrones",
        "_source": {
          "gender": "M",
          "name": {
            "firstname": "Eddard",
            "ofHisName": 1,
            "lastname": "Stark"
          },
          "titles": [
            "lordOfWinterfell",
            "wardenOfTheNorth",
            "handOfTheKing"
          ],
          "house": "Stark",
          "parents": {
            "mother": "Lyarra",
            "father": "Rickard"
          }
        },
        "_id": "2",
        "_score": 1
      },
      {
        "_index": "opensearch-sql_test_index_game_of_thrones",
        "_source": {
          "gender": "M",
          "name": {
            "firstname": "Brandon",
            "ofHisName": 4,
            "lastname": "Stark"
          },
          "titles": [
            "princeOfWinterfell"
          ],
          "house": "Stark",
          "parents": {
            "mother": "Catelyn",
            "father": "Eddard"
          },
          "@wolf": "Summer"
        },
        "_id": "3",
        "_score": 1
      },
      {
        "_index": "opensearch-sql_test_index_game_of_thrones",
        "_source": {
          "gender": "M",
          "name": {
            "firstname": "Jaime",
            "ofHisName": 1,
            "lastname": "Lannister"
          },
          "titles": [
            "kingSlayer",
            "lordCommanderOfTheKingsguard",
            "Ser"
          ],
          "house": "Lannister",
          "parents": {
            "mother": "Joanna",
            "father": "Tywin"
          }
        },
        "_id": "4",
        "_score": 1
      },
      {
        "_index": "opensearch-sql_test_index_game_of_thrones",
        "_source": {
          "seat": "Dragonstone",
          "sigil": "Dragon",
          "words": "fireAndBlood",
          "hname": "Targaryen"
        },
        "_id": "5",
        "_score": 1
      },
      {
        "_index": "opensearch-sql_test_index_game_of_thrones",
        "_source": {
          "seat": "Winterfell",
          "sigil": "direwolf",
          "words": "winterIsComing",
          "hname": "Stark"
        },
        "_id": "6",
        "_score": 1
      },
      {
        "_index": "opensearch-sql_test_index_game_of_thrones",
        "_source": {
          "seat": "CasterlyRock",
          "sigil": "lion",
          "words": "hearMeRoar",
          "hname": "Lannister"
        },
        "_id": "7",
        "_score": 1
      }
    ],
    "total": {
      "value": 7,
      "relation": "eq"
    },
    "max_score": 1
  },
  "took": 57,
  "timed_out": false
}

Example Query

{
  "from": 0,
  "size": 0,
  "_source": {
    "includes": [
      "COUNT"
    ],
    "excludes": []
  },
  "aggregations": {
    "nick": {
      "terms": {
        "field": "nickname",
        "missing": "no_nickname",
        "size": 10,
        "min_doc_count": 1,
        "shard_min_doc_count": 0,
        "show_term_doc_count_error": false,
        "order": [
          {
            "_count": "desc"
          },
          {
            "_key": "asc"
          }
        ]
      },
      "aggregations": {
        "COUNT_0": {
          "value_count": {
            "field": "_index"
          }
        }
      }
    }
  }
}

Example result on 3.0.0 alpha1

 1> Query Result:
  1> {
  1>   "_shards": {
  1>     "total": 1,
  1>     "failed": 0,
  1>     "successful": 1,
  1>     "skipped": 0
  1>   },
  1>   "hits": {
  1>     "hits": [],
  1>     "total": {
  1>       "value": 7,
  1>       "relation": "eq"
  1>     },
  1>     "max_score": null
  1>   },
  1>   "took": 78,
  1>   "timed_out": false,
  1>   "aggregations": {"nick": {
  1>     "doc_count_error_upper_bound": 0,
  1>     "sum_other_doc_count": 0,
  1>     "buckets": [
  1>       {
  1>         "COUNT(*)": {"value": 1},
  1>         "doc_count": 1,
  1>         "key": "daenerys"
  1>       },
  1>       {
  1>         "COUNT(*)": {"value": 1},
  1>         "doc_count": 1,
  1>         "key": "stormborn"
  1>       }
  1>     ]
  1>   }}
  1> }

Expected behavior

Expect result (from non-alpha1)

    {
      "_shards": {
        "total": 1,
        "failed": 0,
        "successful": 1,
        "skipped": 0
      },
      "hits": {
        "hits": [],
        "total": {
          "value": 7,
          "relation": "eq"
        },
        "max_score": null
      },
      "took": 76,
      "timed_out": false,
      "aggregations": {"nick": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "COUNT(*)": {"value": 6},
            "doc_count": 6,
            "key": "no_nickname"
          },
          {
            "COUNT(*)": {"value": 1},
            "doc_count": 1,
            "key": "daenerys"
          },
          {
            "COUNT(*)": {"value": 1},
            "doc_count": 1,
            "key": "stormborn"
          }
        ]
      }}
    }

Additional Details

No response

[peterzhuamazon]

Hi @andrross @msfroh @prudhvigodithi could you help take a look at this?
As this is blocking the SQL bump to 3.0.0.0-alpha1.

Thanks!

[sandeshkr419]

@jainankitk Can you take first stab at this?

[peterzhuamazon]

Hi @sandeshkr419 @jainankitk could you help with this issue?

Thanks!

[kkewwei]

@peterzhuamazon @sandeshkr419 @noCharger I have tested with the newest main branch, it seems ok.

{
   "took": 74,
   "timed_out": false,
   "_shards": {
      "total": 1,
      "successful": 1,
      "skipped": 0,
      "failed": 0
   },
   "hits": {
      "total": {
         "value": 7,
         "relation": "eq"
      },
      "max_score": null,
      "hits": []
   },
   "aggregations": {
      "nick": {
         "doc_count_error_upper_bound": 0,
         "sum_other_doc_count": 0,
         "buckets": [
            {
               "key": "no_nickname",
               "doc_count": 6,
               "COUNT_0": {
                  "value": 6
               }
            },
            {
               "key": "Daenerys \"Stormborn\"",
               "doc_count": 1,
               "COUNT_0": {
                  "value": 1
               }
            }
         ]
      }
   }
}
``