hibernate/hibernate-orm · error · IllegalArgumentException

Can't emulate filter clause for inverse distribution functio

Error message

Can't emulate filter clause for inverse distribution function [%s]

What it means

HypotheticalSetFunction renders ordered-set aggregates (percentile_cont, percentile_disc and friends). The SQL FILTER clause on aggregates cannot always be translated: when a filter predicate is present and the target dialect's translator does not support the FILTER clause, this renderer has no emulation for hypothetical set functions and throws during SQL rendering.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/function/HypotheticalSetFunction.java:68

	public void render(
			SqlAppender sqlAppender,
			List<? extends SqlAstNode> sqlAstArguments,
			Predicate filter,
			ReturnableType<?> returnType,
			SqlAstTranslator<?> walker) {
		render( sqlAppender, sqlAstArguments, filter, Collections.emptyList(), returnType, walker );
	}

	@Override
	public void render(
			SqlAppender sqlAppender,
			List<? extends SqlAstNode> sqlAstArguments,
			Predicate filter,
			List<SortSpecification> withinGroup,
			ReturnableType<?> returnType,
			SqlAstTranslator<?> translator) {
		if ( filter != null && !filterClauseSupported( translator ) ) {
			throw new IllegalArgumentException( "Can't emulate filter clause for inverse distribution function [" + getName() + "]" );
		}
		sqlAppender.appendSql( getName() );
		sqlAppender.appendSql( '(' );
		if ( !sqlAstArguments.isEmpty() ) {
			sqlAstArguments.get( 0 ).accept( translator );
			for ( int i = 1; i < sqlAstArguments.size(); i++ ) {
				sqlAppender.append( ',' );
				sqlAstArguments.get( i ).accept( translator );
			}
		}
		sqlAppender.appendSql( ')' );
		if ( withinGroup != null && !withinGroup.isEmpty() ) {
			translator.getCurrentClauseStack().push( Clause.WITHIN_GROUP );
			sqlAppender.appendSql( " within group (order by " );
			withinGroup.get( 0 ).accept( translator );
			for ( int i = 1; i < withinGroup.size(); i++ ) {
				sqlAppender.appendSql( ',' );
				withinGroup.get( i ).accept( translator );

View on GitHub (pinned to fad1729dce)

Solutions

1. Move the FILTER predicate into the WHERE clause of the subquery feeding the aggregate
2. Select the filtered rows in a derived table and apply the ordered-set aggregate on top of it
3. Drop FILTER when the semantics allow folding it into the main WHERE clause
4. Run the query as native SQL, or target a dialect with native FILTER support

Example fix

// before
select percentile_cont(0.5) within group (order by s.amount) filter (where s.status = 'OK') from Sale s

// after
select percentile_cont(0.5) within group (order by t.amount) from (
  select s.amount as amount from Sale s where s.status = 'OK'
) t

// Only use FILTER on ordered-set aggregates for translators that support the clause
boolean filterSupported(SessionFactory sf) {
    String dialect = sf.getJdbcServices().getDialect().getClass().getSimpleName();
    return dialect.contains("PostgreSQL") || dialect.contains("H2"); // extend per your verified matrix
}

try {
    return em.createQuery(hqlWithFilter, Double.class).getResultList();
} catch (IllegalArgumentException e) {
    if (e.getMessage() != null && e.getMessage().contains("emulate filter clause")) {
        // predicate moved from FILTER into the feeding subquery's WHERE
        return em.createQuery(hqlWithWhere, Double.class).getResultList();
    }
    throw e;
}

When it happens

Trigger: HQL: percentile_cont(0.5) within group (order by x) filter (where e.type = 'A') executed on a dialect that reports no FILTER support (typical MySQL/MariaDB bases without native FILTER).

Common situations: Porting analytics HQL from PostgreSQL to MySQL-family databases; using HQL filter clauses generated by query DSLs; upgrading a dialect where FILTER emulation was previously absent but tolerated.

Related errors

• Can't emulate filter clause for inverse distribution functio
• Can't emulate [%s] in clause %s. Only the SELECT clause is s
• Inverse distribution function '%s' must specify 'WITHIN GROU
• MODE function requires a WITHIN GROUP clause with exactly on
• The function {name} is not an aggregate function

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