{"record":{"id":"242116b7a19347b0","repo":"hibernate/hibernate-orm","slug":"mode-function-requires-a-within-group-clause-with","errorCode":null,"errorMessage":"MODE function requires a WITHIN GROUP clause with exactly one order by item","messagePattern":"MODE function requires a WITHIN GROUP clause with exactly one order by item","errorType":"exception","errorClass":"IllegalArgumentException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/function/ModeStatsModeEmulation.java","lineNumber":44,"sourceCode":"\t\tsuper(\n\t\t\t\tFUNCTION_NAME,\n\t\t\t\tnull,\n\t\t\t\ttypeConfiguration\n\t\t);\n\t}\n\n\t@Override\n\tpublic void render(\n\t\t\tSqlAppender sqlAppender,\n\t\t\tList<? extends SqlAstNode> sqlAstArguments,\n\t\t\tPredicate filter,\n\t\t\tList<SortSpecification> withinGroup,\n\t\t\tReturnableType<?> returnType,\n\t\t\tSqlAstTranslator<?> translator) {\n\t\tfinal boolean caseWrapper = filter != null && !filterClauseSupported( translator );\n\t\tsqlAppender.appendSql( \"stats_mode(\" );\n\t\tif ( withinGroup == null || withinGroup.size() != 1 ) {\n\t\t\tthrow new IllegalArgumentException( \"MODE function requires a WITHIN GROUP clause with exactly one order by item\" );\n\t\t}\n\t\tif ( caseWrapper ) {\n\t\t\ttranslator.getCurrentClauseStack().push( Clause.WHERE );\n\t\t\tsqlAppender.appendSql( \"case when \" );\n\t\t\tfilter.accept( translator );\n\t\t\ttranslator.getCurrentClauseStack().pop();\n\t\t\tsqlAppender.appendSql( \" then \" );\n\t\t\ttranslator.getCurrentClauseStack().push( Clause.WITHIN_GROUP );\n\t\t\twithinGroup.get( 0 ).accept( translator );\n\t\t\tsqlAppender.appendSql( \" else null end)\" );\n\t\t\ttranslator.getCurrentClauseStack().pop();\n\t\t}\n\t\telse {\n\t\t\ttranslator.getCurrentClauseStack().push( Clause.WITHIN_GROUP );\n\t\t\twithinGroup.get( 0 ).accept( translator );\n\t\t\ttranslator.getCurrentClauseStack().pop();\n\t\t\tsqlAppender.appendSql( ')' );\n\t\t\tif ( filter != null ) {","sourceCodeStart":26,"sourceCodeEnd":62,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/function/ModeStatsModeEmulation.java#L26-L62","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Use exactly one ORDER BY expression inside WITHIN GROUP: mode() within group (order by a)","Pick the single deterministic tie-break key that matters for the mode computation","Compute multi-key modes with native SQL or in application code"],"exampleFix":"// before\nselect mode() within group (order by e.dept, e.rank) from Evaluation e\n\n// after\nselect mode() within group (order by e.dept) from Evaluation e","handlingStrategy":"validation","validationCode":"// stats_mode-based MODE needs exactly one WITHIN GROUP order item\nstatic void checkModeArity(java.util.List<String> orderItems) {\n    if (orderItems.size() != 1) {\n        throw new IllegalStateException(\n            \"mode() within group needs exactly 1 order item, got \" + orderItems.size());\n    }\n}","typeGuard":null,"tryCatchPattern":"try {\n    return em.createQuery(hql, Object.class).getSingleResult();\n} catch (IllegalArgumentException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"exactly one order by item\")) {\n        throw new QuerySetupException(\"Reduce WITHIN GROUP to a single ORDER BY item for mode()\", e);\n    }\n    throw e;\n}","preventionTips":["Model mode() with exactly one deterministic sort key from the start","Generate WITHIN GROUP clauses from a structured representation, not string concat","Watch for ORDER BY lists blindly reused inside WITHIN GROUP"],"tags":["hibernate","hql","mode","within-group","oracle","ordered-set-aggregate"],"backgroundTag":"ordered-set-aggregate-arity","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}