Cube 1.8.13 fixes a Top-N pushdown bug that could return the wrong groups on Trino

Cube Core 1.8.13 fixes a query-planning bug that could make a Top-N chart return arbitrary groups instead of the highest-valued groups when Cube’s SQL API pushed a filtered aggregate query to Trino or Presto. The October 25 release ships the correction as a CubeSQL fix.

Why the query could look valid and still be wrong

The reported failure involved a grouped aggregate that was filtered, sorted and limited—the shape generated by a BI Top-N visual. Cube placed ORDER BY inside a subquery, then applied the WHERE … IS NOT NULL filter and LIMIT at an outer level. As the reproduction explains, Trino can ignore an ORDER BY in a subquery when the limit is not at that same level.

That distinction changed the result rather than producing a database error. In the issue’s four-row sales example, the expected top two groups were gizmo 700 and gadget 370; the affected plan could instead return widget 330 and gadget 370. Trino emitted an “ORDER BY in subquery may have no effect” warning, but the query itself completed. The reporter reproduced the problem with Cube 1.7.16 and 1.8.6 against Trino 460; DuckDB happened to preserve the inner ordering in the same test.

What 1.8.13 changes

The merged fix and regression test keep the sort and limit together above the filter for this Top-N plan. Cube says a sort beneath a limit is no longer pushed below a filter, while a filter that Cube itself can execute still produces one pushed-down query carrying the filter, order and limit.

The pull request also states an important remaining boundary. If SQL pushdown is enabled but a filter can only run in DataFusion—for example, because the target dialect lacks a template for a function—the Cube query may no longer carry the ordering. Above Cube’s non-streaming row limit, the final Top-N operation can therefore select from an arbitrary slice rather than the complete ordered set.

What operators should check

Teams using Cube’s PostgreSQL-compatible SQL API over Trino or Presto should prioritize the 1.8.13 update when dashboards or agents generate filtered Top-N queries. They should also inspect Trino warnings and rerun representative rankings after upgrading, especially where calculated columns, null filters or dialect-specific functions are involved.

The larger lesson for analytics-agent deployments is that successful SQL execution is not enough. A semantic layer can generate syntactically accepted SQL whose nesting changes ranking semantics. Regression suites should assert the returned members and order—not merely that the query runs or returns the requested number of rows.