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 1,000 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` <<keyword, family>> including `keyword`, `constant_keyword`, and `wildcard`
* `int` (`short` and `byte` are represented as `int`)
* `long`
* `null`
* `text` <<text, family>> including `text`, `semantic_text` and `match_only_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>>.

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

One limitation of <<esql-search-functions,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 use disjunctions (`OR`), unless:

* All functions used in the OR clauses are full-text functions themselves, or scoring is not used

For example, this query is valid:

[source,esql]
----
FROM books
| WHERE MATCH(author, "Faulkner") AND MATCH(author, "Tolkien")
----

But this query will fail due to the <<esql-stats-by, STATS>> command:

[source,esql]
----
FROM books
| STATS AVG(price) BY author
| WHERE MATCH(author, "Faulkner")
----

And this query that uses a disjunction will succeed:

[source,esql]
----
FROM books
| WHERE MATCH(author, "Faulkner") OR QSTR("author: Hemingway")
----

However using scoring will fail because it uses a non full text function as part of the disjunction:

[source,esql]
----
FROM books METADATA _score
| WHERE MATCH(author, "Faulkner") OR author LIKE "Hemingway"
----

Scoring will work in the following query, as it uses full text functions on both `OR` clauses:

[source,esql]
----
FROM books METADATA _score
| WHERE MATCH(author, "Faulkner") OR QSTR("author: Hemingway")
----


Note that, because of <<esql-limitations-text-fields,the way {esql} treats `text` values>>,
any queries on `text` fields that do not explicitly use the full-text functions,
<<esql-match>>, <<esql-qstr>> or <<esql-kql>>, will behave as if the fields are actually `keyword` fields:
they are case-sensitive and need to match the full string.

[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.

Once a `text` field is retrieved, if the query touches it in any way, for example passing
it into a function, the type will be converted to `keyword`. In fact, functions that operate on both
`text` and `keyword` fields will perform as if the `text` field was a `keyword` field all along.

For example, the following query will return a column `greatest` of type `keyword` no matter
whether any or all of `field1`, `field2`, and `field3` are of type `text`:
[source,esql]
----
| FROM index
| EVAL greatest = GREATEST(field1, field2, field3)
----

Note that {esql}'s retrieval of `keyword` subfields may have unexpected
consequences. Other than when explicitly using the full-text functions, <<esql-match>> and <<esql-qstr>>,
any {esql} query on a `text` field is case-sensitive.

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.*"
----

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.
Or consider using one of the <<esql-search-functions,full-text search>> functions.

[discrete]
[[esql-multi-index-limitations]]
=== Using {esql} to query multiple indices

As discussed in more detail in <<esql-multi-index>>, {esql} can execute a single query across multiple indices,
data streams, or aliases. However, there are some limitations to be aware of:

* All underlying indexes and shards must be active. Using admin commands or UI,
  it is possible to pause an index or shard, for example by disabling a frozen tier instance,
  but then any {esql} query that includes that index or shard will fail, even if the query uses
  <<esql-where>> to filter out the results from the paused index.
  If you see an error of type `search_phase_execution_exception`,
  with the message `Search rejected due to missing shards`, you likely have an index or shard in `UNASSIGNED` state.
* The same field must have the same type across all indexes. If the same field is mapped to different types
  it is still possible to query the indexes,
  but the field must be <<esql-multi-index-union-types,explicitly converted to a single type>>.

[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]