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

The SingleStore emulation of json_query cannot express the ON EMPTY clause; only the default 'null on empty' behavior is supported (json_extract_string already returns NULL when the path matches nothing). An HQL json_query() that explicitly requests 'error on empty', 'empty array on empty', or 'empty object on empty' fails SQL translation with this QueryException.

Source

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

 * SingleStore json_query function.
 */
public class SingleStoreJsonQueryFunction extends JsonQueryFunction {

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

	@Override
	protected void render(
			SqlAppender sqlAppender,
			JsonQueryArguments arguments,
			ReturnableType<?> returnType,
			SqlAstTranslator<?> walker) {
		if ( arguments.errorBehavior() != null && arguments.errorBehavior() != JsonQueryErrorBehavior.ERROR ) {
			throw new QueryException( "Can't emulate on error clause on SingleStore" );
		}
		if ( arguments.emptyBehavior() != null && arguments.emptyBehavior() != JsonQueryEmptyBehavior.NULL ) {
			throw new QueryException( "Can't emulate on empty clause on SingleStore" );
		}
		else {
			final String jsonPath;
			try {
				jsonPath = walker.getLiteralValue( arguments.jsonPath() );
			}
			catch (Exception ex) {
				throw new QueryException( "SingleStore json_query only support literal json paths, but got " + arguments.jsonPath() );
			}
			final List<JsonPathHelper.JsonPathElement> jsonPathElements = JsonPathHelper.parseJsonPathElements( jsonPath );
			final JsonQueryWrapMode wrapMode = arguments.wrapMode();
			final DecorationMode decorationMode = determineDecorationMode( wrapMode );
			if ( decorationMode == DecorationMode.WRAP ) {
				sqlAppender.appendSql( "concat('['," );
			}
			sqlAppender.appendSql( "nullif(json_extract_string(" );
			arguments.jsonDocument().accept( walker );
			for ( JsonPathHelper.JsonPathElement pathElement : jsonPathElements ) {

View on GitHub (pinned to fad1729dce)

Solutions

1. Remove the 'on empty' clause - the implicit behavior on SingleStore is already 'null on empty'.
2. Handle a null return value in Java (null means the path resolved to nothing) instead of asking the DB to raise.
3. Use coalesce()/is null checks in HQL around json_query for missing-path logic.
4. Fall back to a native query if strict ERROR ON EMPTY semantics are a hard requirement.

Example fix

// before - throws on SingleStore
select json_query(e.doc, '$.address' error on empty) from Event e

// after - implicit null on empty; branch on null in Java
select json_query(e.doc, '$.address') from Event e

// Omit 'on empty'; null already means 'path empty' on SingleStore
String hql = "select json_query(e.doc, '$.address') from Event e";

try {
    return session.createQuery(hql, String.class).getSingleResult();
} catch (QueryException e) {
    if (e.getMessage().contains("on empty clause")) {
        return null; // emulate 'null on empty' in application code
    }
    throw e;
}

When it happens

Trigger: HQL like: select json_query(e.doc, '$.missing' error on empty) from Event e (also 'empty array on empty' / 'empty object on empty'). The check is arguments.emptyBehavior() != null && arguments.emptyBehavior() != JsonQueryEmptyBehavior.NULL.

Common situations: Queries written for standard-compliant databases where ERROR ON EMPTY raises an exception for missing paths; migrating to SingleStore; code that distinguishes 'missing path' from 'path resolves to JSON null' via exceptions.

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 H2

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