[ES|QL] "Quick Fix" Autocomplete for Field Type Conflicts

[ninoslavmiskovic]

Describe the feature:

Users often encounter errors when querying across multiple indices that contain fields with the same name but different types. This commonly happens with fields like message, which might be a keyword in some indices and text in others. The current behavior results in a verification_exception error from Elasticsearch. This feature request aims to provide a "quick fix" in the autocomplete functionality of the query builder to resolve such conflicts.

Example of a query:

from logs-*
| keep message, @timestamp

Example of error:

[esql] > Unexpected error from Elasticsearch: verification_exception - Found 1 problem line 2:8: Cannot use field [message] due to ambiguities being mapped as [2] incompatible types: [keyword] in [logs-aws_s3, logs-custom_multiplex, logs-k8_container], [text] in [.ds-logs-elastic_agent-default-2024.06.04-000001, .ds-logs-elastic_agent.filebeat-default-2024.06.04-000001, .ds-logs-elastic_agent.metricbeat-default-2024.06.04-000001, .ds-logs-enterprise_search.api-default-2024.06.04-000001, .ds-logs-enterprise_search.audit-default-2024.06.04-000001, logs-apache_access, logs-apache_error, logs-docker_container, logs-nginx_application_logs, logs-nginx_error, logs-system_error, logs-windows]

Proposed Solution:

1. Automatic Detection and Notification:

• Detect when a query involves fields with the same name but different types across indices.
• Inform users of the conflict and suggest potential resolutions.

2. Quick Fix Options:

• Provide an option to automatically append a conversion function to the query.

Example:

TO_KEYWORD(message) or TO_TEXT(message)

• Suggest the use of EVAL to convert fields before usage.

Example:

EVAL message = TO_KEYWORD(message)

3. UI/UX Enhancements:

• Introduce a prompt in the query builder UI when such a conflict is detected.
• Provide guided steps or a "quick fix" button to apply the necessary conversion.
• Optionally, allow users to save these quick fixes as templates for future queries.

Example of Fixed Query:

FROM logs-* METADATA _index
| EVAL message = TO_TEXT(message)
| KEEP _index, @timestamp, message
| SORT _index ASC, @timestamp DESC

References:

• Elastic PR #107545: ESQL: Union Types Support

[elasticmachine]

Pinging @elastic/kibana-esql (Team:ESQL)