hibernate/hibernate-orm · error · QueryException

Can't emulate on empty clause on SingleStore

Error message

Can't emulate on empty clause on SingleStore

What it means

SingleStore's json_value emulation only supports the default 'null on empty' behavior - json_extract_string returns NULL when the path matches nothing. An HQL json_value() that explicitly requests 'error on empty' or 'default <expr> on empty' cannot be translated, so SingleStoreJsonValueFunction.render throws this QueryException.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/function/json/SingleStoreJsonValueFunction.java:39

 */
public class SingleStoreJsonValueFunction extends JsonValueFunction {

	public SingleStoreJsonValueFunction(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 ) {
			throw new QueryException( "Can't emulate on error clause on SingleStore" );
		}
		if ( arguments.emptyBehavior() != null && arguments.emptyBehavior() != JsonValueEmptyBehavior.NULL ) {
			throw new QueryException( "Can't emulate on empty clause on SingleStore" );
		}
		if ( arguments.returningType() != null ) {
			if ( arguments.returningType().getJdbcMapping().getJdbcType().isBoolean() ) {
				sqlAppender.append( "case " );
			}
			else {
				sqlAppender.append( "cast(" );
			}
		}
		final String jsonPath;
		try {
			jsonPath = walker.getLiteralValue( arguments.jsonPath() );
		}
		catch (Exception ex) {
			throw new QueryException( "SingleStore json_value only support literal json paths, but got " + arguments.jsonPath() );
		}
		final List<JsonPathHelper.JsonPathElement> jsonPathElements = JsonPathHelper.parseJsonPathElements( jsonPath );
		sqlAppender.appendSql( "json_extract_string(" );

View on GitHub (pinned to fad1729dce)

Solutions

1. Remove the 'on empty' clause and handle a null result in Java (null already means 'path empty').
2. Wrap json_value in coalesce() in HQL to supply a default: coalesce(json_value(e.doc, '$.x'), 'none').
3. Validate presence with json_exists() first when missing fields must be an error.
4. Fall back to native SQL if error-on-empty semantics are required.

Example fix

// before - throws on SingleStore
select json_value(e.doc, '$.owner' default 'unknown' on empty) from Event e

// after - coalesce supplies the default in HQL
select coalesce(json_value(e.doc, '$.owner'), 'unknown') from Event e

// Supply defaults via coalesce instead of DEFAULT/ERROR ON EMPTY
String hql = "select coalesce(json_value(e.doc, '$.owner'), 'unknown') from Event e";

try {
    return session.createQuery(hql, String.class).getSingleResult();
} catch (QueryException e) {
    if (e.getMessage().contains("on empty clause")) {
        return "unknown"; // default supplied by the application
    }
    throw e;
}

When it happens

Trigger: HQL like: select json_value(e.doc, '$.missing' error on empty) from Event e, or json_value(e.doc, '$.missing' default 'none' on empty). The check is arguments.emptyBehavior() != null && arguments.emptyBehavior() != JsonValueEmptyBehavior.NULL.

Common situations: Strict queries that treat a missing JSON field as an exceptional condition; use of DEFAULT ... ON EMPTY from the SQL standard; porting queries from Oracle/PostgreSQL to SingleStore.

Related errors

• Can't emulate on error clause on SingleStore
• Can't emulate json_objectagg filter clause when using 'null
• Can't emulate on error clause on SingleStore
• Can't emulate on empty clause on SingleStore
• Can't emulate on error clause on CockroachDB

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