{"record":{"id":"a2158bd129b44a78","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-null-on-error-clause-on-db2","errorCode":null,"errorMessage":"Can't emulate null on error clause on DB2","messagePattern":"Can't emulate null on error clause on DB2","errorType":"exception","errorClass":"QueryException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/function/json/DB2JsonTableFunction.java","lineNumber":99,"sourceCode":"\t\t\t\tfinal boolean isArray = !(jsonPath instanceof Literal literal)\n\t\t\t\t\t\t|| isArrayAccess( (String) literal.getLiteralValue() );\n\t\t\t\tif ( isArray || hasNestedArray( arguments.columnsClause() ) ) {\n\t\t\t\t\twalker.registerQueryTransformer( new SeriesQueryTransformer( maximumSeriesSize ) );\n\t\t\t\t}\n\t\t\t\treturn tableGroup;\n\t\t\t}\n\t\t};\n\t}\n\n\t@Override\n\tprotected void renderJsonTable(\n\t\t\tSqlAppender sqlAppender,\n\t\t\tJsonTableArguments arguments,\n\t\t\tAnonymousTupleTableGroupProducer tupleType,\n\t\t\tString tableIdentifierVariable,\n\t\t\tSqlAstTranslator<?> walker) {\n\t\tif ( arguments.errorBehavior() == JsonTableErrorBehavior.NULL ) {\n\t\t\tthrow new QueryException( \"Can't emulate null on error clause on DB2\" );\n\t\t}\n\t\tfinal Expression jsonDocument = arguments.jsonDocument();\n\t\tfinal Expression jsonPath = arguments.jsonPath();\n\t\tfinal boolean isArray = isArrayAccess( jsonPath, walker );\n\t\tsqlAppender.appendSql( \"lateral(select\" );\n\t\trenderColumnSelects( sqlAppender, arguments.columnsClause(), 0, isArray );\n\t\tsqlAppender.appendSql( \" from \" );\n\n\t\tif ( isArray ) {\n\t\t\tsqlAppender.appendSql( CteGenerateSeriesFunction.CteGenerateSeriesQueryTransformer.NAME );\n\t\t\tsqlAppender.appendSql( \" i join \" );\n\t\t}\n\t\tsqlAppender.appendSql( \"json_table(\" );\n\t\t// DB2 json functions only work when passing object documents,\n\t\t// which is why an array element query result is packed in shell object `{\"a\":...}`\n\t\tif ( isArray ) {\n\t\t\tsqlAppender.appendSql( \"'{\\\"a\\\":'||\" );\n\t\t}","sourceCodeStart":81,"sourceCodeEnd":117,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/function/json/DB2JsonTableFunction.java#L81-L117","documentation":"Hibernate emulates json_table() on DB2 as a lateral(select ... from ...) subquery with a generate_series-based unnest for arrays. That construction propagates JSON parse and path errors, so only the default ERROR ON ERROR behavior is emulatable; the NULL ON ERROR variant is rejected while the query plan is rendered.","triggerScenarios":"HQL on the DB2 dialect where the json_table error clause uses NULL: select t.name from Document d, json_table(d.doc, '$' null on error columns(name varchar)) t. The grammar only allows (error|null) on error, and the NULL form throws.","commonSituations":"ETL-style flattening over JSON columns whose documents may be malformed; defensive NULL ON ERROR written because documents come from an external feed; adding a DB2 profile to a CI matrix that previously only tested databases supporting the clause.","solutions":["Remove null on error - the default ERROR ON ERROR is emulated","Pre-filter to well-formed documents before the json_table query (e.g. where d.doc is json) so errors cannot occur","Quarantine invalid documents at write time so queries can assume valid JSON","Use a native DB2 query for the table function if NULL ON ERROR semantics are mandatory"],"exampleFix":"// before - throws on DB2\nselect t.name from Document d, json_table(d.doc, '$' null on error columns(name varchar)) t\n\n// after - default ERROR ON ERROR; ensure documents are valid\nselect t.name from Document d, json_table(d.doc, '$' columns(name varchar)) t","handlingStrategy":"validation","validationCode":"// Reject NULL ON ERROR for json_table on DB2 before execution\nstatic void assertTranslatable(SessionFactory sf, String hql) {\n    if (sf.getJdbcServices().getDialect() instanceof org.hibernate.dialect.DB2Dialect\n            && hql.toLowerCase().contains(\"null on error\")) {\n        throw new IllegalArgumentException(\n            \"DB2 json_table emulation only supports the default ERROR ON ERROR; pre-validate documents\");\n    }\n}","typeGuard":null,"tryCatchPattern":"try {\n    return session.createQuery(hql, Object.class).getResultList();\n} catch (org.hibernate.QueryException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"null on error clause on DB2\")) {\n        // Retry with default ERROR ON ERROR after ensuring documents are well-formed\n        return session.createQuery(stripClause(hql, \"null on error\"), Object.class).getResultList();\n    }\n    throw e;\n}","preventionTips":["Validate JSON documents at write time so queries can assume well-formed input","Treat NULL ON ERROR as a dialect-specific luxury, not a portable construct","Maintain a DB2 integration-test profile so json_table clauses are exercised on every supported dialect"],"tags":["hibernate","db2","json","hql","json-table","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"}