hibernate/hibernate-orm · error · QueryException

Can't emulate on error clause on CockroachDB

Error message

Can't emulate on error clause on CockroachDB

What it means

CockroachDB has no native json_value(), so Hibernate emulates it with jsonb_path_query_first(), which always raises an error when the JSON document or path is invalid. Because of that, the emulation can only implement the default 'ERROR ON ERROR' behavior, and the SQL translator rejects any other ON ERROR clause while rendering the query plan. This is a translation-time failure: it is thrown before any SQL reaches CockroachDB.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/function/json/CockroachDBJsonValueFunction.java:40

/**
 * CockroachDB json_value function.
 */
public class CockroachDBJsonValueFunction extends JsonValueFunction {

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

	@Override
	protected void render(
			SqlAppender sqlAppender,
			JsonValueArguments arguments,
			ReturnableType<?> returnType,
			SqlAstTranslator<?> walker) {
		// jsonb_path_query_first errors by default
		if ( arguments.errorBehavior() != null && arguments.errorBehavior() != JsonValueErrorBehavior.ERROR ) {
			throw new QueryException( "Can't emulate on error clause on CockroachDB" );
		}
		if ( arguments.emptyBehavior() != null && arguments.emptyBehavior() != JsonValueEmptyBehavior.NULL ) {
			throw new QueryException( "Can't emulate on empty clause on CockroachDB" );
		}
		final String jsonPath;
		try {
			jsonPath = walker.getLiteralValue( arguments.jsonPath() );
		}
		catch (Exception ex) {
			throw new QueryException( "CockroachDB json_value only support literal json paths, but got " + arguments.jsonPath() );
		}
		appendJsonValue(
				sqlAppender,
				arguments.jsonDocument(),
				JsonPathHelper.parseJsonPathElements( jsonPath ),
				arguments.isJsonType(),
				arguments.passingClause(),
				arguments.returningType(),

View on GitHub (pinned to fad1729dce)

Solutions

1. Drop the ON ERROR clause and rely on the default ERROR behavior: json_value(d.doc, '$.score' returning integer)
2. If you only want to avoid errors on malformed documents, exclude invalid documents before the dereference (e.g. where d.doc is json) instead of using NULL ON ERROR
3. Use a native SQL query with jsonb_path_query_first() and handle errors in application code
4. Run the workload on a database whose dialect supports the clause natively (e.g. Oracle)

Example fix

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

// after - default ERROR ON ERROR; keep documents valid and let errors surface
select json_value(d.doc, '$.score' returning integer) from Document d

// Fail fast before executing json_value() with a non-default ON ERROR clause on CockroachDB
static void assertTranslatable(SessionFactory sf, String hql) {
    if (sf.getJdbcServices().getDialect() instanceof org.hibernate.dialect.CockroachDialect) {
        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(
                    "json_value() on CockroachDB supports only the default 'error on error'; remove the clause");
            }
        }
    }
}

try {
    return session.createQuery(hql, String.class).getResultList();
} catch (org.hibernate.QueryException e) {
    if (e.getMessage() != null && e.getMessage().contains("on error clause on CockroachDB")) {
        // Dialect cannot emulate the clause - retry with the default ERROR ON ERROR behavior
        return session.createQuery(stripClause(hql, "on error"), String.class).getResultList();
    }
    throw e;
}

When it happens

Trigger: An HQL query running on the CockroachDB dialect whose json_value() call uses a non-default error clause, e.g. select json_value(d.doc, '$.score' returning integer null on error) from Document d, or json_value(d.doc, '$.score' default 0 on error). Both set JsonValueErrorBehavior to something other than ERROR, and CockroachDBJsonValueFunction.render() throws during SQL rendering.

Common situations: Code written and tested on a database with native json_value (Oracle, DB2, SQL Server) or a more capable emulation, then deployed to CockroachDB; a CI matrix where the PostgreSQL or H2 profile passes but the CockroachDB profile fails; adopting the JSON HQL functions introduced in Hibernate 6.6/7.x on a CockroachDB-backed service with @JdbcTypeCode(SqlTypes.JSON) columns.

Related errors

• 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
• Can't emulate on error clause on SingleStore
• Can't emulate on empty clause on SingleStore

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