{"record":{"id":"a7570ab9fbdb1d3e","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-offset-clause-in-subquery","errorCode":null,"errorMessage":"Can't emulate offset clause in subquery","messagePattern":"Can't emulate offset clause in subquery","errorType":"exception","errorClass":"IllegalArgumentException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/CacheSqlAstTranslator.java","lineNumber":71,"sourceCode":"\t@Override\n\tprotected void renderTopClause(QuerySpec querySpec, boolean addOffset, boolean needsParenthesis) {\n\t\tassertRowsOnlyFetchClauseType( querySpec );\n\t\tsuper.renderTopClause( querySpec, addOffset, needsParenthesis );\n\t}\n\n\t@Override\n\tprotected void renderFetchPlusOffsetExpression(\n\t\t\tExpression fetchClauseExpression,\n\t\t\tExpression offsetClauseExpression,\n\t\t\tint offset) {\n\t\trenderFetchPlusOffsetExpressionAsSingleParameter( fetchClauseExpression, offsetClauseExpression, offset );\n\t}\n\n\t@Override\n\tpublic void visitOffsetFetchClause(QueryPart queryPart) {\n\t\t// Cache only supports the TOP clause\n\t\tif ( !queryPart.isRoot() && queryPart.getOffsetClauseExpression() != null ) {\n\t\t\tthrow new IllegalArgumentException( \"Can't emulate offset clause in subquery\" );\n\t\t}\n\t}\n\n\t@Override\n\tprotected void renderComparison(Expression lhs, ComparisonOperator operator, Expression rhs) {\n\t\trenderComparisonEmulateIntersect( lhs, operator, rhs );\n\t}\n\n\t@Override\n\tprotected void renderSelectTupleComparison(\n\t\t\tList<SqlSelection> lhsExpressions,\n\t\t\tSqlTuple tuple,\n\t\t\tComparisonOperator operator) {\n\t\temulateSelectTupleComparison( lhsExpressions, tuple.getExpressions(), operator, true );\n\t}\n\n\t@Override\n\tprotected void renderPartitionItem(Expression expression) {","sourceCodeStart":53,"sourceCodeEnd":89,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/CacheSqlAstTranslator.java#L53-L89","documentation":"DerivedTableReference wraps a derived from-element (subquery select item, VALUES clause, lateral subquery) which has no physical tables at all. Both resolveTableReference overloads unconditionally throw UnknownTableReferenceException: you cannot ask a subquery for a named base table. Hitting it means query translation tried to resolve a physical table relative to a derived table - usually locking, identity, or column-resolution logic that only makes sense against real tables.","triggerScenarios":"Applying pessimistic locking (SELECT ... FOR UPDATE) or otherwise resolving physical tables against a derived from-element; entity mappings based on subqueries/values where later SQL AST phases request base-table resolution inside the derived reference; path expressions pushed below a lateral/derived boundary by a translator.","commonSituations":"Entities mapped via @Subselect or query-space derived tables combined with locking or native follow-up statements; CTE-referenced entities on dialect paths that resolve columns through the CTE's derived reference; custom dialect translators reordering resolution.","solutions":["Remove operations that require physical tables (locking, native updates by table) from queries touching derived-mapped entities.","Map the entity to a real table or a database view instead of a subselect where physical resolution is needed.","Restructure so column resolution targets the outer table group rather than going through the derived reference.","For CTE-based entities, run against dialects/configurations that keep resolution at the CTE label level, and upgrade Hibernate."],"exampleFix":"// before: locking a subselect-mapped entity needs a physical table\nList<V> rows = em.createQuery(\"select v from OrderView v where v.status = :s\", OrderView.class)\n    .setLockMode(LockModeType.PESSIMISTIC_WRITE) // OrderView is @Subselect-mapped\n    .getResultList();\n\n// after: lock the real entity, or read the view without locking\nList<Order> rows = em.createQuery(\"select o from Order o where o.status = :s\", Order.class)\n    .setLockMode(LockModeType.PESSIMISTIC_WRITE)\n    .getResultList();","handlingStrategy":"validation","validationCode":"// Reject locking on derived-mapped entities before executing\nboolean derivedMapped = \"Subselect\".equals(\n    jpaEntityMeta.getAnnotation( \"org.hibernate.annotations.Subselect\" ) != null ? \"Subselect\" : null );\nif ( hasLockMode && entityAnnotatedWithSubselect( entityClass ) ) {\n    throw new IllegalArgumentException(\"Cannot lock a subselect-mapped entity: \" + entityClass);\n}","typeGuard":null,"tryCatchPattern":"try {\n    return em.createQuery(hql, type).setLockMode( LockModeType.PESSIMISTIC_WRITE ).getResultList();\n} catch ( UnknownTableReferenceException e ) {\n    if ( e.getMessage() != null && e.getMessage().contains(\"DerivedTableReferences\") ) {\n        // lock the real entity or re-read without locking\n        return em.createQuery(hql, type).getResultList();\n    }\n    throw e;\n}","preventionTips":["Never apply pessimistic locking or table-level operations to @Subselect/derived-mapped entities.","Map read models that need locking to real tables or updatable views.","Smoke-test derived-mapped entities against every query path used by the app."],"tags":["derived-table","subselect","locking","sql-ast","table-reference"],"backgroundTag":"unknown-table-reference","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}