hibernate/hibernate-orm · error · QueryException

Can't emulate null on error clause on H2

Error message

Can't emulate null on error clause on H2

What it means

H2's json_table() emulation (system_range unnest plus lateral joins) propagates JSON parse and path errors, so only the default ERROR ON ERROR behavior can be emulated. Asking for null on error cannot be rendered, and H2JsonTableFunction.renderJsonTable() throws while the query plan is generated.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/function/json/H2JsonTableFunction.java:432

			}
		}
	}

	@Override
	public boolean rendersIdentifierVariable(List<SqlAstNode> arguments, SessionFactoryImplementor sessionFactory) {
		// To make our lives simpler when supporting non-column JSON document arguments
		return true;
	}

	@Override
	protected void renderJsonTable(
			SqlAppender sqlAppender,
			JsonTableArguments arguments,
			AnonymousTupleTableGroupProducer tupleType,
			String tableIdentifierVariable,
			SqlAstTranslator<?> walker) {
		if ( arguments.errorBehavior() == JsonTableErrorBehavior.NULL ) {
			throw new QueryException( "Can't emulate null on error clause on H2" );
		}

		final Expression jsonPathExpression = arguments.jsonPath();
		final boolean isArray = isArrayAccess( jsonPathExpression, walker );

		if ( arguments.jsonDocument().getColumnReference() == null ) {
			sqlAppender.append( '(' );
		}
		if ( isArray ) {
			sqlAppender.append( "system_range(1," );
			sqlAppender.append( Integer.toString( maximumArraySize ) );
			sqlAppender.append( ") " );
		}
		else {
			sqlAppender.append( "system_range(1,1) " );
		}
		sqlAppender.append( tableIdentifierVariable );
		if ( arguments.jsonDocument().getColumnReference() == null ) {

View on GitHub (pinned to fad1729dce)

Solutions

1. Remove null on error - the default ERROR ON ERROR is emulated
2. Pre-filter invalid documents before the json_table query (e.g. where d.doc is json)
3. Validate JSON at write time so queries can assume well-formed documents
4. Run these tests with Testcontainers against a database that supports the clause

Example fix

// before - throws on H2
select t.name from Document d, json_table(d.doc, '$' null on error columns(name varchar)) t

// after - default ERROR ON ERROR; ensure documents are valid
select t.name from Document d, json_table(d.doc, '$' columns(name varchar)) t

// Reject NULL ON ERROR for json_table on H2 before execution
static void assertTranslatable(SessionFactory sf, String hql) {
    if (sf.getJdbcServices().getDialect() instanceof org.hibernate.dialect.H2Dialect
            && hql.toLowerCase().contains("null on error")) {
        throw new IllegalArgumentException(
            "H2 json_table emulation only supports the default ERROR ON ERROR; pre-validate documents");
    }
}

try {
    return session.createQuery(hql, Object[].class).getResultList();
} catch (org.hibernate.QueryException e) {
    if (e.getMessage() != null && e.getMessage().contains("null on error clause on H2")) {
        return session.createQuery(stripClause(hql, "null on error"), Object[].class).getResultList();
    }
    throw e;
}

When it happens

Trigger: HQL on the H2 dialect where the json_table error clause uses NULL: select t.name from Document d, json_table(d.doc, '$' null on error columns(name varchar)) t.

Common situations: Defensive NULL ON ERROR written for feeds with possibly malformed documents; H2-based unit tests failing after the clause was added for production databases; switching a test suite from PostgreSQL to H2.

Related errors

• Can't emulate null on error clause on DB2
• Can't emulate on error clause on H2
• Can't emulate error on empty clause on H2
• Can't emulate on error clause on H2
• Can't emulate error on empty clause on H2

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