SQL Limitations

Extremely large queries can consume too much memory during the parsing phase, in which case the Elasticsearch SQL engine will abort parsing and throw an error. In such cases, consider reducing the query to a smaller size by potentially simplifying it or splitting it into smaller queries.

Elasticsearch has a special type of relationship fields called nested fields. In Elasticsearch SQL they can be used by referencing their inner sub-fields. Even though SYS COLUMNS in non-driver mode (in the CLI and in REST calls) and DESCRIBE TABLE will still display them as having the type NESTED, they cannot be used in a query. One can only reference its sub-fields in the form:

[nested_field_name].[sub_field_name]

For example:

SELECT dep.dep_name.keyword FROM test_emp GROUP BY languages;

Elasticsearch SQL doesn’t support the usage of scalar functions on top of nested fields in WHERE and ORDER BY clauses with the exception of comparison and logical operators.

For example:

SELECT * FROM test_emp WHERE LENGTH(dep.dep_name.keyword) > 5;

and

SELECT * FROM test_emp ORDER BY YEAR(dep.start_date);

are not supported but:

SELECT * FROM test_emp WHERE dep.start_date >= CAST('2020-01-01' AS DATE) OR dep.dep_end_date IS NULL;

is supported.

Elasticsearch SQL doesn’t support multi-nested documents, so a query cannot reference more than one nested field in an index. This applies to multi-level nested fields, but also multiple nested fields defined on the same level. For example, for this index:

       column         |     type      |    mapping
----------------------+---------------+-------------
nested_A              |STRUCT         |NESTED
nested_A.nested_X     |STRUCT         |NESTED
nested_A.nested_X.text|VARCHAR        |KEYWORD
nested_A.text         |VARCHAR        |KEYWORD
nested_B              |STRUCT         |NESTED
nested_B.text         |VARCHAR        |KEYWORD

nested_A and nested_B cannot be used at the same time, nor nested_A/nested_B and nested_A.nested_X combination. For such situations, Elasticsearch SQL will display an error message.

When SELECTing a nested field, pagination will not work as expected, Elasticsearch SQL will return at least the page size records. This is because of the way nested queries work in Elasticsearch: the root nested field will be returned and it’s matching inner nested fields as well, pagination taking place on the root nested document and not on its inner hits.

keyword fields in Elasticsearch can be normalized by defining a normalizer. Such fields are not supported in Elasticsearch SQL.

Array fields are not supported due to the "invisible" way in which Elasticsearch handles an array of values: the mapping doesn’t indicate whether a field is an array (has multiple values) or not, so without reading all the data, Elasticsearch SQL cannot know whether a field is a single or multi value. When multiple values are returned for a field, by default, Elasticsearch SQL will throw an exception. However, it is possible to change this behavior through field_multi_value_leniency parameter in REST (disabled by default) or field.multi.value.leniency in drivers (enabled by default).

When doing aggregations (GROUP BY) Elasticsearch SQL relies on Elasticsearch’s composite aggregation for its support for paginating results. However this type of aggregation does come with a limitation: sorting can only be applied on the key used for the aggregation’s buckets. Elasticsearch SQL overcomes this limitation by doing client-side sorting however as a safety measure, allows only up to 512 rows.

It is recommended to use LIMIT for queries that use sorting by aggregation, essentially indicating the top N results that are desired:

SELECT * FROM test GROUP BY age ORDER BY COUNT(*) LIMIT 100;

It is possible to run the same queries without a LIMIT however in that case if the maximum size (10000) is passed, an exception will be returned as Elasticsearch SQL is unable to track (and sort) all the results returned.

Moreover, the aggregation(s) used in the ORDER BY must be only plain aggregate functions. No scalar functions or operators can be used, and therefore no complex columns that combine two ore more aggregate functions can be used for ordering. Here are some examples of queries that are not allowed:

SELECT age, ROUND(AVG(salary)) AS avg FROM test GROUP BY age ORDER BY avg;

SELECT age, MAX(salary) - MIN(salary) AS diff FROM test GROUP BY age ORDER BY diff;

Aggregation functions like MIN, MAX, etc. can only be used directly on fields, and so queries like SELECT MAX(abs(age)) FROM test are not possible.

Using sub-selects (SELECT X FROM (SELECT Y)) is supported to a small degree: any sub-select that can be "flattened" into a single SELECT is possible with Elasticsearch SQL. For example:

SELECT * FROM (SELECT first_name, last_name FROM emp WHERE last_name NOT LIKE '%a%') WHERE first_name LIKE 'A%' ORDER BY 1;

  first_name   |   last_name
---------------+---------------
 Alejandro     |McAlpine
 Anneke        |Preusig
 Anoosh        |Peyn
 Arumugam      |Ossenbruggen

The query above is possible because it is equivalent with:

SELECT first_name, last_name FROM emp WHERE last_name NOT LIKE '%a%' AND first_name LIKE 'A%' ORDER BY 1;

But, if the sub-select would include a GROUP BY or HAVING or the enclosing SELECT would be more complex than SELECT X FROM (SELECT ...) WHERE [simple_condition], this is currently un-supported.

Using FIRST and LAST in the HAVING clause is not supported. The same applies to MIN and MAX when their target column is of type keyword as they are internally translated to FIRST and LAST.

Using TIME data type as a grouping key is currently not supported. For example:

SELECT count(*) FROM test GROUP BY CAST(date_created AS TIME);

On the other hand, it can still be used if it’s wrapped with a scalar function that returns another data type, for example:

SELECT count(*) FROM test GROUP BY MINUTE((CAST(date_created AS TIME));

TIME data type is also currently not supported in histogram grouping function. For example:

SELECT HISTOGRAM(CAST(birth_date AS TIME), INTERVAL '10' MINUTES) as h, COUNT(*) FROM t GROUP BY h

Since geo_shape fields don’t have doc values these fields cannot be used for filtering, grouping or sorting.

By default,geo_points fields are indexed and have doc values. However only latitude and longitude are stored and indexed with some loss of precision from the original values (4.190951585769653E-8 for the latitude and 8.381903171539307E-8 for longitude). The altitude component is accepted but not stored in doc values nor indexed. Therefore calling ST_Z function in the filtering, grouping or sorting will return null.

Most of Elasticsearch SQL’s columns are retrieved from the document’s _source and there is no attempt to get the columns content from docvalue_fields not even in the case _source field is disabled in the mapping explicitly. If a column, for which there is no source stored, is asked for in a query, Elasticsearch SQL will not return it. Field types that don’t follow this restriction are: keyword, date, scaled_float, geo_point, geo_shape since they are NOT returned from _source but from docvalue_fields.

When the number of columns retrievable from docvalue_fields is greater than the configured index.max_docvalue_fields_search setting the query will fail with IllegalArgumentException: Trying to retrieve too many docvalue_fields error. Either the mentioned Elasticsearch setting needs to be adjusted or fewer columns retrievable from docvalue_fields need to be selected.

The aggregation expression in PIVOT will currently accept only one aggregation. It is thus not possible to obtain multiple aggregations for any one pivoted column.

The values that the PIVOT query could pivot must be provided in the query as a list of literals; providing a subquery instead to build this list is not currently supported. For example, in this query:

SELECT * FROM test_emp PIVOT (SUM(salary) FOR languages IN (1, 2))

the languages of interest must be listed explicitly: IN (1, 2). On the other hand, this example would not work:

SELECT * FROM test_emp PIVOT (SUM(salary) FOR languages IN (SELECT languages FROM test_emp WHERE languages <=2 GROUP BY languages))