{"record":{"id":"3908d58525ccc16a","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-on-empty-clause-on-singlestore-3908d5","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/SingleStoreJsonValueFunction.java","lineNumber":39,"sourceCode":" */\npublic class SingleStoreJsonValueFunction extends JsonValueFunction {\n\n\tpublic SingleStoreJsonValueFunction(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\tJsonValueArguments arguments,\n\t\t\tReturnableType<?> returnType,\n\t\t\tSqlAstTranslator<?> walker) {\n\n\t\tif ( arguments.errorBehavior() != null && arguments.errorBehavior() != JsonValueErrorBehavior.NULL ) {\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() != JsonValueEmptyBehavior.NULL ) {\n\t\t\tthrow new QueryException( \"Can't emulate on empty clause on SingleStore\" );\n\t\t}\n\t\tif ( arguments.returningType() != null ) {\n\t\t\tif ( arguments.returningType().getJdbcMapping().getJdbcType().isBoolean() ) {\n\t\t\t\tsqlAppender.append( \"case \" );\n\t\t\t}\n\t\t\telse {\n\t\t\t\tsqlAppender.append( \"cast(\" );\n\t\t\t}\n\t\t}\n\t\tfinal String jsonPath;\n\t\ttry {\n\t\t\tjsonPath = walker.getLiteralValue( arguments.jsonPath() );\n\t\t}\n\t\tcatch (Exception ex) {\n\t\t\tthrow new QueryException( \"SingleStore json_value only support literal json paths, but got \" + arguments.jsonPath() );\n\t\t}\n\t\tfinal List<JsonPathHelper.JsonPathElement> jsonPathElements = JsonPathHelper.parseJsonPathElements( jsonPath );\n\t\tsqlAppender.appendSql( \"json_extract_string(\" );","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/SingleStoreJsonValueFunction.java#L21-L57","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Remove the 'on empty' clause and handle a null result in Java (null already means 'path empty').","Wrap json_value in coalesce() in HQL to supply a default: coalesce(json_value(e.doc, '$.x'), 'none').","Validate presence with json_exists() first when missing fields must be an error.","Fall back to native SQL if error-on-empty semantics are required."],"exampleFix":"// before - throws on SingleStore\nselect json_value(e.doc, '$.owner' default 'unknown' on empty) from Event e\n\n// after - coalesce supplies the default in HQL\nselect coalesce(json_value(e.doc, '$.owner'), 'unknown') from Event e","handlingStrategy":"fallback","validationCode":"// Supply defaults via coalesce instead of DEFAULT/ERROR ON EMPTY\nString hql = \"select coalesce(json_value(e.doc, '$.owner'), 'unknown') 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 \"unknown\"; // default supplied by the application\n    }\n    throw e;\n}","preventionTips":["Use coalesce() in HQL instead of ON EMPTY variants","Treat null results as the 'missing path' signal rather than raising"],"tags":["singlestore","json","json-value","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"}