hibernate/hibernate-orm · error · IllegalArgumentException

MODE function requires a WITHIN GROUP clause with exactly on

Error message

MODE function requires a WITHIN GROUP clause with exactly one order by item

What it means

On dialects where MODE is emulated with Oracle's stats_mode(), the WITHIN GROUP clause must reduce to exactly one sort specification, because stats_mode() takes a single measure argument. Zero items or more than one order-by item throw IllegalArgumentException at render time — after stats_mode( has already been appended.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/function/ModeStatsModeEmulation.java:44

		super(
				FUNCTION_NAME,
				null,
				typeConfiguration
		);
	}

	@Override
	public void render(
			SqlAppender sqlAppender,
			List<? extends SqlAstNode> sqlAstArguments,
			Predicate filter,
			List<SortSpecification> withinGroup,
			ReturnableType<?> returnType,
			SqlAstTranslator<?> translator) {
		final boolean caseWrapper = filter != null && !filterClauseSupported( translator );
		sqlAppender.appendSql( "stats_mode(" );
		if ( withinGroup == null || withinGroup.size() != 1 ) {
			throw new IllegalArgumentException( "MODE function requires a WITHIN GROUP clause with exactly one order by item" );
		}
		if ( caseWrapper ) {
			translator.getCurrentClauseStack().push( Clause.WHERE );
			sqlAppender.appendSql( "case when " );
			filter.accept( translator );
			translator.getCurrentClauseStack().pop();
			sqlAppender.appendSql( " then " );
			translator.getCurrentClauseStack().push( Clause.WITHIN_GROUP );
			withinGroup.get( 0 ).accept( translator );
			sqlAppender.appendSql( " else null end)" );
			translator.getCurrentClauseStack().pop();
		}
		else {
			translator.getCurrentClauseStack().push( Clause.WITHIN_GROUP );
			withinGroup.get( 0 ).accept( translator );
			translator.getCurrentClauseStack().pop();
			sqlAppender.appendSql( ')' );
			if ( filter != null ) {

View on GitHub (pinned to fad1729dce)

Solutions

1. Use exactly one ORDER BY expression inside WITHIN GROUP: mode() within group (order by a)
2. Pick the single deterministic tie-break key that matters for the mode computation
3. Compute multi-key modes with native SQL or in application code

Example fix

// before
select mode() within group (order by e.dept, e.rank) from Evaluation e

// after
select mode() within group (order by e.dept) from Evaluation e

// stats_mode-based MODE needs exactly one WITHIN GROUP order item
static void checkModeArity(java.util.List<String> orderItems) {
    if (orderItems.size() != 1) {
        throw new IllegalStateException(
            "mode() within group needs exactly 1 order item, got " + orderItems.size());
    }
}

try {
    return em.createQuery(hql, Object.class).getSingleResult();
} catch (IllegalArgumentException e) {
    if (e.getMessage() != null && e.getMessage().contains("exactly one order by item")) {
        throw new QuerySetupException("Reduce WITHIN GROUP to a single ORDER BY item for mode()", e);
    }
    throw e;
}

When it happens

Trigger: HQL: mode() within group (order by a, b) (two sort items), or mode() with a missing/empty WITHIN GROUP clause on a dialect using the stats_mode emulation.

Common situations: Multi-key 'group by then mode' analytics ported from other engines; generated HQL that reuses an ORDER BY list inside WITHIN GROUP; Oracle compatibility modes where users expect full MODE syntax.

Related errors

• Inverse distribution function '%s' must specify 'WITHIN GROU
• The function {name} is not an ordered set-aggregate function
• Can't emulate filter clause for inverse distribution functio
• Can't emulate [%s] in clause %s. Only the SELECT clause is s
• Insert conflict 'do update' clause with constraint name is n

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