elasticsearch/docs/reference/esql/esql-limitations.asciidoc

[[esql-limitations]]
== {esql} limitations

++++
<titleabbrev>Limitations</titleabbrev>
++++

[discrete]
[[esql-max-rows]]
=== Result set size limit

By default, an {esql} query returns up to 1000 rows. You can increase the number
of rows up to 10,000 using the <<esql-limit>> command.
include::processing-commands/limit.asciidoc[tag=limitation]

[discrete]
[[esql-supported-types]]
=== Field types

[discrete]
==== Supported types

{esql} currently supports the following <<mapping-types,field types>>:

* `alias`
* `boolean`
* `date`
* `date_nanos` (Tech Preview)
** The following functions don't yet support date nanos: `bucket`, `date_format`, `date_parse`, `date_diff`, `date_extract`
** You can use `to_datetime` to cast to millisecond dates to use unsupported functions
* `double` (`float`, `half_float`, `scaled_float` are represented as `double`)
* `ip`
* `keyword` family including `keyword`, `constant_keyword`, and `wildcard`
* `int` (`short` and `byte` are represented as `int`)
* `long`
* `null`
* `text`
* experimental:[] `unsigned_long`
* `version`
* Spatial types
** `geo_point`
** `geo_shape`
** `point`
** `shape`

[discrete]
==== Unsupported types

{esql} does not yet support the following field types:

* TSDB metrics
** `counter`
** `position`
** `aggregate_metric_double`
* Date/time
** `date_range`
* Other types
** `binary`
** `completion`
** `dense_vector`
** `double_range`
** `flattened`
** `float_range`
** `histogram`
** `integer_range`
** `ip_range`
** `long_range`
** `nested`
** `rank_feature`
** `rank_features`
** `search_as_you_type`

Querying a column with an unsupported type returns an error. If a column with an
unsupported type is not explicitly used in a query, it is returned with `null`
values, with the exception of nested fields. Nested fields are not returned at
all.

[discrete]
==== Limitations on supported types

Some <<mapping-types,field types>> are not supported in all contexts:

* Spatial types are not supported in the <<esql-sort,SORT>> processing command.
  Specifying a column of one of these types as a sort parameter will result in an error:
** `geo_point`
** `geo_shape`
** `cartesian_point`
** `cartesian_shape`

In addition, when <<esql-multi-index, querying multiple indexes>>,
it's possible for the same field to be mapped to multiple types.
These fields cannot be directly used in queries or returned in results,
unless they're <<esql-multi-index-union-types, explicitly converted to a single type>>.

[discrete]
[[esql-_source-availability]]
=== _source availability

{esql} does not support configurations where the
<<mapping-source-field,_source field>> is <<disable-source-field,disabled>>.

experimental:[] {esql}'s support for <<synthetic-source,synthetic `_source`>>
is currently experimental.

[discrete]
[[esql-limitations-full-text-search]]
=== Full-text search

experimental:[] {esql}'s support for <<esql-search-functions,full-text search>>
is currently in Technical Preview. One limitation of full-text search is that
it is necessary to use the search function, like <<esql-match>>, in a <<esql-where>> command
directly after the <<esql-from>> source command, or close enough to it.
Otherwise, the query will fail with a validation error.
Another limitation is that any <<esql-where>> command containing a full-text search function
cannot also use disjunctions (`OR`).

Because of <<esql-limitations-text-fields,the way {esql} treats `text` values>>,
queries on `text` fields are like queries on `keyword` fields: they are
case-sensitive and need to match the full string.

For example, after indexing a field of type `text` with the value `Elasticsearch
query language`, the following `WHERE` clause does not match because the `LIKE`
operator is case-sensitive:
[source,esql]
----
| WHERE field LIKE "elasticsearch query language"
----

The following `WHERE` clause does not match either, because the `LIKE` operator
tries to match the whole string:
[source,esql]
----
| WHERE field LIKE "Elasticsearch"
----

As a workaround, use wildcards and regular expressions. For example:
[source,esql]
----
| WHERE field RLIKE "[Ee]lasticsearch.*"
----

[discrete]
[[esql-limitations-text-fields]]
=== `text` fields behave like `keyword` fields

While {esql} supports <<text,`text`>> fields, {esql} does not treat these fields
like the Search API does. {esql} queries do not query or aggregate the
<<analysis,analyzed string>>. Instead, an {esql} query will try to get a `text`
field's subfield of the <<keyword,keyword family type>> and query/aggregate
that. If it's not possible to retrieve a `keyword` subfield, {esql} will get the
string from a document's `_source`. If the `_source` cannot be retrieved, for
example when using synthetic source, `null` is returned.

Note that {esql}'s retrieval of `keyword` subfields may have unexpected
consequences. An {esql} query on a `text` field is case-sensitive. Furthermore,
a subfield may have been mapped with a <<normalizer,normalizer>>, which can
transform the original string. Or it may have been mapped with <<ignore-above>>,
which can truncate the string. None of these mapping operations are applied to
an {esql} query, which may lead to false positives or negatives.

To avoid these issues, a best practice is to be explicit about the field that
you query, and query `keyword` sub-fields instead of `text` fields.

[discrete]
[[esql-tsdb]]
=== Time series data streams are not supported

{esql} does not support querying time series data streams (TSDS).

[discrete]
[[esql-limitations-date-math]]
=== Date math limitations

Date math expressions work well when the leftmost expression is a datetime, for
example:
[source,txt]
----
now() + 1 year - 2hour + ...
----

But using parentheses or putting the datetime to the right is not always supported yet. For example, the following expressions fail:
[source,txt]
----
1year + 2hour + now()
now() + (1year + 2hour)
----

Date math does not allow subtracting two datetimes, for example:
[source,txt]
----
now() - 2023-10-26
----

[discrete]
[[esql-limitations-enrich]]
=== Enrich limitations

include::esql-enrich-data.asciidoc[tag=limitations]

[discrete]
[[esql-limitations-dissect]]
=== Dissect limitations

include::esql-process-data-with-dissect-grok.asciidoc[tag=dissect-limitations]

[discrete]
[[esql-limitations-grok]]
=== Grok limitations

include::esql-process-data-with-dissect-grok.asciidoc[tag=grok-limitations]

[discrete]
[[esql-limitations-mv]]
=== Multivalue limitations

{esql} <<esql-multivalued-fields,supports multivalued fields>>, but functions
return `null` when applied to a multivalued field, unless documented otherwise.
Work around this limitation by converting the field to single value with one of
the <<esql-mv-functions,multivalue functions>>.

[discrete]
[[esql-limitations-timezone]]
=== Timezone support

{esql} only supports the UTC timezone.

[discrete]
[[esql-limitations-kibana]]
=== Kibana limitations

include::esql-kibana.asciidoc[tag=limitations]