{"record":{"id":"ece6368d4a73c423","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-on-empty-clause-on-singlestore","errorCode":null,"errorMessage":"Can't emulate on empty clause on SingleStore","messagePattern":"Can't emulate on empty clause on SingleStore","errorType":"exception","errorClass":"QueryException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/function/json/SingleStoreJsonQueryFunction.java","lineNumber":39,"sourceCode":" * SingleStore json_query function.\n */\npublic class SingleStoreJsonQueryFunction extends JsonQueryFunction {\n\n\tpublic SingleStoreJsonQueryFunction(TypeConfiguration typeConfiguration) {\n\t\tsuper( typeConfiguration, true, false );\n\t}\n\n\t@Override\n\tprotected void render(\n\t\t\tSqlAppender sqlAppender,\n\t\t\tJsonQueryArguments arguments,\n\t\t\tReturnableType<?> returnType,\n\t\t\tSqlAstTranslator<?> walker) {\n\t\tif ( arguments.errorBehavior() != null && arguments.errorBehavior() != JsonQueryErrorBehavior.ERROR ) {\n\t\t\tthrow new QueryException( \"Can't emulate on error clause on SingleStore\" );\n\t\t}\n\t\tif ( arguments.emptyBehavior() != null && arguments.emptyBehavior() != JsonQueryEmptyBehavior.NULL ) {\n\t\t\tthrow new QueryException( \"Can't emulate on empty clause on SingleStore\" );\n\t\t}\n\t\telse {\n\t\t\tfinal String jsonPath;\n\t\t\ttry {\n\t\t\t\tjsonPath = walker.getLiteralValue( arguments.jsonPath() );\n\t\t\t}\n\t\t\tcatch (Exception ex) {\n\t\t\t\tthrow new QueryException( \"SingleStore json_query only support literal json paths, but got \" + arguments.jsonPath() );\n\t\t\t}\n\t\t\tfinal List<JsonPathHelper.JsonPathElement> jsonPathElements = JsonPathHelper.parseJsonPathElements( jsonPath );\n\t\t\tfinal JsonQueryWrapMode wrapMode = arguments.wrapMode();\n\t\t\tfinal DecorationMode decorationMode = determineDecorationMode( wrapMode );\n\t\t\tif ( decorationMode == DecorationMode.WRAP ) {\n\t\t\t\tsqlAppender.appendSql( \"concat('[',\" );\n\t\t\t}\n\t\t\tsqlAppender.appendSql( \"nullif(json_extract_string(\" );\n\t\t\targuments.jsonDocument().accept( walker );\n\t\t\tfor ( JsonPathHelper.JsonPathElement pathElement : jsonPathElements ) {","sourceCodeStart":21,"sourceCodeEnd":57,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/function/json/SingleStoreJsonQueryFunction.java#L21-L57","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Remove the 'on empty' clause - the implicit behavior on SingleStore is already 'null on empty'.","Handle a null return value in Java (null means the path resolved to nothing) instead of asking the DB to raise.","Use coalesce()/is null checks in HQL around json_query for missing-path logic.","Fall back to a native query if strict ERROR ON EMPTY semantics are a hard requirement."],"exampleFix":"// before - throws on SingleStore\nselect json_query(e.doc, '$.address' error on empty) from Event e\n\n// after - implicit null on empty; branch on null in Java\nselect json_query(e.doc, '$.address') from Event e","handlingStrategy":"fallback","validationCode":"// Omit 'on empty'; null already means 'path empty' on SingleStore\nString hql = \"select json_query(e.doc, '$.address') from Event e\";","typeGuard":null,"tryCatchPattern":"try {\n    return session.createQuery(hql, String.class).getSingleResult();\n} catch (QueryException e) {\n    if (e.getMessage().contains(\"on empty clause\")) {\n        return null; // emulate 'null on empty' in application code\n    }\n    throw e;\n}","preventionTips":["Do not rely on ERROR ON EMPTY for control flow - check for null instead","Test JSON queries against every target dialect in CI"],"tags":["singlestore","json","json-query","hql","sql-dialect","query-exception"],"backgroundTag":"unsupported-sql-dialect-feature","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}