{"record":{"id":"42c18944d22534d0","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-on-error-clause-on-singlestore-42c189","errorCode":null,"errorMessage":"Can't emulate on error clause on SingleStore","messagePattern":"Can't emulate on error 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":36,"sourceCode":"import org.hibernate.type.spi.TypeConfiguration;\n\n/**\n * 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}","sourceCodeStart":18,"sourceCodeEnd":54,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/function/json/SingleStoreJsonQueryFunction.java#L18-L54","documentation":"SingleStore has no ON ERROR clause for json_query, so the community dialect can only render the default behavior (ERROR). When an HQL json_query() specifies any other error behavior - 'null on error', 'empty array on error', or 'empty object on error' - SingleStoreJsonQueryFunction.render throws this QueryException during SQL translation because there is no way to emulate swallowing or rewriting errors in json_extract_string.","triggerScenarios":"HQL like: select json_query(e.doc, '$.tags' null on error) from Event e (also triggers with 'empty array on error' / 'empty object on error'). The check is arguments.errorBehavior() != null && arguments.errorBehavior() != JsonQueryErrorBehavior.ERROR, i.e. any explicit non-default error behavior.","commonSituations":"Defensive JSON queries written for PostgreSQL/Oracle that ask for NULL instead of an exception on malformed documents; migrating an application to SingleStore (via the hibernate-community-dialects SingleStoreDialect) and re-running existing HQL; shared query libraries used across multiple databases.","solutions":["Drop the 'on error' clause from the HQL json_query call - the default (error) is exactly what SingleStore does.","Keep the clause but only as 'error on error' (equivalent to the default) for cross-dialect portability.","Pre-validate the document with json_exists() (or a try/catch around query execution) and branch in Java instead of asking the database to swallow errors.","Fall back to a native query wrapping json_extract_string in logic that returns null on error."],"exampleFix":"// before - throws on SingleStore\nselect json_query(e.doc, '$.tags' null on error) from Event e\n\n// after - default error behavior, handle null result in Java if path misses\nselect json_query(e.doc, '$.tags') from Event e","handlingStrategy":"fallback","validationCode":"// Keep json_query clauses dialect-portable: omit 'on error' entirely (default ERROR is supported everywhere)\nString hql = \"select json_query(e.doc, '$.tags') 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 error clause\")) {\n        return null; // emulate 'null on error' in application code\n    }\n    throw e;\n}","preventionTips":["Omit ON ERROR/ON EMPTY clauses unless a dialect matrix confirms support","Centralize JSON queries so unsupported-clause errors surface in one place","Add integration tests that run JSON queries against the production dialect"],"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"}