hibernate/hibernate-orm · error · QueryException

Can't emulate on error clause on H2

Error message

Can't emulate on error clause on H2

What it means

H2 lacks native json_value(); Hibernate emulates it with dereference expressions, which raise an error on invalid JSON by nature. Only the default ERROR ON ERROR behavior is emulatable - null on error and default <expr> on error are rejected during SQL rendering. Note the asymmetry: default <expr> on empty IS supported on H2 (rendered via coalesce).

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/function/json/H2JsonValueFunction.java:45

/**
 * H2 json_value function.
 */
public class H2JsonValueFunction extends JsonValueFunction {

	public H2JsonValueFunction(TypeConfiguration typeConfiguration) {
		super( typeConfiguration, false, true );
	}

	@Override
	protected void render(
			SqlAppender sqlAppender,
			JsonValueArguments arguments,
			ReturnableType<?> returnType,
			SqlAstTranslator<?> walker) {
		// Json dereference errors by default if the JSON is invalid
		if ( arguments.errorBehavior() != null && arguments.errorBehavior() != JsonValueErrorBehavior.ERROR ) {
			throw new QueryException( "Can't emulate on error clause on H2" );
		}
		if ( arguments.emptyBehavior() == JsonValueEmptyBehavior.ERROR ) {
			throw new QueryException( "Can't emulate error on empty clause on H2" );
		}
		final Expression defaultExpression = arguments.emptyBehavior() == null
				? null
				: arguments.emptyBehavior().getDefaultExpression();
		if ( defaultExpression != null ) {
			sqlAppender.appendSql( "coalesce(" );
		}
		final boolean hexDecoding;
		if ( arguments.returningType() != null ) {
			hexDecoding = H2JsonValueFunction.needsHexDecoding( arguments.returningType().getJdbcMapping() );
			sqlAppender.appendSql( "cast(" );
			if ( hexDecoding ) {
				// We encode binary data as hex, so we have to decode here
				sqlAppender.appendSql( "hextoraw(regexp_replace(" );
			}

View on GitHub (pinned to fad1729dce)

Solutions

1. Drop the on error clause - the default ERROR behavior is what the emulation implements
2. Keep documents valid (write-time validation or where d.doc is json) so errors cannot occur
3. Run these tests with Testcontainers against the production database
4. Use a native query when lenient error handling is required

Example fix

// before - throws on H2
select json_value(d.doc, '$.score' returning integer null on error) from Document d

// after - default ERROR ON ERROR; ensure documents are valid
select json_value(d.doc, '$.score' returning integer) from Document d

// Reject non-default ON ERROR clauses for json_value on H2 before execution
static void assertTranslatable(SessionFactory sf, String hql) {
    if (sf.getJdbcServices().getDialect() instanceof org.hibernate.dialect.H2Dialect) {
        String h = hql.toLowerCase();
        if (h.contains("json_value")) {
            int i = h.indexOf("on error");
            if (i >= 0 && !h.startsWith("error on error", i)) {
                throw new IllegalArgumentException(
                    "H2 json_value only supports the default 'error on error'; remove the clause");
            }
        }
    }
}

try {
    return session.createQuery(hql, Integer.class).getSingleResult();
} catch (org.hibernate.QueryException e) {
    if (e.getMessage() != null && e.getMessage().contains("on error clause on H2")) {
        // Retry with default ERROR ON ERROR; 'default x on empty' is still allowed on H2
        return session.createQuery(stripClause(hql, "on error"), Integer.class).getSingleResult();
    }
    throw e;
}

When it happens

Trigger: HQL on the H2 dialect: select json_value(d.doc, '$.score' returning integer null on error) from Document d, or json_value(d.doc, '$.score' default 0 on error). JsonValueErrorBehavior values other than ERROR throw in H2JsonValueFunction.render().

Common situations: Defensive NULL/DEFAULT ON ERROR clauses written against Oracle or SQL Server semantics, then executed under the H2 unit-test profile; quickstart apps defaulting to in-memory H2 while production uses another database.

Related errors

• Can't emulate error on empty clause on H2
• Can't emulate on error clause on CockroachDB
• Can't emulate on empty clause on CockroachDB
• 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/a9b8caa6c9642938. Report an issue: GitHub.