{"record":{"id":"df34e7d76e256597","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-error-on-empty-clause-on-h2-df34e7","errorCode":null,"errorMessage":"Can't emulate error on empty clause on H2","messagePattern":"Can't emulate error on empty 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":48,"sourceCode":" */\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}\n\t\t}\n\t\telse {\n\t\t\thexDecoding = false;","sourceCodeStart":30,"sourceCodeEnd":66,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/function/json/H2JsonValueFunction.java#L30-L66","documentation":"For json_value() on H2, ERROR ON EMPTY has no emulation: the dereference-based rendering naturally returns NULL for a missing path, and raising an error instead cannot be expressed. The translator accepts NULL ON EMPTY (the default) and DEFAULT <expr> ON EMPTY (rendered via coalesce), and throws only when emptyBehavior() == JsonValueEmptyBehavior.ERROR.","triggerScenarios":"HQL on the H2 dialect: select json_value(d.doc, '$.nick' error on empty) from Document d. This is the only empty form rejected on H2.","commonSituations":"Strict-mode queries ported from Oracle/SQL Server where ERROR ON EMPTY is explicit; H2 unit tests failing after adding strictness clauses for production parity.","solutions":["Remove error on empty and rely on the default NULL ON EMPTY","If you need a default, keep the supported form: json_value(d.doc, '$.nick' default 'n/a' on empty)","Check for null in application code instead of asking the database to raise","Run these queries against the production database in tests via Testcontainers"],"exampleFix":"// before - throws on H2\nselect json_value(d.doc, '$.nick' error on empty) from Document d\n\n// after - the default-expression form is supported on H2\nselect json_value(d.doc, '$.nick' default 'n/a' on empty) from Document d","handlingStrategy":"fallback","validationCode":"// Reject ERROR ON EMPTY 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            && hql.toLowerCase().contains(\"error on empty\")) {\n        throw new IllegalArgumentException(\n            \"H2 json_value cannot emulate 'error on empty'; use the default or 'default <literal> on empty'\");\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(\"error on empty clause on H2\")) {\n        // Retry with the supported default-expression form, then default in Java\n        String raw = session.createQuery(stripClause(hql, \"error on empty\"), String.class).getSingleResult();\n        return raw != null ? raw : \"n/a\";\n    }\n    throw e;\n}","preventionTips":["Prefer 'default <literal> on empty' - it is the empty-clause form H2 supports via coalesce","Treat a null json_value result as 'missing' in application code instead of requesting an error","Lint queries for 'error on empty' when H2 is in the test matrix"],"tags":["hibernate","h2","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"}