[BUG] Index patterns in nested collection queries

[parked-toes]

What is the bug?

Using wildcards in index name works for simple queries only. Self-joined queries fail. More details in index-patterns-for-querying-nested-collection discussion:

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

1. Use sample data from:
   https://opensearch.org/docs/latest/search-plugins/sql/sql/partiql/

Map array of object as nested type.

2. Query nested collection using index pattern employees* :

SELECT e.name AS employeeName,
       p.name AS projectName
FROM employees* AS e,
       e.projects AS p
WHERE p.name LIKE '%security%'

Result:

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

What is the expected behavior?
Search all indexes matching a given pattern

What is your host/environment?
2.6.0

[dai-chen]

@parked-toes Thanks for reporting the issue! I guess this is because the PartiQL in legacy engine doesn't support index pattern. Will take this into account when we migrate it to V2 engine.

[ns-se-ops]

I have self-managed Opensearch cluster [version 1.3.9] - It is working on it without any issues

It is breaking after the domain was upgraded to version 2.0.1

[2023-05-25T04:10:10,479][INFO ][o.o.s.l.p.RestSqlAction  ] [XYZ] [45f0bb0e-d75b-4685-8196-fce24348d34b] Incoming request /_plugins/_sql?pretty=true: ( SELECT identifier AS employeeName, identifier AS projectName FROM table e, table p WHERE identifier LIKE 'string_literal' )
[2023-05-25T04:10:10,554][WARN ][stderr                   ] [XYZ] line 1:8 missing '(' at '.'
[2023-05-25T04:10:10,607][WARN ][o.o.s.l.e.f.PrettyFormatRestExecutor] [XYZ] Error happened in pretty formatter
java.lang.IllegalArgumentException: Index type [employees*] does not exist
	at org.opensearch.sql.legacy.executor.format.SelectResultSet.loadFromEsState(SelectResultSet.java:180) ~[legacy-2.0.1.0.jar:?]
	at org.opensearch.sql.legacy.executor.format.SelectResultSet.<init>(SelectResultSet.java:105) ~[legacy-2.0.1.0.jar:?]
	at org.opensearch.sql.legacy.executor.format.Protocol.loadResultSet(Protocol.java:92) ~[legacy-2.0.1.0.jar:?]
	at org.opensearch.sql.legacy.executor.format.Protocol.<init>(Protocol.java:62) ~[legacy-2.0.1.0.jar:?]
	at org.opensearch.sql.legacy.executor.format.PrettyFormatRestExecutor.buildProtocolForDefaultQuery(PrettyFormatRestExecutor.java:103) ~[legacy-2.0.1.0.jar:?]
	at org.opensearch.sql.legacy.executor.format.PrettyFormatRestExecutor.execute(PrettyFormatRestExecutor.java:67) [legacy-2.0.1.0.jar:?]
	at org.opensearch.sql.legacy.executor.format.PrettyFormatRestExecutor.execute(PrettyFormatRestExecutor.java:43) [legacy-2.0.1.0.jar:?]
	at org.opensearch.sql.legacy.executor.AsyncRestExecutor.doExecuteWithTimeMeasured(AsyncRestExecutor.java:150) [legacy-2.0.1.0.jar:?]
	at org.opensearch.sql.legacy.executor.AsyncRestExecutor.lambda$async$1(AsyncRestExecutor.java:110) [legacy-2.0.1.0.jar:?]
	at org.opensearch.sql.legacy.utils.LogUtils.lambda$withCurrentContext$1(LogUtils.java:59) [legacy-2.0.1.0.jar:?]
	at org.opensearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:739) [opensearch-2.0.1.jar:2.0.1]
	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) [?:?]

---

w.r.t. AWS Opensearch service - Managed Cluster

I have verified that this issue can be re-produced with AWS Opensearch service version OS_2.x and later. It means that the below SQL query is working fine with AWS Opensearch service version OS_1.3 and lower :

SELECT e.name AS employeeName,
       p.name AS projectName
FROM employees* AS e,
       e.projects AS p
WHERE p.name LIKE '%security%'

The below steps were executed on AWS Opensearch service - Managed Cluster Version OS_2.3

1. Creating index with projects field with nested type

PUT employees_nested_test
{
  "mappings": {
    "properties": {
      "projects": {
        "type": "nested"
      }
    }
  }
}

2. Inserting sample data as mentioned in the documentation

POST employees_nested_test/_bulk?refresh
{"index":{"_id":"1"}}
{"id":3,"name":"Bob Smith","title":null,"projects":[{"name":"SQL Spectrum querying","started_year":1990},{"name":"SQL security","started_year":1999},{"name":"OpenSearch security","started_year":2015}]}
{"index":{"_id":"2"}}
{"id":4,"name":"Susan Smith","title":"Dev Mgr","projects":[]}
{"index":{"_id":"3"}}
{"id":6,"name":"Jane Smith","title":"Software Eng 2","projects":[{"name":"SQL security","started_year":1998},{"name":"Hello security","started_year":2015,"address":[{"city":"Dallas","state":"TX"}]}]}

3. querying data with index-name

POST /_plugins/_sql 
{
  "query" : "SELECT e.name AS employeeName, p.name AS projectName FROM employees_nested_test AS e, e.projects AS p WHERE p.name LIKE '%security%'"
}

Output :

{
  "schema": [
    {
      "name": "name",
      "alias": "employeeName",
      "type": "text"
    },
    {
      "name": "projects.name",
      "alias": "projectName",
      "type": "text"
    }
  ],
  "total": 4,
  "datarows": [
    [
      "Jane Smith",
      "SQL security"
    ],
    [
      "Jane Smith",
      "Hello security"
    ],
    [
      "Bob Smith",
      "SQL security"
    ],
    [
      "Bob Smith",
      "OpenSearch security"
    ]
  ],
  "size": 4,
  "status": 200
}

4. Querying data with index-pattern employees*

POST /_plugins/_sql 
{
  "query" : "SELECT e.name AS employeeName, p.name AS projectName FROM employees* AS e, e.projects AS p WHERE p.name LIKE '%security%'"
}

Output :

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

• Executed Step 4 in AWS Opensearch service - Managed Cluster ES_7.10 & OS_1.3

POST /_plugins/_sql 
{
  "query" : "SELECT e.name AS employeeName, p.name AS projectName FROM employees* AS e, e.projects AS p WHERE p.name LIKE '%security%'"
}

Got output as mentioned in Step 3.