Known Limitations & Roadmap

SoftClient4ES runs a large, practical subset of ANSI SQL on Elasticsearch — including cross-index JOINs that Elasticsearch itself cannot do. A few advanced constructs (subqueries, CTEs, set operators beyond UNION ALL) are not in the current release yet. This page tells you exactly what works as of this release, what’s coming, and how to get unblocked today.

Great for explicit JOIN SQL — full BI-tool subquery / CTE support is coming in the next release.

Using a BI tool? Read this first

Two different things can stop a BI tool here, and it is worth separating them.

Some tools cannot connect at all — and that is on their side

Metabase — no generic JDBC database type exists; anything not on Metabase’s own driver list needs a community driver plugin, which is code nobody has written for SoftClient4ES. See Metabase.
Power BI — Power Query has no JDBC connector; its generic connectors are ODBC and OData. The only candidate path is a generic Arrow Flight SQL ODBC driver, which is unproven. See Power BI.
Looker — Looker connects only through drivers it maintains itself, and it allowlists JDBC parameters per dialect, so a customer-supplied driver cannot be introduced. This gap is structural, not commercial — a licence would not close it.
dbt — dbt requires a dedicated adapter plugin per platform. There is no generic JDBC or ODBC adapter, and no SoftClient4ES adapter.

None of these is a gap we can close from our side: each one needs either a change by the vendor or a driver or adapter plugin that nobody has written.

(Each blocker checked against the vendor’s own connection documentation — Metabase, Microsoft Power Query, Looker and dbt — on 2026-08-31 and 2026-09-01.)

Tools that connect, but generate SQL we do not accept yet

Some BI tools auto-generate nested SQL (subqueries / derived tables) even when your logical query has none. Until the next release lands full subquery support, send explicit JOIN SQL instead of letting the tool compose nested queries — where the tool lets you:

Apache Superset / DBeaver / Grafana — you control the SQL. Write explicit JOINs for anything that would otherwise nest, and everything in Works in this release below is available to you.
Tableau — connecting and browsing work; queries are the constrained part. Drag-and-drop worksheets quote and fully qualify every identifier, a form we do not accept yet, and Custom SQL is not a way around it: Tableau documents that it “must wrap the custom SQL statement within a select statement” (Tableau’s Custom SQL documentation, checked 2026-09-01), which turns your query into a derived table. Extract mode narrows the exposure but does not remove it — the extract is still built by querying the source. See Tableau.

General rule: prefer explicit JOIN SQL over tool-generated nested SQL. If you control the query, a cross-index JOIN is fully supported in the current release.

Apache Superset (dedicated dialect), DBeaver, and Grafana (via Arrow Flight SQL) are Tested. Tableau is Compatible — the connection path works, but it is not yet in our formal regression suite.

Works in this release

Cross-index JOINs: INNER / LEFT / RIGHT / FULL / CROSS, plus JOIN UNNEST on nested arrays — something Elasticsearch cannot do natively. (See the JOIN matrix walkthrough for the per-tier rows and worked examples.)
Aggregations + GROUP BY / HAVING.
Analytical SQL: ROW_NUMBER / RANK / DENSE_RANK; the STDDEV / VARIANCE family (STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP); PERCENTILE_CONT / PERCENTILE_DISC; window aggregates and FIRST_VALUE / LAST_VALUE / ARRAY_AGG over OVER (PARTITION BY …).
- The STDDEV / VARIANCE family over a transformed operand (STDDEV(YEAR(hire_date)), VARIANCE(ABS(salary)), plain or windowed) is computed over the transform on Elasticsearch 7, 8 and 9. On Elasticsearch 6 the query is refused with a 400 naming the release, because the client library that line builds on drops the aggregation script — where earlier releases silently returned the statistic of the raw field. Aggregate over a raw field there, or use Elasticsearch 7+. The 6.x refusal is permanent (unmaintained library line). See DQL — Queries.
Conditionals & null handling: CASE / COALESCE / NULLIF / GREATEST / LEAST / ISNULL / ISNOTNULL.
ORDER BY … NULLS FIRST | NULLS LAST.
UNION ALL (concatenate result sets — no de-duplication).
SELECT * EXCEPT(col, …) — drop named columns from SELECT *. This is the BigQuery-style column-exclusion clause. It is not the EXCEPT set operator (see below).

Not in this release (coming in the next release, Quarter 4 2026)

Subqueries: scalar, IN (SELECT …), EXISTS (SELECT …), derived tables FROM (SELECT …), and correlated subqueries.
CTEs: WITH name AS (SELECT …) — recursive and non-recursive.
Set operators: UNION (with row de-duplication), INTERSECT, and the EXCEPT set operator. The EXCEPT set operator is distinct from the SELECT * EXCEPT(cols) column-exclusion clause above — that one works; the set operator does not.
Positional / tiling window functions: NTILE, LAG, LEAD — not yet implemented; coming with the next release’s analytical-SQL work. (Note: PERCENTILE_CONT / PERCENTILE_DISC — percentile aggregates — already work in the current release; the positional/tiling window functions are a different family.)

These arrive in the next release as a driver-side enhancement — single-cluster customers get them by upgrading the driver (JDBC / ADBC / sidecar), with no infrastructure change and no federation server required.

What a not-yet-supported query looks like

A subquery in a WHERE clause is rejected by the parser today:

-- Not supported in the current release: subqueries are not yet implemented.
SELECT name
FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE region = 'EU');

The parser rejects this — IN accepts only literal value lists today, not a nested SELECT. Rewrite it as an explicit JOIN (fully supported), or wait for the next release where the subquery form lands as-is.

Temporary tables are not supported

CREATE TEMPORARY TABLE and CREATE [LOCAL | GLOBAL] TEMPORARY TABLE, with or without ON COMMIT ... ROWS, are refused by intent, and the error names the construct. Two reasons, both structural:

An Elasticsearch index is cluster-global and has no session scope. It is visible to every client that can read the cluster, and there is no session for it to belong to or be cleaned up with — the JDBC and ADBC drivers have no server process at all, so nothing can be told that a connection ended.
ON COMMIT ... ROWS is transaction semantics, and Elasticsearch has no transactions. There is nothing to honour, and accepting the clause to ignore it would silently change how long your data lives.

Use CREATE TABLE for a regular index and DROP TABLE it when you are done.

Tableau’s connection-capability probe issues a CREATE TABLE / DROP TABLE pair against a #-prefixed name, and its Generic SQL-92 dialect issues CREATE LOCAL TEMPORARY TABLE. Every one of those statements is refused, and Tableau then takes its documented fallback. For what that fallback costs — and why a .tdc customization file cannot skip the probe — see Tableau.

A plain CREATE TABLE against a probe-shaped name is refused too, but on Elasticsearch’s index naming rules rather than on the temporary-table grammar: an index name must be lowercase and cannot contain \, /, *, ?, ", <, >, |, a space, a comma or #. The error names every rule the name breaks.

What “not session-scoped” does and does not imply. It is not that Tableau requires session scope — Tableau’s own capability CAP_TEMP_TABLES_NOT_SESSION_SCOPED exists precisely for sources that “use regular tables to simulate temp tables”. So a future release could choose to serve temporary tables with something other than a session. The reason we do not is a cost and a safety argument, not an impossibility: every temporary table would be a cluster-state update serialised through the elected master, and anything that failed to clean them up would accumulate indices in your production cluster until the per-node shard limit refused all index creation, yours included.

Coming in the upcoming release (Quarter 1 2027)

Heterogeneous federation: JOIN or correlate Elasticsearch with PostgreSQL, MySQL, ClickHouse, Snowflake, and more — plus cross-cluster subqueries (e.g. correlate one cluster’s data against another’s).

Deferred (a future release, demand-driven — tell us what you need)

MERGE, RETURNING, INFORMATION_SCHEMA, non-materialized CREATE VIEW, DECIMAL, TIMESTAMP WITH TIME ZONE, INTERVAL as a type, and UUID. No committed date — these are prioritised by customer demand. (Current-release DML already supports INSERT … ON CONFLICT upsert — a different feature from MERGE.)

Roadmap timing

We do not commit firm external dates. The next release is targeted for Quarter 4 2026; the upcoming release (heterogeneous federation) for Quarter 1 2027; the deferred items are demand-driven with no committed date. Treat the next release’s feature list as planned, not guaranteed — its scope is gated on a function-library audit.