SQL JOIN with Subquery issue

Plugins SQL
1

This is my query.

select a.name 
from (
 select distinct ext.keyword as name 
 from log-210110) a 
join (
 select distinct ext.keyword as name 
 from log-210111) b 
on a.name = b.name

My query returns error below.

{
  "error": {
    "reason": "Error occurred in Elasticsearch engine: Field name [ext.keyword] is ambiguous",
    "details": "com.amazon.opendistroforelasticsearch.sql.legacy.rewriter.matchtoterm.VerificationException: Field name [ext.keyword] is ambiguous\nFor more details, please send request for Json format to see the raw response from elasticsearch engine.",
    "type": "VerificationException"
  },
  "status": 400
}

Anyone could show me the way? Thanks in advance:)

2

@mhkang589 Thanks for reaching out! Currently queries like yours are not supported yet. Simple JOIN or subqueries in FROM clause can only be supported separately. We will evaluate this and try to add the support in future.

3

@daichen Thanks for the reply. Have a nice day:)

4

Is this supported now? I am trying to execute the below query -

select * from table_one t where t.document_id not in (select r.document_id from table_two r)

but I am getting the below error -

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "Unsupported subquery",
    "type": "IllegalStateException"
  },
  "status": 503
}
5

Hi @azfar,
Unfortunately, subquery and join support is very limited.

6

Thanks for the reply @yuryf-bq .