[Feature Request] Support for Index Aliases in SQL Queries with JOIN and WHERE Clauses

Is your feature request related to a problem? Please describe

I’m currently working with OpenSearch SQL and have encountered a limitation that I hope could be addressed in future releases.

In my use case, I need to perform SQL queries involving JOIN operations and WHERE conditions using index aliases instead of direct index names. However, when I attempt this, I receive the following error:

 {
  "error": {
    "reason": "Error occurred in OpenSearch engine: no such index [The index 'detection_data' could not be found. Note that wildcard indices are not permitted in SQL.]",
    "details": "[The index 'detection_data' could not be found. Note that wildcard indices are not permitted in SQL.] IndexNotFoundException[no such index [The index 'detection_data' could not be found. Note that wildcard indices are not permitted in SQL.]]"
  },
  "status": 404
}

Attached my query below for additional info. FYI: In my current setup, I’m using aliases (events_data and process_data) to abstract underlying index names for better maintainability and modularity.

POST _plugins/_sql?format=json
{
  "query" : "SELECT obj.*, process.log.attributes.Image, process.log.attributes.ProcessId FROM events_data as obj INNER JOIN `process_data` as process ON obj.spanId = process.log.attributes.EventUuId AND process.log.attributes.EventUuId = 'dfb1db72-7ec7-4e89-b753-57638759b4f6'"
}

Describe the solution you'd like

Would it be possible to add support for using index aliases in SQL queries, particularly in scenarios involving:

• JOIN operations
• WHERE filters
• Table aliasing for readability and modularity

This feature would greatly enhance flexibility and maintainability, especially in environments where index names are versioned or abstracted through aliases.

Related component

Search:Query Capabilities

Describe alternatives you've considered

No response

Additional context

No response

Start by reproducing the failure through the _plugins/_sql endpoint with the supplied JOIN query and configured index aliases events_data and process_data. Trace how SQL table names are resolved before execution, then define completion as successful alias resolution for JOIN and WHERE queries with regression coverage for the reported request.