[BUG] LIKE escape special characters with '\' not working

[chloe-zh]

What is the bug?
We followed MySQL syntax and tried to escape special character like '%' and '_' with the escape char ‘\', because MySQL uses '\' as the escape character, but seems it does not work.
Our observation only applies to some queries that are pushed down to the DSL.

How can one reproduce the bug?
Example A:

SELECT column1 FROM test_identitifer_2 WHERE column1 LIKE 'testEscape\%'

Explain:

{
  "error": {
    "reason": "Invalid SQL query",
    "details": "Failed to parse request payload",
    "type": "IllegalArgumentException"
  },
  "status": 400
}

Example B:

SELECT column1 FROM test_identitifer_2 WHERE column1 LIKE 'testEscape\\%'

Explain:

{
  "root": {
    "name": "ProjectOperator",
    "description": {
      "fields": "[column1]"
    },
    "children": [
      {
        "name": "ElasticsearchIndexScan",
        "description": {
          "request": """ElasticsearchQueryRequest(indexName=test_identifier_2, sourceBuilder={"from":0,"size":200,"timeout":"1m","query":{"wildcard":{"column1.keyword":{"wildcard":"testEscape\\*","boost":1.0}}},"_source":{"includes":["column1"],"excludes":[]},"sort":[{"_doc":{"order":"asc"}}]}, searchDone=false)"""
        },
        "children": []
      }
    ]
  }
}

What is the expected behavior?
The default escape is expected to work. It would be better if users are able to use alternative escape character e.g.

SELECT column1 FROM test_identitifer_2 WHERE column1 LIKE 'testEscape/%'  ESCAPE '/'

What is your host/environment?

• OS: Linux
• Version: AES7.10
• Plugins: All plugins are enabled by default

Do you have any screenshots?
NA

Do you have any additional context?
NA

Proposal to fix
Add instructions for escape cases to build wildcard query from LIKE: https://github.com/opensearch-project/sql/blob/main/opensearch/src/main/java/org/opensearch/sql/opensearch/storage/script/filter/FilterQueryBuilder.java#L59

[dai-chen]

@chloe-zh Thanks for reporting the issue with very detailed info. Will check if this can be fixed soon.

[jingyali-apr]

@chloe-zh Thanks for reporting the issue with very detailed info. Will check if this can be fixed soon.

Hey @dai-chen,
Do we have update for this issue?

Thank you

[dai-chen]

@chloe-zh Thanks for reporting the issue with very detailed info. Will check if this can be fixed soon.

Hey @dai-chen, Do we have update for this issue?

Thank you

@jingyali-apr We haven't worked on this yet. Will evaluate the solution first. Thanks!

[MaxKsyunz]

What version is AES7.10? The sample query is working in most recent SQL plugin.

result from current engine

PS /Users/maxk/inst/act> http :9201/_plugins/_sql format==jdbc query="SELECT Tags FROM sa3dprinting WHERE Tags LIKE 'testEscape\\%'"
HTTP/1.1 200 OK
content-encoding: gzip
content-length: 122
content-type: application/json; charset=UTF-8

{
    "datarows": [],
    "schema": [
        {
            "name": "Tags",
            "type": "text"
        }
    ],
    "size": 0,
    "status": 200,
    "total": 0
}

when legacy engine is forced

PS /Users/maxk/inst/act> http :9201/_plugins/_sql format==json  query="SELECT Tags FROM sa3dprinting WHERE Tags LIKE 'testEscape\\%'"
HTTP/1.1 200 OK
content-encoding: gzip
content-length: 140
content-type: application/json; charset=UTF-8

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

node details

PS /Users/maxk/inst/act> http :9201
HTTP/1.1 200 OK
content-encoding: gzip
content-length: 346
content-type: application/json; charset=UTF-8

{
    "cluster_name": "docker-cluster",
    "cluster_uuid": "qP59y9QBTHGfysGkijDm-w",
    "name": "opensearch-node-230",
    "tagline": "The OpenSearch Project: https://opensearch.org/",
    "version": {
        "build_date": "2022-09-09T00:07:12.137133581Z",
        "build_hash": "6f6e84ebc54af31a976f53af36a5c69d474a5140",
        "build_snapshot": false,
        "build_type": "tar",
        "distribution": "opensearch",
        "lucene_version": "9.3.0",
        "minimum_index_compatibility_version": "7.0.0",
        "minimum_wire_compatibility_version": "7.10.0",
        "number": "2.3.0"
    }
}

explain from current engine

PS /Users/maxk/inst/act> http :9201/_plugins/_sql/_explain format==jdbc query="SELECT Tags FROM sa3dprinting WHERE Tags LIKE 'testEscape\\%'"
HTTP/1.1 200 OK
content-encoding: gzip
content-length: 335
content-type: application/json; charset=UTF-8

{
    "root": {
        "children": [
            {
                "children": [],
                "description": {
                    "request": "OpenSearchQueryRequest(indexName=sa3dprinting, sourceBuilder={\"from\":0,\"size\":200,\"timeout\":\"1m\",\"query\":{\"wildcard\":{\"Tags\":{\"wildcard\":\"testEscape\\\\\\\\*\",\"boost\":1.0}}},\"_source\":{\"includes\":[\"Tags\"],\"excludes\":[]},\"sort\":[{\"_doc\":{\"order\":\"asc\"}}]}, searchDone=false)"
                },
                "name": "OpenSearchIndexScan"
            }
        ],
        "description": {
            "fields": "[Tags]"
        },
        "name": "ProjectOperator"
    }
}

[Yury-Fridlyand]

Maybe it was fixed by #696