SQL Plugin Issue SQLNumberExpr cannot be cast to SQLIdentifierExpr

Plugins SQL
1

We have an issue with the SQL Plugin. The plugin worked in a single server cluster, but in a multiple node cluster it did not.

POST _opendistro/_sql?
{
“query”: “SELECT * FROM myindex LIMIT 50”
}

{
“error”: {
“reason”: “com.alibaba.druid.sql.ast.expr.SQLNumberExpr cannot be cast to com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr”,
“details”: “java.lang.ClassCastException: com.alibaba.druid.sql.ast.expr.SQLNumberExpr cannot be cast to com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr”,
“type”: “ClassCastException”
},
“status”: 503
}

2

I tried running the same query on kibana_sample_data_flights index that comes with Kibana and it works fine.

POST _opendistro/_sql/?
{
    "query": """
    SELECT  * FROM kibana_sample_data_flights LIMIT 50
    """
}

---- result ----
{
  "took" : 5,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 10000,
      "relation" : "gte"
    },
    "max_score" : 1.0,
    "hits" : [
...
...
...
]

Explain:

POST _opendistro/_sql/_explain?
{
    "query": """
    SELECT  * FROM kibana_sample_data_flights LIMIT 50
    """
}

---- result ----
{
  "from" : 0,
  "size" : 50
}

Can you share the Opendistro SQL plugin it was run against.

3

Hello,

I have a similar issue, I am running opendistro 1.6.0.0 with ES 7.6.1 and I get this error when query some indices generate by an internal tool:

{
“error”: {
“reason”: “There was internal problem at backend”,
“details”: “class com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr cannot be cast to class com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr (com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr and com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr are in unnamed module of loader java.net.FactoryURLClassLoader @113f9078)”,
“type”: “ClassCastException”
},
“status”: 503
}

The error occured on SELECT * or SELECT a specific field.

But I can’t reproduce the error on Kibana indices or by creating a simple index myself. I can query the index using ES Json and I can’t find what is wrong.

Does anyone have an idea?

4

Hi,

I finally found the root cause of my issue. Indices was not healthy, number of shards for indices was not corresponding to the number of ES nodes. After cleaning and reconfiguring index creation, the problem disappear.

I hope it can help someone.

5

Upgrades solved the issues