ESQL: Mismatching mapping cannot be worked around with eval (Support for union types)

[craigtaverner]

I have multiple indices with the field host.ip, and most are mapped as type ip, but one is mapped as keyword. In a recent discussion it was mentioned that we could get around this using functions to convert types, however that does not appear to work:

from logs-* | eval host_ip = to_ip(host.ip) | KEEP host_ip, data_stream.dataset | LIMIT 2

Produces the error:

Found 1 problem
line 1:36: Cannot use field [host.ip] due to ambiguities being mapped as [2] incompatible types:
  [keyword] in [.ds-logs-k8-application.log-default-2023.10.10-000001],
  [ip] in [.ds-logs-apache.access-default-2023.10.10-000001, .ds-logs-apache.error-default-2023.10.10-000001, .ds-logs-kafka.log-default-2023.10.10-000001, .ds-logs-mysql.error-default-2023.10.10-000001, .ds-logs-mysql.slowlog-default-2023.10.10-000001, .ds-logs-nginx.access-default-2023.10.10-000001, .ds-logs-nginx.error-default-2023.10.10-000001, .ds-logs-postgresql.log-default-2023.10.10-000001, .ds-logs-redis.log-default-2023.10.10-000001, .ds-logs-redis.slowlog-default-2023.10.10-000001, .ds-logs-system.auth-default-2023.10.10-000001, .ds-logs-system.syslog-default-2023.10.10-000001]

Trying to keep the same name using eval host.ip = to_ip(host.ip) also results in the same error. If I run instead on indices that have only keyword or only ip, then both queries work.

[elasticsearchmachine]

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

[elasticsearchmachine]

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

[craigtaverner]

In discussions it was learned that this is not currently supported and not completely trivial to support either, since the current query planning process assumes a singular type for all fields coming from the indices, and throughout the query. Since querying multiple indices with different field types is in fact a common Elasticsearch use case, we'd like to support this at some point, so we'll label this as an enhancement and re-consider it later.

[elasticsearchmachine]

Pinging @elastic/es-analytics-geo (Team:Analytics)

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)