hibernate/hibernate-orm · error · SyntaxException

'collate' is not allowed for alias-based 'order by' or 'grou

Error message

'collate' is not allowed for alias-based 'order by' or 'group by' items

What it means

When an 'order by'/'group by' identifier matches a select-item alias, Hibernate rewrites it into a positional SqmAliasedNodeRef (findAliasedNodePosition succeeded). A 'collate' specification cannot be attached to that indirect positional reference, so SyntaxException is thrown; collation must be declared on the underlying expression itself.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/query/hql/internal/SemanticQueryBuilder.java:1708

				}
				else if ( sqmPosition != 0 ) {
					return new SqmAliasedNodeRef(
							sqmPosition,
							nodeBuilder.getIntegerType(),
							nodeBuilder
					);
				}
			}
			else {
				final Integer correspondingPosition =
						allowPositionalOrAliases
								? processingState.getPathRegistry()
										.findAliasedNodePosition( identifierText )
								: null;
				if ( correspondingPosition != null ) {
					if ( definedCollate ) {
						// This is syntactically disallowed
						throw new SyntaxException( "'collate' is not allowed for alias-based 'order by' or 'group by' items" );
					}
					return new SqmAliasedNodeRef(
							correspondingPosition,
							nodeBuilder.resolveExpressible( integerDomainType ),
							nodeBuilder
					);
				}

				final var sqmFrom =
						processingState.getPathRegistry()
								.findFromByAlias( identifierText, true );
				if ( sqmFrom != null ) {
					if ( definedCollate ) {
						// This is syntactically disallowed
						throw new SyntaxException( "'collate' is not allowed for alias-based 'order by' or 'group by' items" );
					}
					// this will group-by all the sub-parts in the from-element's model part
					return sqmFrom;

View on GitHub (pinned to fad1729dce)

Solutions

  1. Order/group by the full expression with collate: 'order by e.name collate "..."' instead of the alias
  2. Drop the collate clause if the column/database default collation already gives the wanted ordering
  3. Set the collation at the column or schema level so queries need no collate at all

Example fix

// before
select e.name as n from Employee e order by n collate "de"

// after
select e.name as n from Employee e order by e.name collate "de"
Defensive patterns

Strategy: validation

Validate before calling

// Reject 'collate' attached to a select alias before sending the query
static boolean collateOnAlias(String hql, Set<String> selectAliases) {
    java.util.regex.Matcher m = java.util.regex.Pattern.compile("\\b(order\\s+by|group\\s+by)\\b(.*)$", java.util.regex.Pattern.DOTALL | java.util.regex.Pattern.CASE_INSENSITIVE).matcher(hql);
    if (!m.find()) return false;
    String tail = m.group(2).toLowerCase();
    for (String alias : selectAliases) {
        if (tail.matches("(?s).*\\b" + java.util.regex.Pattern.quote(alias.toLowerCase()) + "\\s+collate\\s+.*")) return true;
    }
    return false;
}

Try / catch

try {
    return em.createQuery(hql, Employee.class).getResultList();
} catch (org.hibernate.query.SyntaxException e) {
    if (e.getMessage() != null && e.getMessage().contains("collate")) {
        // rewrite: move collate onto the underlying expression and retry once
        return em.createQuery(rewriteCollateOntoExpression(hql), Employee.class).getResultList();
    }
    throw e;
}

Prevention

When it happens

Trigger: 'select e.name as n from Employee e order by n collate "latin1"' or 'group by n collate ...' where n is a select-item alias; any collated alias reference in order by or group by.

Common situations: Case-insensitive or locale-aware sorting written on an alias for convenience; queries ported from databases whose SQL allows COLLATE on select aliases.

Related errors

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