hibernate/hibernate-orm · error · QueryException

Can't emulate error on empty clause on H2

Error message

Can't emulate error on empty clause on H2

What it means

For json_value() on H2, ERROR ON EMPTY has no emulation: the dereference-based rendering naturally returns NULL for a missing path, and raising an error instead cannot be expressed. The translator accepts NULL ON EMPTY (the default) and DEFAULT <expr> ON EMPTY (rendered via coalesce), and throws only when emptyBehavior() == JsonValueEmptyBehavior.ERROR.

Source

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

 */
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(" );
			}
		}
		else {
			hexDecoding = false;

View on GitHub (pinned to fad1729dce)

Solutions

1. Remove error on empty and rely on the default NULL ON EMPTY
2. If you need a default, keep the supported form: json_value(d.doc, '$.nick' default 'n/a' on empty)
3. Check for null in application code instead of asking the database to raise
4. Run these queries against the production database in tests via Testcontainers

Example fix

// before - throws on H2
select json_value(d.doc, '$.nick' error on empty) from Document d

// after - the default-expression form is supported on H2
select json_value(d.doc, '$.nick' default 'n/a' on empty) from Document d

// Reject ERROR ON EMPTY for json_value on H2 before execution
static void assertTranslatable(SessionFactory sf, String hql) {
    if (sf.getJdbcServices().getDialect() instanceof org.hibernate.dialect.H2Dialect
            && hql.toLowerCase().contains("error on empty")) {
        throw new IllegalArgumentException(
            "H2 json_value cannot emulate 'error on empty'; use the default or 'default <literal> on empty'");
    }
}

try {
    return session.createQuery(hql, String.class).getSingleResult();
} catch (org.hibernate.QueryException e) {
    if (e.getMessage() != null && e.getMessage().contains("error on empty clause on H2")) {
        // Retry with the supported default-expression form, then default in Java
        String raw = session.createQuery(stripClause(hql, "error on empty"), String.class).getSingleResult();
        return raw != null ? raw : "n/a";
    }
    throw e;
}

When it happens

Trigger: HQL on the H2 dialect: select json_value(d.doc, '$.nick' error on empty) from Document d. This is the only empty form rejected on H2.

Common situations: Strict-mode queries ported from Oracle/SQL Server where ERROR ON EMPTY is explicit; H2 unit tests failing after adding strictness clauses for production parity.

Related errors

• Can't emulate on error 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/df34e7d76e256597. Report an issue: GitHub.