SQL Limitations
editSQL Limitations
editThis functionality is in beta and is subject to change. The design and code is less mature than official GA features and is being provided as-is with no warranties. Beta features are not subject to the support SLA of official GA features.
Nested fields in SYS COLUMNS
and DESCRIBE TABLE
editElasticsearch 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;
Multi-nested fields
editElasticsearch 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.
Paginating nested inner hits
editWhen 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.
Normalized keyword
fields
editkeyword
fields in Elasticsearch can be normalized by defining a normalizer
. Such fields are not supported in Elasticsearch SQL.
Array type of fields
editArray 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.
Sorting by aggregation
editWhen doing aggregations (GROUP BY
) Elasticsearch SQL relies on Elasticsearch’s composite
aggregation for its support for paginating results.
But this type of aggregation does come with a limitation: sorting can only be applied on the key used for the aggregation’s buckets. This
means that queries like SELECT * FROM test GROUP BY age ORDER BY COUNT(*)
are not possible.
Using aggregation functions on top of scalar functions
editAggregation 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 a sub-select
editUsing 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.