hibernate/hibernate-orm · error · QueryException

Can't emulate on empty clause on MariaDB

Error message

Can't emulate on empty clause on MariaDB

What it means

MariaDB returns null when a json_value path matches nothing, so only NULL ON EMPTY is emulatable. The MariaDB json_value renderer rejects every other ON EMPTY behavior with this QueryException during SQL generation.

Source

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

 */
public class MariaDBJsonValueFunction extends JsonValueFunction {

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

	@Override
	protected void render(
			SqlAppender sqlAppender,
			JsonValueArguments arguments,
			ReturnableType<?> returnType,
			SqlAstTranslator<?> walker) {
		if ( arguments.errorBehavior() != null && arguments.errorBehavior() != JsonValueErrorBehavior.NULL ) {
			// MariaDB reports the error 4038 as warning and simply returns null
			throw new QueryException( "Can't emulate on error clause on MariaDB" );
		}
		if ( arguments.emptyBehavior() != null && arguments.emptyBehavior() != JsonValueEmptyBehavior.NULL ) {
			throw new QueryException( "Can't emulate on empty clause on MariaDB" );
		}
			final JdbcType jdbcType = arguments.returningType() == null
					? null
					: arguments.returningType().getJdbcMapping().getJdbcType();
			if ( jdbcType != null ) {
				switch ( jdbcType.getDefaultSqlTypeCode() ) {
					case BOOLEAN:
						sqlAppender.append( "case " );
						break;
					case BINARY:
					case VARBINARY:
					case LONG32VARBINARY:
						// We encode binary data as hex, so we have to decode here
						sqlAppender.append( "unhex(json_unquote(" );
						break;
					case UUID:
						if ( jdbcType.isBinary() ) {
							sqlAppender.append( "unhex(replace(json_unquote(" );

View on GitHub (pinned to fad1729dce)

Solutions

1. Remove the ON EMPTY clause: json_value already returns null on empty on MariaDB.
2. Use NULL ON EMPTY explicitly when you want the intent visible.
3. Wrap the call in coalesce() to supply the default value.
4. Fall back to a native query when ERROR ON EMPTY semantics are required.

Example fix

// before
select json_value(e.doc, '$.name' default 'n/a' on empty) from Entity e

// after
select coalesce(json_value(e.doc, '$.name' null on empty), 'n/a') from Entity e

boolean mariadb = session.getJdbcServices().getDialect() instanceof org.hibernate.dialect.MariaDBDialect;
if (mariadb && emptyBehavior != null && emptyBehavior != NULL_ON_EMPTY) {
    // Plan: remove the clause and supply the default with coalesce in the final query.
    hql = hql.replace(" default " + defaultExpr + " on empty", "");
}

try {
    return session.createQuery(hql, String.class).getSingleResult();
} catch (org.hibernate.QueryException e) {
    if (e.getMessage() != null && e.getMessage().contains("on empty clause on MariaDB")) {
        // Fallback: NULL ON EMPTY plus coalesce for the default value.
        return session.createQuery(fallbackHql, String.class).getSingleResult();
    }
    throw e;
}

When it happens

Trigger: An HQL json_value call on MariaDB uses DEFAULT <expr> ON EMPTY or ERROR ON EMPTY. The check emptyBehavior() != null && != JsonValueEmptyBehavior.NULL fires.

Common situations: Queries written for Oracle or PostgreSQL that supply a DEFAULT value for empty results. Optional JSON fields where the path may be absent in some rows.

Related errors

• Can't emulate on error clause on MariaDB
• Can't emulate on error clause on CockroachDB
• Can't emulate on empty clause on CockroachDB
• CockroachDB json_value only support literal json paths, but
• Can't emulate on error clause on H2

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