[BUG] Delete operation doesn't work for string field

[ps48]

What is the bug? Delete operation doesn't work as expected when using a string field in the where clause. It works as expected when using a number field in the where clause.

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

String Field Example

POST _opendistro/_sql 
{ "query": "select * from test-index where message = 'Hello World'" }

(returns matched rows)

POST _opendistro/_sql 
{ "query": "delete from test-index where message = 'Hello World'" }

(deletes 0 rows)

Number Field Example

POST _opendistro/_sql 
{ "query": "select * from test-index2 where number = 1" }

(returns matched rows)

POST _opendistro/_sql 
{ "query": "select * from test-index2 where number = 1" }

(deletes matched rows)

What is the expected behavior? Delete operation should go through irrespective to the field type.

What is your host/environment?

• OS 1.0, OS 2.2

[dai-chen]

@ps48 Thanks for reporting the issue! Could you share your index mapping and sample data if possible?

[ps48]

Index data

POST test-index/_doc/1
{
  "@timestamp": "2099-11-15T13:12:00",
  "message": "Hello World",
  "user": {
    "id": "sql_user"
  }
}

Index mapping

GET test-index/_mapping

Response:

{
  "test-index" : {
    "mappings" : {
      "properties" : {
        "@timestamp" : {
          "type" : "date"
        },
        "message" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "user" : {
          "properties" : {
            "id" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            }
          }
        }
      }
    }
  }
}

[dai-chen]

I think this is because DELETE statement is only supported in our legacy engine. We need to use inner keyword field name (message.keyword) explicitly. This is handled automatically in SQL engine v2.

$ curl -XPOST "localhost:9200/issue-780/_doc/1" -H 'Content-Type: application/json' -d '
{
  "@timestamp": "2099-11-15T13:12:00",
  "message": "Hello World",
  "user": {
    "id": "sql_user"
  }
}
'

$ curl -XPUT "localhost:9200/_cluster/settings" -H 'Content-Type: application/json' -d '
{
        "transient": {"plugins.sql.delete.enabled": true}
}
'
{"acknowledged":true,"persistent":{},"transient":{"plugins":{"sql":{"delete":{"enabled":"true"}}}}}

$ curl -XPOST "localhost:9200/_plugins/_sql" -H 'Content-Type: application/json' -d '
{
  "query": "delete from issue-780 where message.keyword = '\''Hello World'\'' "
}
'
{
  "schema": [{
    "name": "deleted_rows",
    "type": "long"
  }],
  "total": 1,
  "datarows": [[1]],
  "size": 1,
  "status": 200
}

curl -XPOST "localhost:9200/_plugins/_sql" -H 'Content-Type: application/json' -d '
{
  "query": "select * from issue-780 where message = '\''Hello World'\'' "
}
'
{
  "schema": [
    {
      "name": "@timestamp",
      "type": "timestamp"
    },
    {
      "name": "message",
      "type": "text"
    },
    {
      "name": "user",
      "type": "object"
    }
  ],
  "datarows": [],
  "total": 0,
  "size": 0,
  "status": 200
}

[dai-chen]

Closing the issue for now. This would be enhanced as DDL capability added to SQL engine v2 later.

[dai-chen]

Reopened and labeled as legacy so we can fix this when deprecate legacy query engine.