{"record":{"id":"3d41025f1822064e","repo":"hibernate/hibernate-orm","slug":"singlestore-doesn-t-support-union-union-all-with-l","errorCode":null,"errorMessage":"SingleStore doesn't support UNION/UNION ALL with limit clause","messagePattern":"SingleStore doesn't support UNION/UNION ALL with limit clause","errorType":"exception","errorClass":"UnsupportedOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SingleStoreSqlAstTranslator.java","lineNumber":327,"sourceCode":"\t\t\tassert size == rhsExpressions.size();\n\t\t\tString separator = OPEN_PARENTHESIS + \"\";\n\t\t\tfor ( int i = 0; i < size; i++ ) {\n\t\t\t\tappendSql( separator );\n\t\t\t\trenderDistinct( (Expression) lhsExpressions.get( i ), operator, (Expression) rhsExpressions.get( i ) );\n\t\t\t\tseparator = \") and (\";\n\t\t\t}\n\t\t\tappendSql( CLOSE_PARENTHESIS );\n\t\t}\n\t\telse {\n\t\t\tsuper.emulateTupleComparison( lhsExpressions, rhsExpressions, operator, indexOptimized );\n\t\t}\n\t}\n\n\t@Override\n\tprotected void renderCombinedLimitClause(Expression offsetExpression, Expression fetchExpression) {\n\t\tif ( offsetExpression != null || fetchExpression != null ) {\n\t\t\tif ( getCurrentQueryPart() instanceof QueryGroup && (((QueryGroup) getCurrentQueryPart()).getSetOperator() == SetOperator.UNION || ((QueryGroup) getCurrentQueryPart()).getSetOperator() == SetOperator.UNION_ALL) ) {\n\t\t\t\tthrow new UnsupportedOperationException(\n\t\t\t\t\t\t\"SingleStore doesn't support UNION/UNION ALL with limit clause\" );\n\t\t\t}\n\t\t}\n\t\tsuper.renderCombinedLimitClause( offsetExpression, fetchExpression );\n\t}\n\n\n\t@Override\n\tprotected void renderPartitionItem(Expression expression) {\n\t\tif ( expression instanceof Literal ) {\n\t\t\tappendSql( \"'0'\" );\n\t\t}\n\t\telse if ( expression instanceof Summarization ) {\n\t\t\tSummarization summarization = (Summarization) expression;\n\t\t\trenderCommaSeparated( summarization.getGroupings() );\n\t\t\tappendSql( \" with \" );\n\t\t\tappendSql( summarization.getKind().sqlText() );\n\t\t}","sourceCodeStart":309,"sourceCodeEnd":345,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SingleStoreSqlAstTranslator.java#L309-L345","documentation":"renderCombinedLimitClause() in SingleStoreSqlAstTranslator throws when a LIMIT/OFFSET (setMaxResults/setFirstResult) applies to a top-level query part that is a UNION or UNION ALL: SingleStore cannot apply a limit clause to a combined result set in the position Hibernate would render it. The check inspects the current QueryGroup's set operator during SQL generation, so pagination is rejected at translation time rather than by the database.","triggerScenarios":"Calling setMaxResults(...) and/or setFirstResult(...) on an HQL query whose top level is a union, e.g. 'select a.id from A a union select b.id from B b' with pagination; Pageable/pageable request handling over union queries on SingleStore.","commonSituations":"Spring Data queries with Pageable over HQL unions; search screens merging multiple sources with UNION then paginating; code that worked on MySQL (which allows LIMIT after UNION) moved to SingleStore.","solutions":["Wrap the union in an outer SELECT and paginate that instead: 'select x from (select ... union select ...) order by x' with setMaxResults on the outer query","Apply the limit to each union arm individually when arm-level capping gives the same semantics (careful with global ordering)","Materialize the union into a temp table and paginate a plain select over it","Do the pagination in memory for small result sets"],"exampleFix":"// before - pagination on top-level union (throws)\nem.createQuery('select a.id as x from A a union select b.id as x from B b order by x')\n  .setMaxResults(20).getResultList();\n\n// after - paginate the outer query\nem.createQuery('select x from (select a.id as x from A a union select b.id as x from B b) order by x')\n  .setMaxResults(20).getResultList();","handlingStrategy":"validation","validationCode":"static boolean isPaginatedUnion(String hql, int firstResult, int maxResults) {\n    String lower = hql.toLowerCase();\n    boolean topLevelUnion = lower.contains(' union ');\n    return topLevelUnion && (firstResult > 0 || maxResults != Integer.MAX_VALUE);\n}\n\nif (dialect instanceof SingleStoreDialect && isPaginatedUnion(hql, first, max)) {\n    throw new UnsupportedOperationException(\n        'Paginate the outer query that wraps the union, not the union itself');\n}","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Wrap unions in an outer select before paginating on SingleStore","Apply arm-level limits explicitly when semantics allow","Integration-test Pageable queries that contain unions on every target database"],"tags":["hibernate","singlestore","sql-translator","union","pagination","limit"],"backgroundTag":"union-limit-pagination-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}