hibernate/hibernate-orm · error · QueryException

Can't emulate null on error clause on DB2

Error message

Can't emulate null on error clause on DB2

What it means

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.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/function/json/DB2JsonTableFunction.java:99

				final boolean isArray = !(jsonPath instanceof Literal literal)
						|| isArrayAccess( (String) literal.getLiteralValue() );
				if ( isArray || hasNestedArray( arguments.columnsClause() ) ) {
					walker.registerQueryTransformer( new SeriesQueryTransformer( maximumSeriesSize ) );
				}
				return tableGroup;
			}
		};
	}

	@Override
	protected void renderJsonTable(
			SqlAppender sqlAppender,
			JsonTableArguments arguments,
			AnonymousTupleTableGroupProducer tupleType,
			String tableIdentifierVariable,
			SqlAstTranslator<?> walker) {
		if ( arguments.errorBehavior() == JsonTableErrorBehavior.NULL ) {
			throw new QueryException( "Can't emulate null on error clause on DB2" );
		}
		final Expression jsonDocument = arguments.jsonDocument();
		final Expression jsonPath = arguments.jsonPath();
		final boolean isArray = isArrayAccess( jsonPath, walker );
		sqlAppender.appendSql( "lateral(select" );
		renderColumnSelects( sqlAppender, arguments.columnsClause(), 0, isArray );
		sqlAppender.appendSql( " from " );

		if ( isArray ) {
			sqlAppender.appendSql( CteGenerateSeriesFunction.CteGenerateSeriesQueryTransformer.NAME );
			sqlAppender.appendSql( " i join " );
		}
		sqlAppender.appendSql( "json_table(" );
		// DB2 json functions only work when passing object documents,
		// which is why an array element query result is packed in shell object `{"a":...}`
		if ( isArray ) {
			sqlAppender.appendSql( "'{\"a\":'||" );
		}

View on GitHub (pinned to fad1729dce)

Solutions

1. Remove null on error - the default ERROR ON ERROR is emulated
2. Pre-filter to well-formed documents before the json_table query (e.g. where d.doc is json) so errors cannot occur
3. Quarantine invalid documents at write time so queries can assume valid JSON
4. Use a native DB2 query for the table function if NULL ON ERROR semantics are mandatory

Example fix

// before - throws on DB2
select t.name from Document d, json_table(d.doc, '$' null on error columns(name varchar)) t

// after - default ERROR ON ERROR; ensure documents are valid
select t.name from Document d, json_table(d.doc, '$' columns(name varchar)) t

// Reject NULL ON ERROR for json_table on DB2 before execution
static void assertTranslatable(SessionFactory sf, String hql) {
    if (sf.getJdbcServices().getDialect() instanceof org.hibernate.dialect.DB2Dialect
            && hql.toLowerCase().contains("null on error")) {
        throw new IllegalArgumentException(
            "DB2 json_table emulation only supports the default ERROR ON ERROR; pre-validate documents");
    }
}

try {
    return session.createQuery(hql, Object.class).getResultList();
} catch (org.hibernate.QueryException e) {
    if (e.getMessage() != null && e.getMessage().contains("null on error clause on DB2")) {
        // Retry with default ERROR ON ERROR after ensuring documents are well-formed
        return session.createQuery(stripClause(hql, "null on error"), Object.class).getResultList();
    }
    throw e;
}

When it happens

Trigger: 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.

Common situations: 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.

Related errors

• Can't emulate null on error clause on H2
• Can't emulate on error clause on CockroachDB
• Can't emulate on empty clause on CockroachDB
• Can't emulate json_arrayagg filter clause when using 'null o
• Can't emulate json_objectagg 'with unique keys' clause.

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/a2158bd129b44a78. Report an issue: GitHub.