{"record":{"id":"4cd02271690c9526","repo":"hibernate/hibernate-orm","slug":"collate-is-not-allowed-for-alias-based-order-by","errorCode":null,"errorMessage":"'collate' is not allowed for alias-based 'order by' or 'group by' items","messagePattern":"'collate' is not allowed for alias-based 'order by' or 'group by' items","errorType":"exception","errorClass":"SyntaxException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/query/hql/internal/SemanticQueryBuilder.java","lineNumber":1708,"sourceCode":"\t\t\t\t}\n\t\t\t\telse if ( sqmPosition != 0 ) {\n\t\t\t\t\treturn new SqmAliasedNodeRef(\n\t\t\t\t\t\t\tsqmPosition,\n\t\t\t\t\t\t\tnodeBuilder.getIntegerType(),\n\t\t\t\t\t\t\tnodeBuilder\n\t\t\t\t\t);\n\t\t\t\t}\n\t\t\t}\n\t\t\telse {\n\t\t\t\tfinal Integer correspondingPosition =\n\t\t\t\t\t\tallowPositionalOrAliases\n\t\t\t\t\t\t\t\t? processingState.getPathRegistry()\n\t\t\t\t\t\t\t\t\t\t.findAliasedNodePosition( identifierText )\n\t\t\t\t\t\t\t\t: null;\n\t\t\t\tif ( correspondingPosition != null ) {\n\t\t\t\t\tif ( definedCollate ) {\n\t\t\t\t\t\t// This is syntactically disallowed\n\t\t\t\t\t\tthrow new SyntaxException( \"'collate' is not allowed for alias-based 'order by' or 'group by' items\" );\n\t\t\t\t\t}\n\t\t\t\t\treturn new SqmAliasedNodeRef(\n\t\t\t\t\t\t\tcorrespondingPosition,\n\t\t\t\t\t\t\tnodeBuilder.resolveExpressible( integerDomainType ),\n\t\t\t\t\t\t\tnodeBuilder\n\t\t\t\t\t);\n\t\t\t\t}\n\n\t\t\t\tfinal var sqmFrom =\n\t\t\t\t\t\tprocessingState.getPathRegistry()\n\t\t\t\t\t\t\t\t.findFromByAlias( identifierText, true );\n\t\t\t\tif ( sqmFrom != null ) {\n\t\t\t\t\tif ( definedCollate ) {\n\t\t\t\t\t\t// This is syntactically disallowed\n\t\t\t\t\t\tthrow new SyntaxException( \"'collate' is not allowed for alias-based 'order by' or 'group by' items\" );\n\t\t\t\t\t}\n\t\t\t\t\t// this will group-by all the sub-parts in the from-element's model part\n\t\t\t\t\treturn sqmFrom;","sourceCodeStart":1690,"sourceCodeEnd":1726,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/query/hql/internal/SemanticQueryBuilder.java#L1690-L1726","documentation":"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.","triggerScenarios":"'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.","commonSituations":"Case-insensitive or locale-aware sorting written on an alias for convenience; queries ported from databases whose SQL allows COLLATE on select aliases.","solutions":["Order/group by the full expression with collate: 'order by e.name collate \"...\"' instead of the alias","Drop the collate clause if the column/database default collation already gives the wanted ordering","Set the collation at the column or schema level so queries need no collate at all"],"exampleFix":"// before\nselect e.name as n from Employee e order by n collate \"de\"\n\n// after\nselect e.name as n from Employee e order by e.name collate \"de\"","handlingStrategy":"validation","validationCode":"// Reject 'collate' attached to a select alias before sending the query\nstatic boolean collateOnAlias(String hql, Set<String> selectAliases) {\n    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);\n    if (!m.find()) return false;\n    String tail = m.group(2).toLowerCase();\n    for (String alias : selectAliases) {\n        if (tail.matches(\"(?s).*\\\\b\" + java.util.regex.Pattern.quote(alias.toLowerCase()) + \"\\\\s+collate\\\\s+.*\")) return true;\n    }\n    return false;\n}","typeGuard":null,"tryCatchPattern":"try {\n    return em.createQuery(hql, Employee.class).getResultList();\n} catch (org.hibernate.query.SyntaxException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"collate\")) {\n        // rewrite: move collate onto the underlying expression and retry once\n        return em.createQuery(rewriteCollateOntoExpression(hql), Employee.class).getResultList();\n    }\n    throw e;\n}","preventionTips":["Keep collate next to the real expression, never on an alias or ordinal","Prefer database-level collation when every query needs the same sort order"],"tags":["hql","collate","order-by","group-by","syntax"],"backgroundTag":"collation-not-supported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}