hibernate/hibernate-orm · error · UnsupportedOperationException

SingleStore doesn't support UNION/UNION ALL with limit claus

Error message

SingleStore doesn't support UNION/UNION ALL with limit clause

What it means

renderCombinedLimitClause() in SingleStoreSqlAstTranslator throws when a LIMIT/OFFSET (setMaxResults/setFirstResult) applies to a top-level query part that is a UNION or UNION ALL: SingleStore cannot apply a limit clause to a combined result set in the position Hibernate would render it. The check inspects the current QueryGroup's set operator during SQL generation, so pagination is rejected at translation time rather than by the database.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SingleStoreSqlAstTranslator.java:327

			assert size == rhsExpressions.size();
			String separator = OPEN_PARENTHESIS + "";
			for ( int i = 0; i < size; i++ ) {
				appendSql( separator );
				renderDistinct( (Expression) lhsExpressions.get( i ), operator, (Expression) rhsExpressions.get( i ) );
				separator = ") and (";
			}
			appendSql( CLOSE_PARENTHESIS );
		}
		else {
			super.emulateTupleComparison( lhsExpressions, rhsExpressions, operator, indexOptimized );
		}
	}

	@Override
	protected void renderCombinedLimitClause(Expression offsetExpression, Expression fetchExpression) {
		if ( offsetExpression != null || fetchExpression != null ) {
			if ( getCurrentQueryPart() instanceof QueryGroup && (((QueryGroup) getCurrentQueryPart()).getSetOperator() == SetOperator.UNION || ((QueryGroup) getCurrentQueryPart()).getSetOperator() == SetOperator.UNION_ALL) ) {
				throw new UnsupportedOperationException(
						"SingleStore doesn't support UNION/UNION ALL with limit clause" );
			}
		}
		super.renderCombinedLimitClause( offsetExpression, fetchExpression );
	}


	@Override
	protected void renderPartitionItem(Expression expression) {
		if ( expression instanceof Literal ) {
			appendSql( "'0'" );
		}
		else if ( expression instanceof Summarization ) {
			Summarization summarization = (Summarization) expression;
			renderCommaSeparated( summarization.getGroupings() );
			appendSql( " with " );
			appendSql( summarization.getKind().sqlText() );
		}

View on GitHub (pinned to fad1729dce)

Solutions

1. Wrap the union in an outer SELECT and paginate that instead: 'select x from (select ... union select ...) order by x' with setMaxResults on the outer query
2. Apply the limit to each union arm individually when arm-level capping gives the same semantics (careful with global ordering)
3. Materialize the union into a temp table and paginate a plain select over it
4. Do the pagination in memory for small result sets

Example fix

// before - pagination on top-level union (throws)
em.createQuery('select a.id as x from A a union select b.id as x from B b order by x')
  .setMaxResults(20).getResultList();

// after - paginate the outer query
em.createQuery('select x from (select a.id as x from A a union select b.id as x from B b) order by x')
  .setMaxResults(20).getResultList();

static boolean isPaginatedUnion(String hql, int firstResult, int maxResults) {
    String lower = hql.toLowerCase();
    boolean topLevelUnion = lower.contains(' union ');
    return topLevelUnion && (firstResult > 0 || maxResults != Integer.MAX_VALUE);
}

if (dialect instanceof SingleStoreDialect && isPaginatedUnion(hql, first, max)) {
    throw new UnsupportedOperationException(
        'Paginate the outer query that wraps the union, not the union itself');
}

When it happens

Trigger: Calling setMaxResults(...) and/or setFirstResult(...) on an HQL query whose top level is a union, e.g. 'select a.id from A a union select b.id from B b' with pagination; Pageable/pageable request handling over union queries on SingleStore.

Common situations: Spring Data queries with Pageable over HQL unions; search screens merging multiple sources with UNION then paginating; code that worked on MySQL (which allows LIMIT after UNION) moved to SingleStore.

Related errors

• SingleStore doesn't support ANY clause
• SingleStore doesn't support ALL clause
• Can't emulate offset fetch clause in subquery
• Can't render offset and fetch clause for subquery
• Can't render offset and fetch clause for subquery

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/3d41025f1822064e. Report an issue: GitHub.