{"record":{"id":"70b612bfb55fca02","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-on-error-clause-on-cockroachdb-70b612","errorCode":null,"errorMessage":"Can't emulate on error clause on CockroachDB","messagePattern":"Can't emulate on error clause on CockroachDB","errorType":"exception","errorClass":"QueryException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/function/json/CockroachDBJsonValueFunction.java","lineNumber":40,"sourceCode":"\n/**\n * CockroachDB json_value function.\n */\npublic class CockroachDBJsonValueFunction extends JsonValueFunction {\n\n\tpublic CockroachDBJsonValueFunction(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\t\t// jsonb_path_query_first errors by default\n\t\tif ( arguments.errorBehavior() != null && arguments.errorBehavior() != JsonValueErrorBehavior.ERROR ) {\n\t\t\tthrow new QueryException( \"Can't emulate on error clause on CockroachDB\" );\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 CockroachDB\" );\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( \"CockroachDB json_value only support literal json paths, but got \" + arguments.jsonPath() );\n\t\t}\n\t\tappendJsonValue(\n\t\t\t\tsqlAppender,\n\t\t\t\targuments.jsonDocument(),\n\t\t\t\tJsonPathHelper.parseJsonPathElements( jsonPath ),\n\t\t\t\targuments.isJsonType(),\n\t\t\t\targuments.passingClause(),\n\t\t\t\targuments.returningType(),","sourceCodeStart":22,"sourceCodeEnd":58,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/function/json/CockroachDBJsonValueFunction.java#L22-L58","documentation":"CockroachDB has no native json_value(), so Hibernate emulates it with jsonb_path_query_first(), which always raises an error when the JSON document or path is invalid. Because of that, the emulation can only implement the default 'ERROR ON ERROR' behavior, and the SQL translator rejects any other ON ERROR clause while rendering the query plan. This is a translation-time failure: it is thrown before any SQL reaches CockroachDB.","triggerScenarios":"An HQL query running on the CockroachDB dialect whose json_value() call uses a non-default error clause, e.g. select json_value(d.doc, '$.score' returning integer null on error) from Document d, or json_value(d.doc, '$.score' default 0 on error). Both set JsonValueErrorBehavior to something other than ERROR, and CockroachDBJsonValueFunction.render() throws during SQL rendering.","commonSituations":"Code written and tested on a database with native json_value (Oracle, DB2, SQL Server) or a more capable emulation, then deployed to CockroachDB; a CI matrix where the PostgreSQL or H2 profile passes but the CockroachDB profile fails; adopting the JSON HQL functions introduced in Hibernate 6.6/7.x on a CockroachDB-backed service with @JdbcTypeCode(SqlTypes.JSON) columns.","solutions":["Drop the ON ERROR clause and rely on the default ERROR behavior: json_value(d.doc, '$.score' returning integer)","If you only want to avoid errors on malformed documents, exclude invalid documents before the dereference (e.g. where d.doc is json) instead of using NULL ON ERROR","Use a native SQL query with jsonb_path_query_first() and handle errors in application code","Run the workload on a database whose dialect supports the clause natively (e.g. Oracle)"],"exampleFix":"// before - throws on CockroachDB\nselect json_value(d.doc, '$.score' returning integer null on error) from Document d\n\n// after - default ERROR ON ERROR; keep documents valid and let errors surface\nselect json_value(d.doc, '$.score' returning integer) from Document d","handlingStrategy":"fallback","validationCode":"// Fail fast before executing json_value() with a non-default ON ERROR clause on CockroachDB\nstatic void assertTranslatable(SessionFactory sf, String hql) {\n    if (sf.getJdbcServices().getDialect() instanceof org.hibernate.dialect.CockroachDialect) {\n        String h = hql.toLowerCase();\n        if (h.contains(\"json_value\")) {\n            int i = h.indexOf(\"on error\");\n            if (i >= 0 && !h.startsWith(\"error on error\", i)) {\n                throw new IllegalArgumentException(\n                    \"json_value() on CockroachDB supports only the default 'error on error'; remove the clause\");\n            }\n        }\n    }\n}","typeGuard":null,"tryCatchPattern":"try {\n    return session.createQuery(hql, String.class).getResultList();\n} catch (org.hibernate.QueryException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"on error clause on CockroachDB\")) {\n        // Dialect cannot emulate the clause - retry with the default ERROR ON ERROR behavior\n        return session.createQuery(stripClause(hql, \"on error\"), String.class).getResultList();\n    }\n    throw e;\n}","preventionTips":["Keep one integration-test profile per target dialect (cockroachdb included) so untranslatable clauses fail in CI, not production","Default to omitting ON ERROR/ON EMPTY clauses - the defaults are the only universally emulatable forms","Encapsulate json_* calls in one repository layer so dialect-specific rewrites have a single home","Before using a non-default JSON clause, check the dialect's function implementation (e.g. CockroachDBJsonValueFunction) for the supported set"],"tags":["hibernate","cockroachdb","json","hql","json-value","sql-dialect","query-exception"],"backgroundTag":"json-on-error-clause-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}