[BUG] Wildcard query breaking change in 2.x branch.

[penghuo]

Describe the bug
Wildcard query has breaking change in latest 2.x and main branch.

• SQL plugin integration test failed. https://github.com/opensearch-project/sql/actions/runs/3661675873/jobs/6190099425

To Reproduce

PUT {{baseUrl}}/wildcard_00001
Content-Type: application/json

{
  "mappings": {
    "properties": {
      "firstname": {
        "type": "text"
      }
    }
  }
}

POST {{baseUrl}}/wildcard_00001/_doc
Content-Type: application/json

{
  "firstname": "Amber JOHnny"
}

• query with wildcard.

GET {{baseUrl}}/wildcard_00001/_search
Content-Type: application/json

{
  "from": 0,
  "size": 200,
  "timeout": "1m",
  "query": {
    "wildcard": {
      "firstname": {
        "wildcard": "Ambe?",
        "boost": 1
      }
    }
  },
  "_source": {
    "includes": [
      "firstname"
    ],
    "excludes": []
  },
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ]
}

But no hits

{
  "took": 88,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 0,
      "relation": "eq"
    },
    "max_score": null,
    "hits": []
  }
}

Expected behavior

• Test on 2.4.

{
  "hits": {
    "total": {
      "value": 1,
      "relation": "eq"
    },
    "max_score": null,
    "hits": [
      {
        "_index": "wildcard_00001",
        "_id": "5smC-YQBBVP7erYv_lLT",
        "_score": null,
        "_source": {
          "firstname": "Amber JOHnny"
        },
        "sort": [
          0
        ]
      }
    ]
  }
}

Plugins
Please list all plugins currently enabled.

Screenshots
If applicable, add screenshots to help explain your problem.

Host/Environment (please complete the following information):

• OS: macOS
• Version: 2.x/main branch

Additional context
Add any other context about the problem here.

[penghuo]

may related to #5462.

[penghuo]

@nknize go throught #5462 and I agree it is the bug fix. Correct me if I am wrong, my understanding are:

• Using standard analyzer and case_insensitive set to false, the expectation is wildcard pattern should match normalized term.
• SQL user should aware the text filed is normalized as lower case letter and pattern in Like operator match against normalized token.

If my understanding is correct, the query I posted is a bug and not a breaking change. But the query looks natural, not sure if there are users who depend on such a query.

[nknize]

• Using standard analyzer and case_insensitive set to false, the expectation is wildcard pattern should match normalized term.

Standard Analyzer uses the lowercase token filter. So all indexed text will be lowercased. Setting case_insensitve to false on the wildcard query (which is the default behavior) means the pattern "Ambe?" should not match. So the behavior here is correct. Previously it was a bug. Set case_insensitive to true here and the pattern will match.

[penghuo]

Thanks @nknize.

The SQL plugin will change the code accordingly.