IP field types are currently not supported.

[HernanMora]

Description

It is not possible to make queries where an IP type field is selected

Elasticsearch Version: 6.8.3
SQL Plugin version: 0.10.0.0

Request

POST _opendistro/_sql?format=jdbc
{
  "query": "SELECT host_ip, hostname FROM devices* WHERE host_ip = TERM('10.11.23.0/24') LIMIT 5"
}

Response

{
  "error": {
    "reason": "IP field types are currently not supported.",
    "details": "java.lang.IllegalArgumentException: IP field types are currently not supported.",
    "type": "IllegalArgumentException"
  },
  "status": 500
}

Is there any way to make queries where these types of fields are selected?
Regards!

[abbashus]

Did a small test to check the missing functionality.

PUT network

PUT network/_mapping?pretty
{
  "properties": {
    "host_ip": {
      "type": "ip"
    },
    "hostname": {
      "type": "text",
      "fields": {
        "keyword": {
          "type": "keyword"
        }
      }
    }
  }
}

PUT /network/_doc/1?pretty
{
  "host_ip": "192.168.1.1",
  "hostname" : "www.opendistro.com"
}

Explain seems fine:

POST _opendistro/_sql/_explain?
{
  "query": """
  SELECT host_ip, hostname 
  FROM network
  WHERE host_ip = TERM("192.168.0.0/16") LIMIT 5
  """
}

{
  "from" : 0,
  "size" : 5,
  "query" : {
    "bool" : {
      "filter" : [
        {
          "bool" : {
            "must" : [
              {
                "term" : {
                  "host_ip" : {
                    "value" : "192.168.0.0/16",
                    "boost" : 1.0
                  }
                }
              }
            ],
            "adjust_pure_negative" : true,
            "boost" : 1.0
          }
        }
      ],
      "adjust_pure_negative" : true,
      "boost" : 1.0
    }
  },
  "_source" : {
    "includes" : [
      "host_ip",
      "hostname"
    ],
    "excludes" : [ ]
  }
}

Query without jdbc format

POST _opendistro/_sql/?
{
  "query": """
  SELECT host_ip, hostname 
  FROM network
  WHERE host_ip = TERM("192.168.0.0/16") LIMIT 5
  """
}

{
  "took" : 4,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 0.0,
    "hits" : [
      {
        "_index" : "network",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 0.0,
        "_source" : {
          "host_ip" : "192.168.1.1",
          "hostname" : "www.opendistro.com"
        }
      }
    ]
  }
}

Query with jdbc format

POST _opendistro/_sql/?format=jdbc
{
  "query": """
  SELECT host_ip, hostname 
  FROM network
  WHERE host_ip = TERM("192.168.0.0/16") LIMIT 5
  """
}

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "IP fieldName types are currently not supported.",
    "type": "IllegalArgumentException"
  },
  "status": 500
}

There is no error from Elasticsearch, this is a limitation from JDBC formatter.
@HernanMora Thanks for reporting, we will evaluate this and come with a fix.

[chloe-zh]

@HernanMora This issue is fixed now. Please try the latest version of plugin built from the source code in master branch. Thanks!

[HernanMora]

Thank you!

The JDBC driver works correctly =)

For the SQL plugin I had to apply the commit from the master branch to opendistro-0.10

git cherry-pick -n a332808455b1e3e0230874f4595357e9fd7a238f

I also had to apply the following commit to query in multiple indexes

git cherry-pick -n f23f30b296282469d53122bf6c57aa9c0d256e5d

Is it possible to apply all the fixes after version 0.10.0.0 to get the maximum functionality of the plugin for Elasticsearch 6.8.3 ?

That would be great =)

[chloe-zh]

@HernanMora Sorry about the inconvenience it might have brought to you, but we did critical compatibility check for every released version, so the new fixes might not be compatible with the old versions. We suggest you get the latest version of ES and our plugin source code and that would work well.