Unable to escape characters for string literals[BUG]

[ghost]

Describe the bug
Unable to escape characters for string literals

Or for query issues:

1. Run the query SELECT 'i\'m'
2. See unexpected result i\'m

Expected behavior
The result should be i'm

Screenshots

[ghost]

This issue affects TDVT tests:

• exprtests/standard\setup.logical.txt

[Yury-Fridlyand]

Adding more info to this bug:

• PPL and SQL both affected
• character escaping rules are not applied to all string literals

Some examples:

SELECT 'Pat O''Hanrahan & <Matthew Eldridge]'''

returns
Pat O''Hanrahan & <Matthew Eldridge]'' instead of Pat O'Hanrahan & <Matthew Eldridge]'

SELECT * FROM staples WHERE `Customer Name` = "Mary O'Rourke";

returns valid result, but

SELECT * FROM staples WHERE `Customer Name` = 'Mary O\'Rourke';

yields empty result set.
In case of PPL

source=staples | WHERE `Customer Name` = "Mary O'Rourke";

returns valid data, but

source=staples | WHERE `Customer Name` = 'Mary O\'Rourke';

doesn't.

staples table is a sample data from TDVT suite.

[MaxKsyunz]

Adding the following test to sql/src/test/java/org/opensearch/sql/sql/parser/AstExpressionBuilderTest.java will also reproduce the problem.

 @Test
  public void canBuildEscapedStringLiteral() {
    assertEquals(
            stringLiteral("O'Rourke"),
            buildExprAst("'O\\'Rourke'")
    );
  }

[dai-chen]

Tested and I think this is already fixed in #696:

POST _plugins/_sql
{
  "query": """
    SELECT 'O\'Rourke'
  """
}
{
  "schema": [
    {
      "name": """'O\'Rourke'""",
      "type": "keyword"
    }
  ],
  "datarows": [
    [
      """O\'Rourke"""
    ]
  ],
  "total": 1,
  "size": 1,
  "status": 200
}

[Yury-Fridlyand]

Fix for escape characters is coming in Bit-Quill#159. It is a breaking change, so it is targeted to 3.x branch.