[BUG] Unknown index when querying data with fetch_size

[deathjoin]

What is the bug? When querying data with fetch_size api responses with Unknown index.

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

1. Create test index

PUT test.data-1

2. Add some data

POST _bulk
{ "create": { "_index": "test.data-1", "_id": "1" } }
{ "type": "first", "message": "Hello" }
{ "create": { "_index": "test.data-1", "_id": "2" } }
{ "type": "first", "message": "Hello" }
{ "create": { "_index": "test.data-1", "_id": "3" } }
{ "type": "second", "message": "World" }

3. Run query

POST /_plugins/_sql
{
  "query": "SELECT * FROM `test.*`",
  "fetch_size": 100
}

4. Face the error

{
  "error": {
    "reason": "Error occurred in OpenSearch engine: Unknown index [`test.*`]",
    "details": "org.opensearch.sql.legacy.rewriter.matchtoterm.VerificationException: Unknown index [`test.*`]\nFor more details, please send request for Json format to see the raw response from OpenSearch engine.",
    "type": "VerificationException"
  },
  "status": 400
}

What is the expected behavior? Data returned

{
  "schema": [
    {
      "name": "message",
      "type": "text"
    },
    {
      "name": "type",
      "type": "text"
    }
  ],
  "datarows": [
    [
      "Hello",
      "first"
    ],
    [
      "Hello",
      "first"
    ],
    [
      "World",
      "second"
    ]
  ],
  "total": 3,
  "size": 3,
  "status": 200
}

What is your host/environment?

• OS: Linux (Docker)
• Version 2.3.0
• Plugins: SQL 2.3.0.0

Do you have any additional context?

Query was working on OpenSearch 1.3.0 and we just updated to 2.3.0

Only way I found query working is to run without fetch_size and other fields like filter, but it is not an option for us 😢

POST /_plugins/_sql
{
  "query": "SELECT * FROM `test.*`"
}

docker logs error:

[2022-10-21T14:35:12,552][INFO ][o.o.s.l.p.RestSqlAction  ] [opensearch-0] [99a577f7-2e35-49bd-b998-d7562becc848] Incoming request /_plugins/_sql?pretty=true: ( SELECT * FROM table )
[2022-10-21T14:35:12,553][ERROR][o.o.s.l.p.RestSqlAction  ] [opensearch-0] 99a577f7-2e35-49bd-b998-d7562becc848 Client side error during query execution
org.opensearch.sql.legacy.rewriter.matchtoterm.VerificationException: Unknown index [`test.*`]
	at org.opensearch.sql.legacy.rewriter.matchtoterm.TermFieldRewriter.checkMappingCompatibility(TermFieldRewriter.java:242) ~[legacy-2.3.0.0.jar:?]
	at org.opensearch.sql.legacy.rewriter.matchtoterm.TermFieldRewriter.visit(TermFieldRewriter.java:71) ~[legacy-2.3.0.0.jar:?]
	at com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock.accept0(MySqlSelectQueryBlock.java:255) ~[druid-1.0.15.jar:1.0.15]
	at com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock.accept0(MySqlSelectQueryBlock.java:246) ~[druid-1.0.15.jar:1.0.15]
	at com.alibaba.druid.sql.ast.SQLObjectImpl.accept(SQLObjectImpl.java:40) ~[druid-1.0.15.jar:1.0.15]
	at com.alibaba.druid.sql.ast.SQLObjectImpl.acceptChild(SQLObjectImpl.java:62) ~[druid-1.0.15.jar:1.0.15]
	at com.alibaba.druid.sql.ast.statement.SQLSelect.accept0(SQLSelect.java:85) ~[druid-1.0.15.jar:1.0.15]
	at com.alibaba.druid.sql.ast.SQLObjectImpl.accept(SQLObjectImpl.java:40) ~[druid-1.0.15.jar:1.0.15]
	at com.alibaba.druid.sql.ast.SQLObjectImpl.acceptChild(SQLObjectImpl.java:62) ~[druid-1.0.15.jar:1.0.15]
	at com.alibaba.druid.sql.ast.expr.SQLQueryExpr.accept0(SQLQueryExpr.java:55) ~[druid-1.0.15.jar:1.0.15]
	at com.alibaba.druid.sql.ast.SQLObjectImpl.accept(SQLObjectImpl.java:40) ~[druid-1.0.15.jar:1.0.15]
	at org.opensearch.sql.legacy.query.OpenSearchActionFactory.create(OpenSearchActionFactory.java:114) ~[legacy-2.3.0.0.jar:?]
	at org.opensearch.sql.legacy.plugin.SearchDao.explain(SearchDao.java:52) ~[legacy-2.3.0.0.jar:?]
	at org.opensearch.sql.legacy.plugin.RestSqlAction.explainRequest(RestSqlAction.java:208) [legacy-2.3.0.0.jar:?]
	at org.opensearch.sql.legacy.plugin.RestSqlAction.lambda$prepareRequest$1(RestSqlAction.java:164) [legacy-2.3.0.0.jar:?]
	at org.opensearch.sql.opensearch.executor.Scheduler.lambda$withCurrentContext$0(Scheduler.java:30) [opensearch-2.3.0.0.jar:?]
	at org.opensearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:747) [opensearch-2.3.0.jar:2.3.0]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
	at java.lang.Thread.run(Thread.java:833) [?:?]

Cluster settings almost untouched

{
  "persistent" : {
    "cluster" : {
      "max_shards_per_node" : "3000"
    },
    "plugins" : {
      "index_state_management" : {
        "metadata_migration" : {
          "status" : "1"
        },
        "template_migration" : {
          "control" : "-1"
        }
      }
    }
  },
  "transient" : { }
}

Cluster health

{
  "cluster_name" : "test",
  "status" : "yellow",
  "timed_out" : false,
  "number_of_nodes" : 2,
  "number_of_data_nodes" : 2,
  "discovered_master" : true,
  "discovered_cluster_manager" : true,
  "active_primary_shards" : 1995,
  "active_shards" : 2002,
  "relocating_shards" : 0,
  "initializing_shards" : 0,
  "unassigned_shards" : 53,
  "delayed_unassigned_shards" : 0,
  "number_of_pending_tasks" : 0,
  "number_of_in_flight_fetch" : 0,
  "task_max_waiting_in_queue_millis" : 0,
  "active_shards_percent_as_number" : 97.42092457420924
}

And shards

{
    "index" : "test.data-1",
    "shard" : "0",
    "prirep" : "p",
    "state" : "STARTED",
    "docs" : "3",
    "store" : "4.6kb",
    "ip" : "10.10.0.2",
    "node" : "opensearch-1"
  },
  {
    "index" : "test.data-1",
    "shard" : "0",
    "prirep" : "r",
    "state" : "STARTED",
    "docs" : "3",
    "store" : "4.6kb",
    "ip" : "10.10.0.3",
    "node" : "opensearch-0"
  },