[BUG] Pagination of index aliases is not supported

[MaxKsyunz]

What is the bug?
Pagination of index alias does not work.

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

1. Create an index -- indexA -- and load data
2. Create an alias -- aliasA -- for the index
3. Send query like SELECT * FROM aliasA with non-zero fetch_size
4. See error like

{
    "error": {
        "details": "Index type [alias_phrases] does not exist",
        "reason": "There was internal problem at backend",
        "type": "IllegalArgumentException"
    },
    "status": 500
}

What is the expected behavior?
Paging an index directly or its alias should produce the same result.

See this stack overflow question for reference.

[MaxKsyunz]

This is not specific to pagination. Legacy engine does not handle aliases properly. It expects OpenSearch mappings response to contain table name from the FROM clause as root-level property . However for aliases it is the name of the aliased index.

The following two responses are identical because alias_phrases is an alias to phrases.

maxk@maxk-macos sql % http :9200/alias_phrases/_mappings


{
    "phrases": {
        "mappings": {
            "properties": {
                "insert_time2": {
                    "type": "date"
                },
                "phrase": {
                    "store": true,
                    "type": "text"
                },
                "test field": {
                    "type": "long"
                }
            }
        }
    }
}

to

maxk@maxk-macos sql % http :9200/phrases/_mappings 
{
    "phrases": {
        "mappings": {
            "properties": {
                "insert_time2": {
                    "type": "date"
                },
                "phrase": {
                    "store": true,
                    "type": "text"
                },
                "test field": {
                    "type": "long"
                }
            }
        }
    }
}

[rrlamichhane]

This applies to filters as well:

This query would work:

POST _plugins/_sql/
{
  "query" : "SELECT start_time FROM my_opensearch_index"
}

This query with filter would NOT work:

POST _plugins/_sql/
{
  "query" : "SELECT start_time FROM my_opensearch_index"
},
"filter" : {
    "range" : {
      "start_time" : {
        "lt" : "2023"
      }
    }
  }

# ERROR
{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "Index type [my_opensearch_index] does not exist",
    "type": "IllegalArgumentException"
  },
  "status": 500
}

[MaxKsyunz]

Thank you @rrlamichhane .

Created #1412 to track.

[ctellechea2001]

@MaxKsyunz Any update on that? I have the same situation with Pagination and filter using index aliases.

Thanks in advance!

[ctellechea2001]

@joshuali925 @rrlamichhane @Yury-Fridlyand Do you have any update on that issue?

I discovered today that the issue related to non-zero fetch_size was resolved in the latest version of opensearch v2.11.1 (keep happened in 2.7.0)
But the issue related to filter keep happens in the latest version.

POST _plugins/_sql/
{
"query": "select * from transitions",
"fetch_size": 1,
"filter": {
"term": {
"reason": "bad ip"
}
}
}

{ "error": { "reason": "There was internal problem at backend", "details": "Index type [transitions] does not exist", "type": "IllegalArgumentException" }, "status": 500 }

always doing that queries to the index ALIAS. if you go directly everything is fine.

[ctellechea2001]

@MaxKsyunz Do you have any update on that? We saw that the same issue still happening in the latest versions of opensearch.

The new information that we discovered is that the issue happened with the default output format(jdbc) POST _plugins/_sql/format=jdbc. With any other format it is working fine. (json,csv).

I discovered today that the issue related to non-zero fetch_size was resolved in the latest version of opensearch v2.11.1 (keep happened in 2.7.0)
But the issue related to filter keep happens in the latest version.

POST _plugins/_sql/
{
"query": "select * from transitions",
"fetch_size": 1,
"filter": {
"term": {
"reason": "bad ip"
}
}
}

{ "error": { "reason": "There was internal problem at backend", "details": "Index type [transitions] does not exist", "type": "IllegalArgumentException" }, "status": 500 }

always doing that queries to the index ALIAS. if you go directly everything is fine.