{"record":{"id":"857d046f34702ca3","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-on-empty-clause-on-cockroachdb-857d04","errorCode":null,"errorMessage":"Can't emulate on empty clause on CockroachDB","messagePattern":"Can't emulate on empty 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":43,"sourceCode":" */\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(),\n\t\t\t\twalker\n\t\t);\n\t}","sourceCodeStart":25,"sourceCodeEnd":61,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/function/json/CockroachDBJsonValueFunction.java#L25-L61","documentation":"On CockroachDB, Hibernate emulates json_value() via jsonb_path_query_first(), which naturally returns SQL NULL when the path matches nothing. The emulation therefore only supports the default NULL ON EMPTY behavior; asking for ERROR ON EMPTY or DEFAULT <expr> ON EMPTY cannot be rendered, and the SQL translator throws this QueryException during query plan compilation.","triggerScenarios":"HQL on the CockroachDB dialect with json_value() using a non-default empty clause, e.g. select json_value(d.doc, '$.nick' error on empty) from Document d or select json_value(d.doc, '$.nick' default 'n/a' on empty) from Document d. The check arguments.emptyBehavior() != JsonValueEmptyBehavior.NULL rejects both forms.","commonSituations":"Queries ported from Oracle or SQL Server where DEFAULT ON EMPTY is the idiomatic defensive pattern; multi-database products where only the CockroachDB profile fails; Hibernate 6.6+/7.x JSON functions introduced into an existing CRDB service.","solutions":["Remove the ON EMPTY clause - NULL ON EMPTY is already the emulated default","Emulate DEFAULT <expr> ON EMPTY with coalesce(): coalesce(json_value(d.doc, '$.nick'), 'n/a')","Move default-value substitution into application code after reading the possibly-null result","Use a native SQL query against jsonb_path_query_first() if ERROR ON EMPTY semantics are truly required"],"exampleFix":"// before - throws on CockroachDB\nselect json_value(d.doc, '$.nick' default 'n/a' on empty) from Document d\n\n// after - default NULL ON EMPTY; apply the default with coalesce\nselect coalesce(json_value(d.doc, '$.nick'), 'n/a') from Document d","handlingStrategy":"fallback","validationCode":"// Reject non-default ON EMPTY clauses for json_value() before running 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 empty\");\n            if (i >= 0 && !h.startsWith(\"null on empty\", i)) {\n                throw new IllegalArgumentException(\n                    \"Use coalesce() instead of 'error/default on empty' for json_value() on CockroachDB\");\n            }\n        }\n    }\n}","typeGuard":null,"tryCatchPattern":"try {\n    return session.createQuery(hql, String.class).getSingleResult();\n} catch (org.hibernate.QueryException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"on empty clause on CockroachDB\")) {\n        // Retry with default NULL ON EMPTY and apply the default in Java\n        String raw = session.createQuery(stripClause(hql, \"on empty\"), String.class).getSingleResult();\n        return raw != null ? raw : \"n/a\";\n    }\n    throw e;\n}","preventionTips":["Express DEFAULT ON EMPTY as coalesce(json_value(...), default) - it is portable across every Hibernate dialect","Never write 'error on empty' if the query must run on CockroachDB or H2","Test each dialect profile in CI so clause incompatibilities surface at build time"],"tags":["hibernate","cockroachdb","json","hql","json-value","sql-dialect","query-exception"],"backgroundTag":"json-on-empty-clause-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}