hibernate/hibernate-orm · error · UnsupportedOperationException

Database does not support the 'with ordinality' syntax for c

Error message

Database does not support the 'with ordinality' syntax for custom set-returning functions

What it means

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.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java:6396

		}
		renderDerivedTableReferenceIdentificationVariable( tableReference );
	}

	@Override
	public void visitFunctionTableReference(FunctionTableReference tableReference) {
		tableReference.getFunctionExpression().accept( this );
		if ( !tableReference.rendersIdentifierVariable() ) {
			renderTableReferenceIdentificationVariable( tableReference );
		}
	}

	@Override
	public void renderNamedSetReturningFunction(String functionName, List<? extends SqlAstNode> sqlAstArguments, AnonymousTupleTableGroupProducer tupleType, String tableIdentifierVariable, SqlAstNodeRenderingMode argumentRenderingMode) {
		renderSimpleNamedFunction( functionName, sqlAstArguments, argumentRenderingMode );

		if ( tupleType.findSubPart( CollectionPart.Nature.INDEX.getName(), null ) != null ) {
			if ( dialect.getDefaultOrdinalityColumnName() == null ) {
				throw new UnsupportedOperationException( "Database does not support the 'with ordinality' syntax for custom set-returning functions" );
			}
			appendSql( " with ordinality" );
		}
	}

	protected final void renderSimpleNamedFunction(String functionName, List<? extends SqlAstNode> sqlAstArguments, SqlAstNodeRenderingMode argumentRenderingMode) {
		appendSql( functionName );
		appendSql( '(' );
		if ( !sqlAstArguments.isEmpty() ) {
			render( sqlAstArguments.get( 0 ), argumentRenderingMode );
			for ( int i = 1; i < sqlAstArguments.size(); i++ ) {
				appendSql( ',' );
				render( sqlAstArguments.get( i ), argumentRenderingMode );
			}
		}
		appendSql( ')' );
	}

View on GitHub (pinned to fad1729dce)

Solutions

1. Rewrite the HQL so the index/ordinality part of the function result is not referenced
2. Register a dialect-specific SetReturningFunctionDescriptor that emulates ordinality (e.g. wrapping output in row_number())
3. Use a native SQL query with the database's own ordinality emulation
4. Target a database whose dialect supports ordinality (PostgreSQL, H2, HSQL, CockroachDB)

Example fix

// before (on Oracle/SQLServer)
List<Object[]> rows = session.createQuery(
    "select t.index, t.value from generate_series(1,10) t", Object[].class).list();

// after
List<Object[]> rows = session.createNativeQuery(
    "select rownum, t.value from table(generate_series(1,10)) t").list();

// Check dialect capability before referencing the index of a set-returning function
boolean ordinalitySupported = ((Dialect) sessionFactory.getJdbcServices().getDialect())
        .getDefaultOrdinalityColumnName() != null;
if (!ordinalitySupported && queryNeedsOrdinality(hql)) {
    // drop the index selection or use native SQL
}

When it happens

Trigger: 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.

Common situations: 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.

Related errors

• FROM_FUNCTION
• Passed `invariantType` for function return cannot be null
• Passed `selectionExpression` for function return cannot be n
• Passed `component` for function return cannot be null
• Lateral joins can only be left or inner. Illegal join type:

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/db8c1267dcb5a3d5. Report an issue: GitHub.