ES|QL does not consistently handle field names starting with a number

[VimCommando]

Elasticsearch Version

8.11.0-SNAPSHOT

Installed Plugins

No response

Java Version

bundled

OS Version

Darwin MacBook-Pro.local 22.6.0 Darwin Kernel Version 22.6.0: Fri Sep 15 13:41:28 PDT 2023; root:xnu-8796.141.3.700.8~1/RELEASE_ARM64_T6000 arm64

Problem Description

If a field name starts with a leading digit, ES|QL commands sometimes accept it and other times error on it.

I expect the field name to be handled consistently between all commands.

For a real-world example, take Elasticsearch's own stack monitoring:

node_stats.os.cpu.load_average.1m
elasticsearch.node.stats.os.cpu.load_avg.1m

The child field 1m is what causes errors.

Steps to Reproduce

Take a mapping that uses fields with leading numbers. Elasticsearch's stack monitoring has *.load_average.1m as a good starting point.

SORT

Use a base query to see the data does exist, and ES|QL can retrieve it:

from .monitoring-es-8-*
| where metricset.name == "node_stats"
| keep source_node.name, elasticsearch.node.stats.os.cpu.load_avg.1m

Now add a sort clause, and see it error on the .1 portion of the field name:

from .monitoring-es-8-*
| where metricset.name == "node_stats"
| keep source_node.name, elasticsearch.node.stats.os.cpu.load_avg.1m
| sort elasticsearch.node.stats.os.cpu.load_avg.1m

STATS ... BY

Use stats on node_stats.process.cpu.percent and it works:

from .monitoring-es-8-*
| where metricset.name == "node_stats"
| keep source_node.name, node_stats.process.cpu.percent
| stats avg_cpu = avg(node_stats.process.cpu.percent) by source_node.name

Use stats on elasticsearch.node.stats.os.cpu.load_avg.1m and it fails:

from .monitoring-es-8-*
| where metricset.name == "node_stats"
| keep source_node.name, elasticsearch.node.stats.os.cpu.load_avg.1m
| stats avg_cpu = avg(elasticsearch.node.stats.os.cpu.load_avg.1m) by source_node.name

RENAME

If you first rename the field, sort and stats work:

from .monitoring-es-8-*
| where metricset.name == "node_stats"
| keep source_node.name, elasticsearch.node.stats.os.cpu.load_avg.1m
| rename elasticsearch.node.stats.os.cpu.load_avg.1m as load_average
| stats max_load_avg = max(load_average) by source_node.name
| sort max_load_avg desc

Autocomplete

The field also breaks autocomplete. If you use autocomplete before the numerical field it works:

But after the field, it fails:

Logs (if relevant)

Logs for ailure on sort:

2023-10-04 16:47:13 {"@timestamp":"2023-10-05T02:47:13.482Z", "log.level":"DEBUG", "message":"Parsing as statement: from .monitoring-es-8-*\n| where metricset.name == \"node_stats\"\n| keep source_node.name, elasticsearch.node.stats.os.cpu.load_avg.1m\n| sort elasticsearch.node.stats.os.cpu.load_avg.1m", "ecs.version": "1.2.0","service.name":"ES_ECS","event.dataset":"elasticsearch.server","process.thread.name":"elasticsearch[esql-data][esql][T#5]","log.logger":"org.elasticsearch.xpack.esql.parser.EsqlParser","trace.id":"8ce5c7c44f33d01f60397e2626bbc588","elasticsearch.cluster.uuid":"Z_D3SfJYRfKxdyJ8sQnLPA","elasticsearch.node.id":"7P6LpFHCQXa83mrNEW1X4Q","elasticsearch.node.name":"esql-data","elasticsearch.cluster.name":"esql"}
2023-10-04 16:47:13 {"@timestamp":"2023-10-05T02:47:13.483Z", "log.level": "INFO", "message":"Failed execution of ESQL query.\nQuery string: [from .monitoring-es-8-*\n| where metricset.name == \"node_stats\"\n| keep source_node.name, elasticsearch.node.stats.os.cpu.load_avg.1m\n| sort elasticsearch.node.stats.os.cpu.load_avg.1m]\nExecution time: [1]ms", "ecs.version": "1.2.0","service.name":"ES_ECS","event.dataset":"elasticsearch.server","process.thread.name":"elasticsearch[esql-data][esql][T#5]","log.logger":"org.elasticsearch.xpack.esql.action.EsqlResponseListener","trace.id":"8ce5c7c44f33d01f60397e2626bbc588","elasticsearch.cluster.uuid":"Z_D3SfJYRfKxdyJ8sQnLPA","elasticsearch.node.id":"7P6LpFHCQXa83mrNEW1X4Q","elasticsearch.node.name":"esql-data","elasticsearch.cluster.name":"esql"}

Logs for failure on stats ... by:

2023-10-04 16:49:08 {"@timestamp":"2023-10-05T02:49:08.287Z", "log.level":"DEBUG", "message":"ESQL query:\nfrom .monitoring-es-8-*\n| where metricset.name == \"node_stats\"\n| keep source_node.name, elasticsearch.node.stats.os.cpu.load_avg.1m\n| stats elasticsearch.node.stats.os.cpu.load_avg.1m by source_node.name", "ecs.version": "1.2.0","service.name":"ES_ECS","event.dataset":"elasticsearch.server","process.thread.name":"elasticsearch[esql-data][esql][T#4]","log.logger":"org.elasticsearch.xpack.esql.session.EsqlSession","trace.id":"9281898aba91c07f83cefe03ed0f8e74","elasticsearch.cluster.uuid":"Z_D3SfJYRfKxdyJ8sQnLPA","elasticsearch.node.id":"7P6LpFHCQXa83mrNEW1X4Q","elasticsearch.node.name":"esql-data","elasticsearch.cluster.name":"esql"}
2023-10-04 16:49:08 {"@timestamp":"2023-10-05T02:49:08.287Z", "log.level":"DEBUG", "message":"Parsing as statement: from .monitoring-es-8-*\n| where metricset.name == \"node_stats\"\n| keep source_node.name, elasticsearch.node.stats.os.cpu.load_avg.1m\n| stats elasticsearch.node.stats.os.cpu.load_avg.1m by source_node.name", "ecs.version": "1.2.0","service.name":"ES_ECS","event.dataset":"elasticsearch.server","process.thread.name":"elasticsearch[esql-data][esql][T#4]","log.logger":"org.elasticsearch.xpack.esql.parser.EsqlParser","trace.id":"9281898aba91c07f83cefe03ed0f8e74","elasticsearch.cluster.uuid":"Z_D3SfJYRfKxdyJ8sQnLPA","elasticsearch.node.id":"7P6LpFHCQXa83mrNEW1X4Q","elasticsearch.node.name":"esql-data","elasticsearch.cluster.name":"esql"}
2023-10-04 16:49:08 {"@timestamp":"2023-10-05T02:49:08.287Z", "log.level": "INFO", "message":"Beginning execution of ESQL query.\nQuery string: [from .monitoring-es-8-*\n| where metricset.name == \"node_stats\"\n| keep source_node.name, elasticsearch.node.stats.os.cpu.load_avg.1m\n| stats elasticsearch.node.stats.os.cpu.load_avg.1m by source_node.name]", "ecs.version": "1.2.0","service.name":"ES_ECS","event.dataset":"elasticsearch.server","process.thread.name":"elasticsearch[esql-data][transport_worker][T#2]","log.logger":"org.elasticsearch.xpack.esql.action.RestEsqlQueryAction","trace.id":"9281898aba91c07f83cefe03ed0f8e74","elasticsearch.cluster.uuid":"Z_D3SfJYRfKxdyJ8sQnLPA","elasticsearch.node.id":"7P6LpFHCQXa83mrNEW1X4Q","elasticsearch.node.name":"esql-data","elasticsearch.cluster.name":"esql"}
2023-10-04 16:49:08 {"@timestamp":"2023-10-05T02:49:08.287Z", "log.level":"DEBUG", "message":"ESQL query:\nfrom .monitoring-es-8-*\n| where metricset.name == \"node_stats\"\n| keep source_node.name, elasticsearch.node.stats.os.cpu.load_avg.1m\n| stats elasticsearch.node.stats.os.cpu.load_avg.1m by source_node.name", "ecs.version": "1.2.0","service.name":"ES_ECS","event.dataset":"elasticsearch.server","process.thread.name":"elasticsearch[esql-data][esql][T#3]","log.logger":"org.elasticsearch.xpack.esql.session.EsqlSession","trace.id":"9281898aba91c07f83cefe03ed0f8e74","elasticsearch.cluster.uuid":"Z_D3SfJYRfKxdyJ8sQnLPA","elasticsearch.node.id":"7P6LpFHCQXa83mrNEW1X4Q","elasticsearch.node.name":"esql-data","elasticsearch.cluster.name":"esql"}
2023-10-04 16:49:08 {"@timestamp":"2023-10-05T02:49:08.288Z", "log.level":"DEBUG", "message":"Parsing as statement: from .monitoring-es-8-*\n| where metricset.name == \"node_stats\"\n| keep source_node.name, elasticsearch.node.stats.os.cpu.load_avg.1m\n| stats elasticsearch.node.stats.os.cpu.load_avg.1m by source_node.name", "ecs.version": "1.2.0","service.name":"ES_ECS","event.dataset":"elasticsearch.server","process.thread.name":"elasticsearch[esql-data][esql][T#3]","log.logger":"org.elasticsearch.xpack.esql.parser.EsqlParser","trace.id":"9281898aba91c07f83cefe03ed0f8e74","elasticsearch.cluster.uuid":"Z_D3SfJYRfKxdyJ8sQnLPA","elasticsearch.node.id":"7P6LpFHCQXa83mrNEW1X4Q","elasticsearch.node.name":"esql-data","elasticsearch.cluster.name":"esql"}
2023-10-04 16:49:08 {"@timestamp":"2023-10-05T02:49:08.288Z", "log.level": "INFO", "message":"Failed execution of ESQL query.\nQuery string: [from .monitoring-es-8-*\n| where metricset.name == \"node_stats\"\n| keep source_node.name, elasticsearch.node.stats.os.cpu.load_avg.1m\n| stats elasticsearch.node.stats.os.cpu.load_avg.1m by source_node.name]\nExecution time: [1]ms", "ecs.version": "1.2.0","service.name":"ES_ECS","event.dataset":"elasticsearch.server","process.thread.name":"elasticsearch[esql-data][esql][T#4]","log.logger":"org.elasticsearch.xpack.esql.action.EsqlResponseListener","trace.id":"9281898aba91c07f83cefe03ed0f8e74","elasticsearch.cluster.uuid":"Z_D3SfJYRfKxdyJ8sQnLPA","elasticsearch.node.id":"7P6LpFHCQXa83mrNEW1X4Q","elasticsearch.node.name":"esql-data","elasticsearch.cluster.name":"esql"}
2023-10-04 16:49:08 {"@timestamp":"2023-10-05T02:49:08.289Z", "log.level": "INFO", "message":"Failed execution of ESQL query.\nQuery string: [from .monitoring-es-8-*\n| where metricset.name == \"node_stats\"\n| keep source_node.name, elasticsearch.node.stats.os.cpu.load_avg.1m\n| stats elasticsearch.node.stats.os.cpu.load_avg.1m by source_node.name]\nExecution time: [1]ms", "ecs.version": "1.2.0","service.name":"ES_ECS","event.dataset":"elasticsearch.server","process.thread.name":"elasticsearch[esql-data][esql][T#3]","log.logger":"org.elasticsearch.xpack.esql.action.EsqlResponseListener","trace.id":"9281898aba91c07f83cefe03ed0f8e74","elasticsearch.cluster.uuid":"Z_D3SfJYRfKxdyJ8sQnLPA","elasticsearch.node.id":"7P6LpFHCQXa83mrNEW1X4Q","elasticsearch.node.name":"esql-data","elasticsearch.cluster.name":"esql"}

[elasticsearchmachine]

Pinging @elastic/es-ql (Team:QL)

[elasticsearchmachine]

Pinging @elastic/elasticsearch-esql (:Query Languages/ES|QL)