[BUG] Subquery aliasing doesn't work with ` characters

[ghost]

What is the bug?
Subquery aliasing doesn't work with ` characters.

How can one reproduce the bug?
Steps to reproduce the behavior:

1. Make a query for

SELECT `a`.`LOD - Fixed - 1 (copy 2)` AS `LOD - Fixed - 1 (copy 2)`
FROM (
  SELECT MIN(`Staples`.`Order Date`) AS `LOD - Fixed - 1 (copy 2)`
  FROM `staples` `Staples`
  GROUP BY `Staples`.`Customer Name`
) `a`
GROUP BY `LOD - Fixed - 1 (copy 2)`
ORDER BY `LOD - Fixed - 1 (copy 2)` ASC

2. See "ClassCastException"

What is the expected behavior?
Behaviour should match if the alias of the inner query is just a which is a valid result.

What is your host/environment?

• OpenSearch 1.3.1

[dai-chen]

I can reproduce the issue by ODS sample data in playground:

POST _plugins/_sql
{
  "query": """
    SELECT
      `a`.`Origin` AS `Origin`,
      AVG(`AvgTicketPrice`) AS `avgPrice`
    FROM (
      SELECT `Origin`, `AvgTicketPrice`
      FROM `opensearch_dashboards_sample_data_flights`
      WHERE `FlightDelay` = True
    ) AS `a`
    GROUP BY `a`.`Origin`
  """
}

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "class com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource cannot be cast to class com.alibaba.druid.sql.ast.statement.SQLJoinTableSource (com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource and com.alibaba.druid.sql.ast.statement.SQLJoinTableSource are in unnamed module of loader java.net.FactoryURLClassLoader @55e42684)",
    "type": "ClassCastException"
  },
  "status": 503
}

I suspect this issue is the root cause of other subquery issue:

• Group By results in RuntimeException[BUG] #342
• [BUG] A query with a subquery fails #347
• Lack of support for subqueries results in RuntimeException[BUG] #304

Because I tested complicated subquery in FROM clause without back quoted alias, I see no problem and all outer grouping and sorting can be done in memory. Probably we can update the limitation doc: https://github.com/opensearch-project/sql/blob/main/docs/user/limitations/limitations.rst#subqueries-in-the-from-clause

POST _plugins/_sql
{
  "query": """
    SELECT b.Origin, b.avgPrice
    FROM (
      SELECT
        a.Origin AS Origin,
        AVG(AvgTicketPrice) AS avgPrice
      FROM (
        SELECT Origin, AvgTicketPrice
        FROM opensearch_dashboards_sample_data_flights
        WHERE FlightDelay = True
      ) AS a
      GROUP BY a.Origin
    ) AS b
    ORDER BY b.avgPrice DESC
  """
}

{
  "schema": [
    {
      "name": "Origin",
      "type": "keyword"
    },
    {
      "name": "avgPrice",
      "type": "double"
    }
  ],
  "datarows": [
    [
      "Charles de Gaulle International Airport",
      999.1396484375
    ],
    [
      "Huntsville International Carl T Jones Field",
      993.1741333007812
    ],
    [
      "Minneapolis-St Paul International/Wold-Chamberlain Airport",
      952.474853515625
    ],
...

Explain output:
{
  "root": {
    "name": "ProjectOperator",
    "description": {
      "fields": "[Origin, avgPrice]"
    },
    "children": [
      {
        "name": "SortOperator",
        "description": {
          "sortList": {
            "avgPrice": {
              "sortOrder": "DESC",
              "nullOrder": "NULL_LAST"
            }
          }
        },
        "children": [
          {
            "name": "ProjectOperator",
            "description": {
              "fields": "[Origin, avgPrice]"
            },
            "children": [
              {
                "name": "AggregationOperator",
                "description": {
                  "aggregators": "[AVG(AvgTicketPrice)]",
                  "groupBy": "[Origin]"
                },
                "children": [
                  {
                    "name": "ProjectOperator",
                    "description": {
                      "fields": "[Origin, AvgTicketPrice]"
                    },
                    "children": [
                      {
                        "name": "OpenSearchIndexScan",
                        "description": {
                          "request": """OpenSearchQueryRequest(indexName=opensearch_dashboards_sample_data_flights, sourceBuilder={"from":0,"size":200,"timeout":"1m","query":{"term":{"FlightDelay":{"value":true,"boost":1.0}}},"_source":{"includes":["Origin","AvgTicketPrice"],"excludes":[]},"sort":[{"_doc":{"order":"asc"}}]}, searchDone=false)"""
                        },
                        "children": []
                      }
                    ]
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  }
}

[dai-chen]

Analyzing failure caused fall back to legacy which throws ClassCastException due to unsupported FROM subquery with aggregation.

The detailed error is: "The qualifier [a] of qualified name [a.Origin] must be an field name, index name or its alias". The reason may be visitSubqueryRelation doesn't push alias name to symbol table.

[dai-chen]

It seems the back quoted alias name is pushed to symbol table rather than unquoted: