Inconsistent behavior when querying host.ip field: ORDER BY ASC and WHERE IS NOT NULL fail with "Arrays not supported"

wwxb · August 21, 2025, 5:23am

Hello Elasticsearch team,

I encountered an unexpected issue when querying a field in my index using the SQL API.

Index: xuji_test
Mapping excerpt (simplified):

POST /_sql?format=json
{
  "query": """
  DESCRIBE xuji_test
  """
}

Response:

{
  "columns": [
    { "name": "column", "type": "keyword" },
    { "name": "type", "type": "keyword" },
    { "name": "mapping", "type": "keyword" }
  ],
  "rows": [
    ["host.ip", "VARCHAR", "text"]
  ]
}

Environment

GET /_nodes/os,jvm
{
  "_nodes" : {
    "total" : 1,
    "successful" : 1,
    "failed" : 0
  },
  "cluster_name" : "elastic7.16-one",
  "nodes" : {
    "o--iaTabTkKNW7Q69xOStQ" : {
      "name" : "node-10",
      "transport_address" : "10.241.60.19:9300",
      "host" : "10.241.60.19",
      "ip" : "10.241.60.19",
      "version" : "7.16.2",
      "build_flavor" : "default",
      "build_type" : "tar",
      "build_hash" : "2b937c44140b6559905130a8650c64dbd0879cfb",
      "roles" : [
        "data",
        "data_cold",
        "data_content",
        "data_frozen",
        "data_hot",
        "data_warm",
        "ingest",
        "master",
        "ml",
        "remote_cluster_client",
        "transform"
      ],
      "attributes" : {
        "ml.machine_memory" : "16655716352",
        "xpack.installed" : "true",
        "transform.node" : "true",
        "ml.max_open_jobs" : "512",
        "ml.max_jvm_size" : "6372720640"
      },
      "os" : {
        "refresh_interval_in_millis" : 1000,
        "name" : "Linux",
        "pretty_name" : "CentOS Linux 7 (Core)",
        "arch" : "amd64",
        "version" : "3.10.0-1160.119.1.el7.x86_64",
        "available_processors" : 8,
        "allocated_processors" : 8
      },
      "jvm" : {
        "pid" : 3397,
        "version" : "11.0.16",
        "vm_name" : "OpenJDK 64-Bit Server VM",
        "vm_version" : "11.0.16+8",
        "vm_vendor" : "Eclipse Adoptium",
        "bundled_jdk" : true,
        "using_bundled_jdk" : false,
        "start_time_in_millis" : 1739933359507,
        "mem" : {
          "heap_init_in_bytes" : 6442450944,
          "heap_max_in_bytes" : 6372720640,
          "non_heap_init_in_bytes" : 7667712,
          "non_heap_max_in_bytes" : 0,
          "direct_max_in_bytes" : 0
        },
        "gc_collectors" : [
          "ParNew",
          "ConcurrentMarkSweep"
        ],
        "memory_pools" : [
          "CodeHeap 'non-nmethods'",
          "Metaspace",
          "CodeHeap 'profiled nmethods'",
          "Compressed Class Space",
          "Par Eden Space",
          "Par Survivor Space",
          "CodeHeap 'non-profiled nmethods'",
          "CMS Old Gen"
        ],
        "using_compressed_ordinary_object_pointers" : "true",
        "input_arguments" : [
          "-Xshare:auto",
          "-Des.networkaddress.cache.ttl=60",
          "-Des.networkaddress.cache.negative.ttl=10",
          "-XX:+AlwaysPreTouch",
          "-Xss1m",
          "-Djava.awt.headless=true",
          "-Dfile.encoding=UTF-8",
          "-Djna.nosys=true",
          "-XX:-OmitStackTraceInFastThrow",
          "-Dio.netty.noUnsafe=true",
          "-Dio.netty.noKeySetOptimization=true",
          "-Dio.netty.recycler.maxCapacityPerThread=0",
          "-Dio.netty.allocator.numDirectArenas=0",
          "-Dlog4j.shutdownHookEnabled=false",
          "-Dlog4j2.disable.jmx=true",
          "-Dlog4j2.formatMsgNoLookups=true",
          "-Djava.locale.providers=SPI,COMPAT",
          "--add-opens=java.base/java.io=ALL-UNNAMED",
          "-Xms6g",
          "-Xmx6g",
          "-XX:+UseConcMarkSweepGC",
          "-XX:CMSInitiatingOccupancyFraction=75",
          "-XX:+UseCMSInitiatingOccupancyOnly",
          "-Djava.io.tmpdir=/tmp/elasticsearch-12660063856807810236",
          "-XX:+HeapDumpOnOutOfMemoryError",
          "-XX:+ExitOnOutOfMemoryError",
          "-XX:HeapDumpPath=data",
          "-XX:ErrorFile=logs/hs_err_pid%p.log",
          "-Xlog:gc*,gc+age=trace,safepoint:file=logs/gc.log:utctime,pid,tags:filecount=32,filesize=64m",
          "-XX:MaxDirectMemorySize=3221225472",
          "-Des.path.home=/data/br/base/elasticsearch-7.16.2",
          "-Des.path.conf=/data/br/base/elasticsearch-7.16.2/config",
          "-Des.distribution.flavor=default",
          "-Des.distribution.type=tar",
          "-Des.bundled_jdk=true"
        ]
      }
    }
  }
}

Queries and Results

The following query works as expected:

POST /_sql?format=json
{
  "query": """
  SELECT "host.ip" AS "ip" FROM "xuji_test" ORDER BY "ip" DESC
  """
}

However, changing the order direction to ASC fails:

POST /_sql?format=json
{
  "query": """
  SELECT "host.ip" AS "ip" FROM "xuji_test" ORDER BY "ip" ASC
  """
}

Error:

{
  "error": {
    "root_cause": [
      {
        "type": "ql_illegal_argument_exception",
        "reason": "Arrays (returned by [host.ip]) are not supported"
      }
    ],
    "type": "ql_illegal_argument_exception",
    "reason": "Arrays (returned by [host.ip]) are not supported"
  },
  "status": 500
}

Similarly, a simple WHERE filter also fails:

POST /_sql?format=json
{
  "query": """
  SELECT "host.ip" AS "ip" FROM "xuji_test" WHERE "ip" IS NOT NULL
  """
}

Returns the same error about arrays.

Expected Behavior

Since ORDER BY "ip" DESC works, ORDER BY "ip" ASC should also work consistently.
Filtering with WHERE "ip" IS NOT NULL should not fail with an "Arrays not supported" error.

Question

Is this a bug in the SQL engine’s handling of multi-valued fields, or is there a limitation in how host.ip is mapped (text/VARCHAR)? If it’s expected, could you clarify why DESC works while ASC and WHERE fail?

Thanks!

Tortoise · August 21, 2025, 6:38am

Hello @wwxb

Welcome to the community!!
Could you please share the ELK version ?

It is working on version 8.18.5 :

POST /_sql?format=json
{
  "query": """
  SELECT "ip" FROM "kibana_sample_data_logs" ORDER BY "ip" ASC
  """
}

Just for better visibility took format=tsv 

ip
0.72.176.46
0.72.176.46
0.207.229.147
0.207.229.147

WHERE clause is also working :

POST /_sql?format=tsv
{
  "query": """
  SELECT host FROM kibana_sample_data_logs WHERE ip is not null
  """
}

host
www.elastic.co
www.elastic.co
artifacts.elastic.co
artifacts.elastic.co

Thanks!!

wwxb · August 21, 2025, 7:23am

Thank you for your reply. I have already specified the environmental information in the post.

GET /_nodes/os,jvm
{
  "_nodes" : {
    "total" : 1,
    "successful" : 1,
    "failed" : 0
  },
  "cluster_name" : "elastic7.16-one",
  "nodes" : {
    "o--iaTabTkKNW7Q69xOStQ" : {
      "name" : "node-10",
      "transport_address" : "10.241.60.19:9300",
      "host" : "10.241.60.19",
      "ip" : "10.241.60.19",
      "version" : "7.16.2",
      "build_flavor" : "default",
      "build_type" : "tar",
      "build_hash" : "2b937c44140b6559905130a8650c64dbd0879cfb",
      "roles" : [
        "data",
        "data_cold",
        "data_content",
        "data_frozen",
        "data_hot",
        "data_warm",
        "ingest",
        "master",
        "ml",
        "remote_cluster_client",
        "transform"
      ],
      "attributes" : {
        "ml.machine_memory" : "16655716352",
        "xpack.installed" : "true",
        "transform.node" : "true",
        "ml.max_open_jobs" : "512",
        "ml.max_jvm_size" : "6372720640"
      },
      "os" : {
        "refresh_interval_in_millis" : 1000,
        "name" : "Linux",
        "pretty_name" : "CentOS Linux 7 (Core)",
        "arch" : "amd64",
        "version" : "3.10.0-1160.119.1.el7.x86_64",
        "available_processors" : 8,
        "allocated_processors" : 8
      },
      "jvm" : {
        "pid" : 3397,
        "version" : "11.0.16",
        "vm_name" : "OpenJDK 64-Bit Server VM",
        "vm_version" : "11.0.16+8",
        "vm_vendor" : "Eclipse Adoptium",
        "bundled_jdk" : true,
        "using_bundled_jdk" : false,
        "start_time_in_millis" : 1739933359507,
        "mem" : {
          "heap_init_in_bytes" : 6442450944,
          "heap_max_in_bytes" : 6372720640,
          "non_heap_init_in_bytes" : 7667712,
          "non_heap_max_in_bytes" : 0,
          "direct_max_in_bytes" : 0
        },
        "gc_collectors" : [
          "ParNew",
          "ConcurrentMarkSweep"
        ],
        "memory_pools" : [
          "CodeHeap 'non-nmethods'",
          "Metaspace",
          "CodeHeap 'profiled nmethods'",
          "Compressed Class Space",
          "Par Eden Space",
          "Par Survivor Space",
          "CodeHeap 'non-profiled nmethods'",
          "CMS Old Gen"
        ],
        "using_compressed_ordinary_object_pointers" : "true",
        "input_arguments" : [
          "-Xshare:auto",
          "-Des.networkaddress.cache.ttl=60",
          "-Des.networkaddress.cache.negative.ttl=10",
          "-XX:+AlwaysPreTouch",
          "-Xss1m",
          "-Djava.awt.headless=true",
          "-Dfile.encoding=UTF-8",
          "-Djna.nosys=true",
          "-XX:-OmitStackTraceInFastThrow",
          "-Dio.netty.noUnsafe=true",
          "-Dio.netty.noKeySetOptimization=true",
          "-Dio.netty.recycler.maxCapacityPerThread=0",
          "-Dio.netty.allocator.numDirectArenas=0",
          "-Dlog4j.shutdownHookEnabled=false",
          "-Dlog4j2.disable.jmx=true",
          "-Dlog4j2.formatMsgNoLookups=true",
          "-Djava.locale.providers=SPI,COMPAT",
          "--add-opens=java.base/java.io=ALL-UNNAMED",
          "-Xms6g",
          "-Xmx6g",
          "-XX:+UseConcMarkSweepGC",
          "-XX:CMSInitiatingOccupancyFraction=75",
          "-XX:+UseCMSInitiatingOccupancyOnly",
          "-Djava.io.tmpdir=/tmp/elasticsearch-12660063856807810236",
          "-XX:+HeapDumpOnOutOfMemoryError",
          "-XX:+ExitOnOutOfMemoryError",
          "-XX:HeapDumpPath=data",
          "-XX:ErrorFile=logs/hs_err_pid%p.log",
          "-Xlog:gc*,gc+age=trace,safepoint:file=logs/gc.log:utctime,pid,tags:filecount=32,filesize=64m",
          "-XX:MaxDirectMemorySize=3221225472",
          "-Des.path.home=/data/br/base/elasticsearch-7.16.2",
          "-Des.path.conf=/data/br/base/elasticsearch-7.16.2/config",
          "-Des.distribution.flavor=default",
          "-Des.distribution.type=tar",
          "-Des.bundled_jdk=true"
        ]
      }
    }
  }
}

Tortoise · August 21, 2025, 7:39am

Okay the issue you are facing is on version 7.16.2

RainTown · August 21, 2025, 9:55am

7.16.2 (released in 2021) is also running on CentOS 7, minor releases 7.x from 2014-2020, and is now EOL.

wwxb · August 22, 2025, 3:16am

I just want to confirm whether it is a bug or a usage issue.

Topic		Replies	Views
Getting error while fetching Array field using elasticsearch-sql-cli Elasticsearch elastic-stack-sql	3	1448	February 12, 2021
Dealing with Arrays and IP address fields in elasticsearch SQL Elasticsearch	3	2173	August 28, 2018
Did I stumble across another bug? SQL select ordering on 2 fields Elasticsearch elastic-stack-sql	2	455	January 16, 2020
Cannot query ip address fields using elastic SQL Kibana	8	1070	November 2, 2020
Result from ES\|QL differs from result of regular search Elasticsearch esql	8	52	August 19, 2025

Inconsistent behavior when querying host.ip field: ORDER BY ASC and WHERE IS NOT NULL fail with "Arrays not supported"

Environment

Queries and Results

Expected Behavior

Question

Related topics