{"record":{"id":"a9b8caa6c9642938","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-on-error-clause-on-h2-a9b8ca","errorCode":null,"errorMessage":"Can't emulate on error clause on H2","messagePattern":"Can't emulate on error clause on H2","errorType":"exception","errorClass":"QueryException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/function/json/H2JsonValueFunction.java","lineNumber":45,"sourceCode":"\n/**\n * H2 json_value function.\n */\npublic class H2JsonValueFunction extends JsonValueFunction {\n\n\tpublic H2JsonValueFunction(TypeConfiguration typeConfiguration) {\n\t\tsuper( typeConfiguration, false, true );\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// Json dereference errors by default if the JSON is invalid\n\t\tif ( arguments.errorBehavior() != null && arguments.errorBehavior() != JsonValueErrorBehavior.ERROR ) {\n\t\t\tthrow new QueryException( \"Can't emulate on error clause on H2\" );\n\t\t}\n\t\tif ( arguments.emptyBehavior() == JsonValueEmptyBehavior.ERROR ) {\n\t\t\tthrow new QueryException( \"Can't emulate error on empty clause on H2\" );\n\t\t}\n\t\tfinal Expression defaultExpression = arguments.emptyBehavior() == null\n\t\t\t\t? null\n\t\t\t\t: arguments.emptyBehavior().getDefaultExpression();\n\t\tif ( defaultExpression != null ) {\n\t\t\tsqlAppender.appendSql( \"coalesce(\" );\n\t\t}\n\t\tfinal boolean hexDecoding;\n\t\tif ( arguments.returningType() != null ) {\n\t\t\thexDecoding = H2JsonValueFunction.needsHexDecoding( arguments.returningType().getJdbcMapping() );\n\t\t\tsqlAppender.appendSql( \"cast(\" );\n\t\t\tif ( hexDecoding ) {\n\t\t\t\t// We encode binary data as hex, so we have to decode here\n\t\t\t\tsqlAppender.appendSql( \"hextoraw(regexp_replace(\" );\n\t\t\t}","sourceCodeStart":27,"sourceCodeEnd":63,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/function/json/H2JsonValueFunction.java#L27-L63","documentation":"H2 lacks native json_value(); Hibernate emulates it with dereference expressions, which raise an error on invalid JSON by nature. Only the default ERROR ON ERROR behavior is emulatable - null on error and default <expr> on error are rejected during SQL rendering. Note the asymmetry: default <expr> on empty IS supported on H2 (rendered via coalesce).","triggerScenarios":"HQL on the H2 dialect: select json_value(d.doc, '$.score' returning integer null on error) from Document d, or json_value(d.doc, '$.score' default 0 on error). JsonValueErrorBehavior values other than ERROR throw in H2JsonValueFunction.render().","commonSituations":"Defensive NULL/DEFAULT ON ERROR clauses written against Oracle or SQL Server semantics, then executed under the H2 unit-test profile; quickstart apps defaulting to in-memory H2 while production uses another database.","solutions":["Drop the on error clause - the default ERROR behavior is what the emulation implements","Keep documents valid (write-time validation or where d.doc is json) so errors cannot occur","Run these tests with Testcontainers against the production database","Use a native query when lenient error handling is required"],"exampleFix":"// before - throws on H2\nselect json_value(d.doc, '$.score' returning integer null on error) from Document d\n\n// after - default ERROR ON ERROR; ensure documents are valid\nselect json_value(d.doc, '$.score' returning integer) from Document d","handlingStrategy":"fallback","validationCode":"// Reject non-default ON ERROR clauses for json_value on H2 before execution\nstatic void assertTranslatable(SessionFactory sf, String hql) {\n    if (sf.getJdbcServices().getDialect() instanceof org.hibernate.dialect.H2Dialect) {\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                    \"H2 json_value only supports the default 'error on error'; remove the clause\");\n            }\n        }\n    }\n}","typeGuard":null,"tryCatchPattern":"try {\n    return session.createQuery(hql, Integer.class).getSingleResult();\n} catch (org.hibernate.QueryException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"on error clause on H2\")) {\n        // Retry with default ERROR ON ERROR; 'default x on empty' is still allowed on H2\n        return session.createQuery(stripClause(hql, \"on error\"), Integer.class).getSingleResult();\n    }\n    throw e;\n}","preventionTips":["Omit ON ERROR clauses in HQL meant to run on H2; rely on valid documents instead","Use 'default <literal> on empty' (supported on H2) rather than 'null/default ... on error'","Pair H2 fast tests with Testcontainers tests of the same JSON queries for the production dialect"],"tags":["hibernate","h2","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"}