[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 https://github.com/opensearch-project/OpenSearch/pull/5462/.

[penghuo]

@nknize go throught https://github.com/opensearch-project/OpenSearch/pull/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.