Cannot query ip address fields using elastic SQL

I am using the Kibana console to run a SQL query with an ip field as criteria. Here is the query:

POST /_sql?format=json 
 {
  "query": """SELECT "@timestamp", "destination.ip" FROM "dummyindex"  WHERE "destination.ip" IN ('127.01.01.01', '128.01.01.01') and "@timestamp" > '2020-09-16' """
}

This is the error:

{
  "error" : {
    "root_cause" : [
      {
        "type" : "verification_exception",
        "reason" : "Found 1 problem\nline 1:63: 1st argument of [\"destination.ip\" IN ('127.01.01.01', '128.01.01.01')] must be [ip], found value ['127.01.01.01'] type [keyword]"
      }
    ],
    "type" : "verification_exception",
    "reason" : "Found 1 problem\nline 1:63: 1st argument of [\"destination.ip\" IN ('127.01.01.01', '128.01.01.01')] must be [ip], found value ['127.01.01.01'] type [keyword]"
  },
  "status" : 400
}

Is there a special syntax to query ip fields using SQL?

Is this error not saying the IP is not whitelisted?

No, it is looking at the field as a keyword instead of an ip data type and the query does not run

Yeah looks that way and I just ran into the same problem.

I borrowed the idea from https://github.com/elastic/elasticsearch/pull/34758/files/7b43b24fa1dc3c6e9e2f843281cd6c9aa76b8cf9#diff-f9d17e274b4800d6b8f0fa433d1bce17R83 and it is now working for me in this form.

POST _sql
{
  "query":"select count(clientip) from  kibana_sample_data_logs where clientip BETWEEN '129.40.10.1' AND '130.49.143.213'"
}

Thank you for your answer. THe issue seems to be with the IN clause. The BETWEEN clause works fine. If you try your query like this:

select count(clientip) from  kibana_sample_data_logs where clientip IN ( '129.40.10.1' , '130.49.143.213')

I bet you are going to get the same error. This seems to be a bug in elastic's implementation of SQL using ip addresses.

This might be improved, to have the values in the IN set converted to the type of the attribute, but you could also achieve that already by converting them explicitly: ...where clientip IN ( '129.40.10.1'::IP , '130.49.143.213'::ip)

Thank you very much. That worked.