--- title: ES|QL limitations description: By default, an ES|QL query returns up to 1,000 rows. You can increase the number of rows up to 10,000 using the LIMIT command. ES|QL currently supports... url: https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/limitations products: - Elasticsearch applies_to: - Elastic Cloud Serverless: Generally available - Elastic Stack: Generally available --- # ES|QL limitations ## Result set size limit By default, an ES|QL query returns up to 1,000 rows. You can increase the number of rows up to 10,000 using the [`LIMIT`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/limit) command. For instance, ```esql FROM index | WHERE field = "value" ``` is equivalent to: ```esql FROM index | WHERE field = "value" | LIMIT 1000 ``` Queries do not return more than 10,000 rows, regardless of the `LIMIT` command’s value. This is a configurable upper limit. To overcome this limitation: - Reduce the result set size by modifying the query to only return relevant data. Use [`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/where) to select a smaller subset of the data. - Shift any post-query processing to the query itself. You can use the ES|QL [`STATS`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/stats-by) command to aggregate data in the query. The upper limit only applies to the number of rows that are output by the query, not to the number of documents it processes: the query runs on the full data set. Consider the following two queries: ```esql FROM index | WHERE field0 == "value" | LIMIT 20000 ``` and ```esql FROM index | STATS AVG(field1) BY field2 | LIMIT 20000 ``` In both cases, the filtering by `field0` in the first query or the grouping by `field2` in the second is applied over all the documents present in the `index`, irrespective of their number or indexes size. However, both queries will return at most 10,000 rows, even if there were more rows available to return. The default and maximum limits can be changed using these dynamic cluster settings: - `esql.query.result_truncation_default_size` - `esql.query.result_truncation_max_size` For time-series aggregations under the [`TS`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/ts) source command specifically, the default and maximum limits are higher and can be changed using these cluster settings: - `esql.query.timeseries_result_truncation_default_size` - `esql.query.timeseries_result_truncation_max_size` Modifying these values involves trade-offs. A larger result-set involves a higher memory pressure and increased processing times; the internode traffic within and across clusters can also increase. These limitations are similar to those enforced by the [search API for pagination](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/elasticsearch/rest-apis/paginate-search-results). | Functionality | Search | ES|QL | |-------------------------------------------------------------|-------------------------|------------------------------------------------------| | Results returned by default | 10 | 1,000 | | Results returned by default (time-series aggregations) | 10 | 10,000 | | Default upper limit | 10,000 | 10,000 | | Default upper limit (time-series aggregations) | 10,000 | 10,000,000 | | Specify number of results | `size` | `LIMIT` | | Change default number of results | n/a | esql.query.result_truncation_default_size | | Change default number of results (time-series aggregations) | n/a | esql.query.timeseries_result_truncation_default_size | | Change default upper limit | index-max-result-window | esql.query.result_truncation_max_size | | Change default upper limit (time-series aggregations) | index-max-result-window | esql.query.timeseries_result_truncation_max_size | ## Field types ### Supported types ES|QL currently supports the following [field types](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/elasticsearch/mapping-reference/field-data-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`) - `dense_vector` Elastic Stack: Preview since 9.2 Elastic Cloud Serverless: Preview - `ip` - `keyword` [family](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/elasticsearch/mapping-reference/keyword) including `keyword`, `constant_keyword`, and `wildcard` - `int` (`short` and `byte` are represented as `int`) - `long` - `null` - `text` [family](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/elasticsearch/mapping-reference/text) including `text`, `semantic_text` and `match_only_text` - `unsigned_long` Elastic Stack: Preview Elastic Cloud Serverless: Preview - `version` - Spatial types - `geo_point` - `geo_shape` - `point` - `shape` - TSDB metrics Elastic Stack: Preview since 9.2 Elastic Cloud Serverless: Preview - `counter` - `gauge` - `aggregate_metric_double` - `exponential_histogram` Elastic Stack: Preview in 9.3 - `tdigest` Elastic Stack: Preview in 9.3 ### Unsupported types ES|QL does not support certain field types. If the limitation only applies to specific product versions, it is indicated in the following list: - Elastic Stack: Generally available from 9.0 to 9.1 `dense_vector` - Elastic Stack: Generally available from 9.0 to 9.1 TSDB metrics - `counter` - `gauge` - `aggregate_metric_double` - Date/time - `date_range` - Other types - `binary` - `completion` - `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. ### Limitations on supported types Some [field types](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/elasticsearch/mapping-reference/field-data-types) are not supported in all contexts: - Spatial types are not supported in the [SORT](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/sort) processing command. Specifying an expression that evaluates to one of these types as a sort key will result in an error: - `geo_point` - `geo_shape` - `cartesian_point` - `cartesian_shape` - In addition, when [querying multiple indexes](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/esql-multi-index), 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 [explicitly converted to a single type](/elastic/docs-builder/docs/3028/reference/query-languages/esql/esql-multi-index#esql-multi-index-union-types). #### Spatial precision The spatial types `geo_point`, `geo_shape`, `cartesian_point` and `cartesian_shape` are maintained at source precision in the original documents, but indexed at reduced precision by Lucene, for performance reasons. To ensure this optimization is available in the widest context, all [spatial functions](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/spatial-functions) will produce results at this reduced precision, aligned with the underlying Lucene index grid. For `geo_point` and `geo_shape`, this grid is smaller than 1 cm at the equator, which is still very high precision for most use cases. If the exact, original precision is desired, return the original field in the ES|QL query, which will maintain the original values. To prioritize performance over precision, simply drop that field. For example: ```esql FROM airports | EVAL geohex = ST_GEOHEX(location, 1) | KEEP location, geohex ``` This query will perform slowly, due to the need to retrieve the original `location` field from the source document. However, the following example will perform much faster: ```esql FROM airports | EVAL geohex = ST_GEOHEX(location, 1) | EVAL x = ST_X(location), y = ST_Y(location) | KEEP x, y, geohex ``` This query will perform much faster, since the original field `location` is not retrieved, and the three spatial functions used will all return values aligned with the Lucene index grid. Note that if you return both the original `location` and the extracted `x` and `y` you will see very slight differences in the extracted values due to the precision loss. #### Partial support in 9.2.0 - Elastic Stack: Preview since 9.2 The following types are only partially supported on 9.2.0. This is fixed in 9.2.1: - `dense_vector`: The [`KNN` function](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/dense-vector-functions/knn) and the [`TO_DENSE_VECTOR` function](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/type-conversion-functions/to_dense_vector) will work and any field data will be retrieved as part of the results. However, the type will appear as `unsupported` when these functions are not used. - `aggregate_metric_double`: Using the [`TO_AGGREGATE_METRIC_DOUBLE` function](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/type-conversion-functions/to_aggregate_metric_double) will work and any field data will be retrieved as part of the results. However, the type will appear as `unsupported` if this function is not used. This means that a simple query like `FROM test` will not retrieve `dense_vector` or `aggregate_metric_double` data. However, using the appropriate functions will work: - `FROM test WHERE KNN("dense_vector_field", [0, 1, 2, ...])` - `FROM test | EVAL agm_data = TO_AGGREGATE_METRIC_DOUBLE(aggregate_metric_double_field)` ## _source availability ES|QL does not support configurations where the [_source field](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/elasticsearch/mapping-reference/mapping-source-field) is [disabled](/elastic/docs-builder/docs/3028/reference/elasticsearch/mapping-reference/mapping-source-field#disable-source-field). ## Full-text search One limitation of [full-text search](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/search-functions) is that it is necessary to use the search function, like [`MATCH`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/search-functions/match), in a [`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/where) command directly after the [`FROM`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/from) source command, or close enough to it. Otherwise, the query will fail with a validation error. For example, this query is valid: ```esql FROM books | WHERE MATCH(author, "Faulkner") AND MATCH(author, "Tolkien") ``` But this query will fail due to the [STATS](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/stats-by) command: ```esql FROM books | STATS AVG(price) BY author | WHERE MATCH(author, "Faulkner") ``` Note that any queries on `text` fields that do not explicitly use the full-text functions, [`MATCH`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/search-functions/match), [`QSTR`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/search-functions/qstr) or [`KQL`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/search-functions/kql), will behave as if the fields are actually `keyword` fields: they are case-sensitive and need to match the full string. ## Using ES|QL to query multiple indices As discussed in more detail in [Using ES|QL to query multiple indices](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/esql-multi-index), ES|QL 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 ES|QL query that includes that index or shard will fail, even if the query uses [`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/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 [explicitly converted to a single type](/elastic/docs-builder/docs/3028/reference/query-languages/esql/esql-multi-index#esql-multi-index-union-types). ## Time series data streams Time series data streams (TSDS) are supported in technical preview. ES|QL does not support querying time series data streams (TSDS). ## Date math limitations Date math expressions work well when the leftmost expression is a datetime, for example: ```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: ```txt 1year + 2hour + now() now() + (1year + 2hour) ``` Date math does not allow subtracting two datetimes, for example: ```txt now() - 2023-10-26 ``` ## Enrich limitations While all three enrich policy types are supported, there are some limitations to be aware of: - The `geo_match` enrich policy type only supports the `intersects` spatial relation. - It is required that the `match_field` in the `ENRICH` command is of the correct type. For example, if the enrich policy is of type `geo_match`, the `match_field` in the `ENRICH` command must be of type `geo_point` or `geo_shape`. Likewise, a `range` enrich policy requires a `match_field` of type `integer`, `long`, `date`, or `ip`, depending on the type of the range field in the original enrich index. - However, this constraint is relaxed for `range` policies when the `match_field` is of type `KEYWORD`. In this case the field values will be parsed during query execution, row by row. If any value fails to parse, the output values for that row will be set to `null`, an appropriate warning will be produced and the query will continue to execute. ## Dissect limitations The `DISSECT` command does not support reference keys. ## Grok limitations The `GROK` command does not support configuring [custom patterns](/elastic/docs-builder/docs/3028/reference/enrich-processor/grok-processor#custom-patterns), or [multiple patterns](/elastic/docs-builder/docs/3028/reference/enrich-processor/grok-processor#trace-match). The `GROK` command is not subject to [Grok watchdog settings](/elastic/docs-builder/docs/3028/reference/enrich-processor/grok-processor#grok-watchdog). ## Multivalue limitations ES|QL [supports multivalued fields](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/esql-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 [multivalue functions](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/mv-functions). ## Timezone support ES|QL only supports the UTC timezone. ## INLINE STATS limitations [`CATEGORIZE`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/grouping-functions/categorize) grouping function is not currently supported. Also, [`INLINE STATS`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/inlinestats-by) cannot yet have an unbounded [`SORT`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/sort) before it. You must either move the SORT after it, or add a [`LIMIT`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/limit) before the [`SORT`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/sort). ## Kibana limitations - The filter bar interface is not enabled when Discover is in ES|QL mode. To filter data, use [variable controls](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3028/explore-analyze/discover/try-esql#add-variable-control), filter buttons within the table and field list, or write a query that uses the [`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/where) command instead. - Discover shows no more than 10,000 rows. This limit only applies to the number of rows that are retrieved by the query and displayed in Discover. Queries and aggregations run on the full data set. - Discover shows no more than 50 columns. If a query returns more than 50 columns, Discover only shows the first 50. - CSV export from Discover shows no more than 10,000 rows. This limit only applies to the number of rows that are retrieved by the query and displayed in Discover. Queries and aggregations run on the full data set. - Querying many indices at once without any filters can cause an error in kibana which looks like `[esql] > Unexpected error from Elasticsearch: The content length (536885793) is bigger than the maximum allowed string (536870888)`. The response from ES|QL is too long. Use [`DROP`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/drop) or [`KEEP`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/keep) to limit the number of fields returned. ## Known issues Refer to [Known issues](https://www.elastic.co/elastic/docs-builder/docs/3028/release-notes/elasticsearch/known-issues) for a list of known issues for ES|QL.