SQL plugin not returning correct count for attribute is NULL

Plugins SQL
,
1

Versions (relevant - OpenSearch/Dashboard/Server OS/Browser):2.19.4 and 2.18.0

Describe the issue: SQL plugin does not return correct data if below query is executed

POST: _plugins/_sql

{ “query”:"SELECT COUNT(*) AS COUNT FROM `index_name` WHERE `containerName`= ‘Mobiles’ AND (`attributes.Phone Spec.URL` IS NULL) "}

Response:

{
“error”: {
“reason”: “Invalid SQL query”,
“details”: “can’t resolve Symbol(namespace=FIELD_NAME, name=attributes.Phone Spec.URL) in type env”,
“type”: “SemanticCheckException”
},
“status”: 400
}

Configuration: below JSON data is indexed in opensearch

{
“_index”: “42”,
“_id”: “122233”,
“_score”: 8.534866,
“_source”: {
“type”: “ITEM”,
“companyName”: “Datanew”,
“containerName”: “Mobiles”,
“primaryKey”: “item3”,
“displayName”: “item3”,
“attributes”: {
“Phone Spec”: {
“URL”: null,
“Accessories”: null,

        }
      },
      "mappings": {

      },
      "dataCompletenessPercent": [],
      "validationErrorCount": 0,
      "versionNumber": 1
    }

}

Relevant Logs or Screenshots: We can search items is indexed using API /_search?q=*

2

@bimlesh_singh It seems to be related to your Phone Spec field name. I suggest avoiding white spaces in the field names and using snake notation, i.e. Phone_Spec or camel, i.e. PhoneSpec

3

@bimlesh_singh I’ve tested your query, and I think I might have a solution/workaround for you.
When you ingest a first document, and fields have a value of null, then no mapping is created for that field.

POST index1/_doc
{
  "First Value": null,
  "Second_Value": 2
}

GET index1/_mappings

output:

{
  "index1": {
    "mappings": {
      "properties": {
        "Second_Value": {
          "type": "long"
        }
      }
    }
  }
}

Then the below query will fail

POST _plugins/_sql
{
  "query": "SELECT * FROM `index1` where `First Value` IS NULL"
}

output:

"{\n  \"error\": {\n    \"reason\": \"Invalid SQL query\",\n    \"details\": \"can't resolve Symbol(namespace=FIELD_NAME, name=First Value) in type env\",\n    \"type\": \"SemanticCheckException\"\n  },\n  \"status\": 400\n}"

Also, this will return only fields with mappings.

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

output:

{
  "schema": [
    {
      "name": "Second_Value",
      "type": "long"
    }
  ],
  "datarows": [
    [
      2
    ]
  ],
  "total": 1,
  "size": 1,
  "status": 200
}

Adding mapping for the missing field will allow running queries successfully.

PUT index1/_mapping
{
    "properties": {
      "First Value": {
        "type": "float"
      }
  }
}

POST _plugins/_sql
{
  "query": "SELECT * FROM `index1` where `First Value` IS NULL"
}

output:

{
  "schema": [
    {
      "name": "First Value",
      "type": "float"
    },
    {
      "name": "Second_Value",
      "type": "long"
    }
  ],
  "datarows": [
    [
      null,
      2
    ]
  ],
  "total": 1,
  "size": 1,
  "status": 200
}
4

Thanks Pablo for quick response!

We don’t have any static mappings as our JSON document is very dynamic as fields and its data types are not known initially .Our mapping looks like below.

{
    "41": {
        "mappings": {
            "properties": {
                "attributes": {
                    "properties": {
                        "011094_Hierarchy_spec": {
                            "properties": {
                                "Apple": {
                                    "type": "object"
                                },
                                "Grouping": {
                                    "type": "object"
                                },
                                "ID": {
                                    "type": "float"
                                }
                            }
                        },
                        "01_AG_Cat_Spec": {
                            "properties": {
                                "Grp": {
                                    "type": "object"
                                },
                                "Id": {
                                    "type": "text",
                                    "fields": {
                                        "keyword": {
                                            "type": "keyword",
                                            "ignore_above": 256
                                        }
                                    }
                                },
                                "Name": {
                                    "type": "text",
                                    "fields": {
                                        "keyword": {
                                            "type": "keyword",
                                            "ignore_above": 256
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

Can we use any API to know the mapping of JAVA datatype to Opnesaerch data type mapping to update/add specific mapping for new attributes are getting added or deleted?

5

I’m not aware of such API.

6

Thanks np

7

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.