{"record":{"id":"d3b4280e72f07322","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-s-in-clause-s-only-the-select-c","errorCode":null,"errorMessage":"Can't emulate [%s] in clause %s. Only the SELECT clause is supported","messagePattern":"Can't emulate \\[(.+?)\\] in clause (.+?)\\. Only the SELECT clause is supported","errorType":"exception","errorClass":"IllegalArgumentException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/function/HypotheticalSetWindowEmulation.java","lineNumber":72,"sourceCode":"\t\t\t\tthis,\n\t\t\t\targuments,\n\t\t\t\tfilter,\n\t\t\t\twithinGroupClause,\n\t\t\t\timpliedResultType,\n\t\t\t\tgetArgumentsValidator(),\n\t\t\t\tgetReturnTypeResolver(),\n\t\t\t\tqueryEngine.getCriteriaBuilder(),\n\t\t\t\tgetName()\n\t\t) {\n\n\t\t\t@Override\n\t\t\tpublic Expression convertToSqlAst(SqmToSqlAstConverter walker) {\n\t\t\t\tfinal Clause currentClause = walker.getCurrentClauseStack().getCurrent();\n\t\t\t\tif ( currentClause == Clause.OVER ) {\n\t\t\t\t\treturn super.convertToSqlAst( walker );\n\t\t\t\t}\n\t\t\t\telse if ( currentClause != Clause.SELECT ) {\n\t\t\t\t\tthrow new IllegalArgumentException( \"Can't emulate [\" + getName() + \"] in clause \" + currentClause + \". Only the SELECT clause is supported\" );\n\t\t\t\t}\n\t\t\t\tfinal ReturnableType<?> resultType = resolveResultType( walker );\n\n\t\t\t\tList<SqlAstNode> arguments = resolveSqlAstArguments( getArguments(), walker );\n\t\t\t\tArgumentsValidator argumentsValidator = getArgumentsValidator();\n\t\t\t\tif ( argumentsValidator != null ) {\n\t\t\t\t\targumentsValidator.validateSqlTypes( arguments, getFunctionName() );\n\t\t\t\t}\n\t\t\t\tList<SortSpecification> withinGroup;\n\t\t\t\tif ( this.getWithinGroup() == null ) {\n\t\t\t\t\twithinGroup = emptyList();\n\t\t\t\t}\n\t\t\t\telse {\n\t\t\t\t\twalker.getCurrentClauseStack().push( Clause.ORDER );\n\t\t\t\t\ttry {\n\t\t\t\t\t\tfinal List<SqmSortSpecification> sortSpecifications = this.getWithinGroup().getSortSpecifications();\n\t\t\t\t\t\twithinGroup = new ArrayList<>( sortSpecifications.size() );\n\t\t\t\t\t\tfor ( SqmSortSpecification sortSpecification : sortSpecifications ) {","sourceCodeStart":54,"sourceCodeEnd":90,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/function/HypotheticalSetWindowEmulation.java#L54-L90","documentation":"On dialects without native ordered-set aggregate support Hibernate emulates hypothetical set functions by inlining a scalar subquery. That emulation only knows how to place the subquery in the SELECT clause or an OVER window; referencing the function in any other clause (WHERE, GROUP BY, HAVING, ORDER BY, ...) is rejected at SQM-to-SQL conversion.","triggerScenarios":"HQL: ... order by percentile_cont(0.5) within group (order by x), or filtering on the function's value in WHERE/GROUP BY/HAVING, on a dialect that emulates the function.","commonSituations":"Sorting or filtering report queries by a computed percentile; MySQL-family targets where percentile functions are emulated; HQL copied from SELECT-list usage into ORDER BY during refactoring.","solutions":["Wrap the query: compute the function in an inner SELECT with an alias, then ORDER BY / filter on the alias in the outer query","Keep the emulated function strictly inside the SELECT list","Use native SQL for that query, or a dialect with native ordered-set aggregate support"],"exampleFix":"// before\nselect e.dept, percentile_cont(0.5) within group (order by e.salary) from Emp e group by e.dept order by percentile_cont(0.5) within group (order by e.salary)\n\n// after\nselect d.dept, d.med from (\n  select e.dept as dept, percentile_cont(0.5) within group (order by e.salary) as med from Emp e group by e.dept\n) d order by d.med","handlingStrategy":"fallback","validationCode":"// Emulated ordered-set aggregates may only appear in the SELECT list / OVER —\n// reject queries that reference them in other clauses before running them\nstatic boolean clauseSafeHql(String hql, String fn) {\n    String lower = hql.toLowerCase(java.util.Locale.ROOT);\n    int fnAt = lower.indexOf(fn.toLowerCase());\n    if (fnAt < 0) return true;\n    for (String kw : new String[]{\" order by \", \" where \", \" group by \", \" having \"}) {\n        int kwAt = lower.indexOf(kw);\n        if (kwAt >= 0 && lower.indexOf(fn.toLowerCase(), kwAt) >= 0) return false;\n    }\n    return true;\n}","typeGuard":null,"tryCatchPattern":"try {\n    return em.createQuery(hql, Double.class).getResultList();\n} catch (IllegalArgumentException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"Only the SELECT clause is supported\")) {\n        // re-issue with the function computed in an inner select and an outer alias reference\n        return em.createQuery(wrapInSubquery(hql), Double.class).getResultList();\n    }\n    throw e;\n}","preventionTips":["Compute emulated functions in the SELECT list and reference them by alias elsewhere","Wrap aggregates in a derived table when ordering/filtering on them","Test report queries on the emulation dialect, not only on PostgreSQL"],"tags":["hibernate","hql","window-function","ordered-set-aggregate","query-clause"],"backgroundTag":"window-function-emulation-limit","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}