hibernate/hibernate-orm · error · QueryException

Can't emulate on empty clause on CockroachDB

Error message

Can't emulate on empty clause on CockroachDB

What it means

On CockroachDB, Hibernate emulates json_value() via jsonb_path_query_first(), which naturally returns SQL NULL when the path matches nothing. The emulation therefore only supports the default NULL ON EMPTY behavior; asking for ERROR ON EMPTY or DEFAULT <expr> ON EMPTY cannot be rendered, and the SQL translator throws this QueryException during query plan compilation.

Source

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

 */
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(),
				walker
		);
	}

View on GitHub (pinned to fad1729dce)

Solutions

1. Remove the ON EMPTY clause - NULL ON EMPTY is already the emulated default
2. Emulate DEFAULT <expr> ON EMPTY with coalesce(): coalesce(json_value(d.doc, '$.nick'), 'n/a')
3. Move default-value substitution into application code after reading the possibly-null result
4. Use a native SQL query against jsonb_path_query_first() if ERROR ON EMPTY semantics are truly required

Example fix

// before - throws on CockroachDB
select json_value(d.doc, '$.nick' default 'n/a' on empty) from Document d

// after - default NULL ON EMPTY; apply the default with coalesce
select coalesce(json_value(d.doc, '$.nick'), 'n/a') from Document d

// Reject non-default ON EMPTY clauses for json_value() before running 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 empty");
            if (i >= 0 && !h.startsWith("null on empty", i)) {
                throw new IllegalArgumentException(
                    "Use coalesce() instead of 'error/default on empty' for json_value() on CockroachDB");
            }
        }
    }
}

try {
    return session.createQuery(hql, String.class).getSingleResult();
} catch (org.hibernate.QueryException e) {
    if (e.getMessage() != null && e.getMessage().contains("on empty clause on CockroachDB")) {
        // Retry with default NULL ON EMPTY and apply the default in Java
        String raw = session.createQuery(stripClause(hql, "on empty"), String.class).getSingleResult();
        return raw != null ? raw : "n/a";
    }
    throw e;
}

When it happens

Trigger: HQL on the CockroachDB dialect with json_value() using a non-default empty clause, e.g. select json_value(d.doc, '$.nick' error on empty) from Document d or select json_value(d.doc, '$.nick' default 'n/a' on empty) from Document d. The check arguments.emptyBehavior() != JsonValueEmptyBehavior.NULL rejects both forms.

Common situations: Queries ported from Oracle or SQL Server where DEFAULT ON EMPTY is the idiomatic defensive pattern; multi-database products where only the CockroachDB profile fails; Hibernate 6.6+/7.x JSON functions introduced into an existing CRDB service.

Related errors

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