Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[BUG] unsupported operator: if when using sql API #2320

Open
sudssf opened this issue Oct 18, 2023 · 5 comments
Open

[BUG] unsupported operator: if when using sql API #2320

sudssf opened this issue Oct 18, 2023 · 5 comments
Labels
bug Something isn't working

Comments

@sudssf
Copy link

sudssf commented Oct 18, 2023

What is the bug?
Opensearch version 2.8.0
we are migrating from elasticsearch to opensearch and planning to use nested sql queries to replace runtime field which were supported in elasticsearch sql API

after following documentation from https://opensearch.org/docs/2.0/search-plugins/sql/sql-ppl-api/#query-api

when I try to run sql query using if operator I get following exception
{
"error": {
"reason": "There was internal problem at backend",
"details": "unsupported operator: if",
"type": "UnsupportedOperationException"
},
"status": 503
}

same query works in query workbench. if I use nested query only group by fields are returned.

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

  1. call _sql API to run query in jdbc format
  2. post sql query
  3. go to query workbench and verify query works

steps to replicate in dev console (queries works workbench)

POST _bulk
{ "index" : { "_index" : "test_bulk1", "_id" : "1" } }
{ "a": "a", "id": 1 }



GET test_bulk1/_search


POST /_plugins/_sql?format=jdbc
{
  "query": "SELECT IF(id=1, 1, 0 ) as id FROM test_bulk1    group by a",
  "fetch_size" : 10
}

POST /_plugins/_sql?format=jdbc
{
  "query": "SELECT *FROM (SELECT IF(id=1, 1, 0 ) as id FROM test_bulk1 group by a)as tmp",
  "fetch_size" : 10
}

What is the expected behavior?
output from query workbench and SQL needs to be consistent

What is your host/environment?

  • OS: Centos 7
  • Opensearch Version 2.8.0
  • Plugins

Do you have any screenshots?
N/A
Do you have any additional context?
N/A

@sudssf sudssf added bug Something isn't working untriaged labels Oct 18, 2023
@sudssf
Copy link
Author

sudssf commented Oct 18, 2023

I found the issue since I want to group by data and also transform before group by , I need to move group by in outer nested query. I will test and reopen issue if I get same error

@sudssf
Copy link
Author

sudssf commented Oct 18, 2023

I found multiple issues and created feature request to add support for scripted field #2325 . any guidance is appreciated.
following is example which does not work

PUT /test_bulk3
{
  "mappings": {
    "properties": {
      "s_label": {
        "type": "keyword"
      },
      "seq":{
        "type": "integer"
      }
    }
  }
}

POST test_bulk3/_bulk
{ "index":{} }
{ "s_label": "a", "seq": 1 }
{ "index":{} }
{ "s_label": "b", "seq": 2 }


POST /_plugins/_sql?format=jdbc
{
  "query": "SELECT * FROM test_bulk3",
  "fetch_size" : 10
}


POST /_plugins/_sql?format=jdbc
{
  "query": "SELECT  IF(s_label = 'a', 1, 0), seq FROM test_bulk3",
  "fetch_size" : 10
}

@sudssf sudssf reopened this Oct 18, 2023
@sudssf
Copy link
Author

sudssf commented Oct 19, 2023

looks like if I remove fetch_size parameter I get the results, but no cursor is present
if I add fetch_size parameter I get error similar to following

\n  \"error\": {\n    \"reason\": \"Invalid SQL query\",\n    \"details\": \"could not parse sqlBinaryOpExpr need to be identifier/valuable gotclass com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr with value:...  \"type\": \"SqlParseException\"\n  },\n  \"status\": 400\n}"

if fetch_size is the issue , I wonder if I can paginate using OFFSET,LIMIT at the end of the query?

@sudssf
Copy link
Author

sudssf commented Oct 21, 2023

adding @Yury-Fridlyand for help as I see there PR created for pagination related changes #1752

Yuri do you know what is limitation of V1 LIMIT OFFSET,PAGE_SIZE approach for pagination?

background: we are migrating from ES 7.17 to Opensearch 2.8.0+ and facing issues with some of the queries which use pagination and runtime fields.
I am trying to use nested SQL queries to overcome runtime fields

@LantaoJin
Copy link
Member

LantaoJin commented Jun 19, 2024

The failed query in #2320 (comment) has 2 problems.

  1. SELECT IF(id=1, 1, 0 ) as id FROM test_bulk1 group by a is an invalid query which is missing aggregation function. The non-aggregating expression "id" is based on columns which are not participating in the GROUP BY clause. An valid aggregation query, for example, could be SELECT max(IF(id=1, 1, 0)) as max_id FROM test_bulk1 group by a. Invalid query should throw AnalysisException ([BUG] Invalid aggregation statement should throw SemanticCheckException #2764), and java.lang.UnsupportedOperationException: unsupported operator: if won't be thrown.
  2. Adding "fetch_size" in the query will trigger Cursor operation, but the query with group by as an aggregation statement hasn't supported PaginatedPlan yet. So it fallback to legacy engine which caused different behaviours. Supporting PaginatedPlan with aggregation could be discussed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

3 participants