{"record":{"id":"db8c1267dcb5a3d5","repo":"hibernate/hibernate-orm","slug":"database-does-not-support-the-with-ordinality-sy","errorCode":null,"errorMessage":"Database does not support the 'with ordinality' syntax for custom set-returning functions","messagePattern":"Database does not support the 'with ordinality' syntax for custom set-returning functions","errorType":"exception","errorClass":"UnsupportedOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java","lineNumber":6396,"sourceCode":"\t\t}\n\t\trenderDerivedTableReferenceIdentificationVariable( tableReference );\n\t}\n\n\t@Override\n\tpublic void visitFunctionTableReference(FunctionTableReference tableReference) {\n\t\ttableReference.getFunctionExpression().accept( this );\n\t\tif ( !tableReference.rendersIdentifierVariable() ) {\n\t\t\trenderTableReferenceIdentificationVariable( tableReference );\n\t\t}\n\t}\n\n\t@Override\n\tpublic void renderNamedSetReturningFunction(String functionName, List<? extends SqlAstNode> sqlAstArguments, AnonymousTupleTableGroupProducer tupleType, String tableIdentifierVariable, SqlAstNodeRenderingMode argumentRenderingMode) {\n\t\trenderSimpleNamedFunction( functionName, sqlAstArguments, argumentRenderingMode );\n\n\t\tif ( tupleType.findSubPart( CollectionPart.Nature.INDEX.getName(), null ) != null ) {\n\t\t\tif ( dialect.getDefaultOrdinalityColumnName() == null ) {\n\t\t\t\tthrow new UnsupportedOperationException( \"Database does not support the 'with ordinality' syntax for custom set-returning functions\" );\n\t\t\t}\n\t\t\tappendSql( \" with ordinality\" );\n\t\t}\n\t}\n\n\tprotected final void renderSimpleNamedFunction(String functionName, List<? extends SqlAstNode> sqlAstArguments, SqlAstNodeRenderingMode argumentRenderingMode) {\n\t\tappendSql( functionName );\n\t\tappendSql( '(' );\n\t\tif ( !sqlAstArguments.isEmpty() ) {\n\t\t\trender( sqlAstArguments.get( 0 ), argumentRenderingMode );\n\t\t\tfor ( int i = 1; i < sqlAstArguments.size(); i++ ) {\n\t\t\t\tappendSql( ',' );\n\t\t\t\trender( sqlAstArguments.get( i ), argumentRenderingMode );\n\t\t\t}\n\t\t}\n\t\tappendSql( ')' );\n\t}\n","sourceCodeStart":6378,"sourceCodeEnd":6414,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L6378-L6414","documentation":"When rendering a custom (user-defined) set-returning function used as a table reference, Hibernate appends PostgreSQL-style 'with ordinality' when the tuple type exposes an INDEX part (row ordinality). Only dialects whose getDefaultOrdinalityColumnName() returns non-null (PostgreSQL, H2, HSQL, CockroachDB) support this; on all others this UnsupportedOperationException is thrown.","triggerScenarios":"Calling a custom set-returning function (registered via SetReturningFunctionDescriptor / @TableFunction) in HQL whose return tuple includes the index/ordinality part (e.g. selecting f().index or using a collection-part navigation that needs INDEX) on Oracle, SQL Server, MySQL, DB2, or any dialect where getDefaultOrdinalityColumnName() is null.","commonSituations":"Using generate_series-like or unnest-like custom table functions with ordinality on non-PostgreSQL databases; migrating an HQL query that reads the index of set-returning function output between databases; Hibernate 6.x where custom set-returning functions and tuple table groups were introduced.","solutions":["Rewrite the HQL so the index/ordinality part of the function result is not referenced","Register a dialect-specific SetReturningFunctionDescriptor that emulates ordinality (e.g. wrapping output in row_number())","Use a native SQL query with the database's own ordinality emulation","Target a database whose dialect supports ordinality (PostgreSQL, H2, HSQL, CockroachDB)"],"exampleFix":"// before (on Oracle/SQLServer)\nList<Object[]> rows = session.createQuery(\n    \"select t.index, t.value from generate_series(1,10) t\", Object[].class).list();\n\n// after\nList<Object[]> rows = session.createNativeQuery(\n    \"select rownum, t.value from table(generate_series(1,10)) t\").list();","handlingStrategy":"validation","validationCode":"// Check dialect capability before referencing the index of a set-returning function\nboolean ordinalitySupported = ((Dialect) sessionFactory.getJdbcServices().getDialect())\n        .getDefaultOrdinalityColumnName() != null;\nif (!ordinalitySupported && queryNeedsOrdinality(hql)) {\n    // drop the index selection or use native SQL\n}","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Do not select the INDEX part of custom set-returning function results on non-PostgreSQL databases","Provide dialect-specific SetReturningFunctionDescriptors when a function must emulate ordinality","Document which databases each custom table function supports"],"tags":["hibernate","set-returning-function","ordinality","table-function","dialect-support"],"backgroundTag":"set-returning-function-ordinality","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}